[RESOLVED] Creating MySQL databases

I thought we had an off-topic section, but I guess not so here I am

I am upgrading my private in-house reptile husbandry website. Right now we can input data with jot, but it is too limited and we need to be able to do more with the data.

I have figured out some of the simpler tables but I am stuck with the breeding part. Here is some info...

each animal has it’s own unique numeric id, when they are bred we get a new id; id-foo x id-bar so that is the pair id. the pairs are not solid, one may mate with many others so I am sticking with the id-foo x id-bar system. So that brings me to the ’pairing’ page - any breeding records for that particular pair are found on that page and multiple breeding sessions are also listed on that page.

The offspring needs to be recorded as well. there are three types, slugs, still, and live. Slugs just need a count, stills need a list of just the morph or type, and live needs the type and an id assigned by the existing framework.

I also need to keep per-session notes for the pairing so that would be session id, notes, and date.

What I do not understand is if I have a table for pairings, sessions, slugs, stills, and lives... how do I keep track of what records match?

I have have only found examples for very simple relations, such as one table has employees and the other has performance data and the performance data is referenced by employee id, but this is a bit deeper than that. If I use a similar system I would have to have all kinds of behind the scenes id references I know there is a better way.

Thanks!

Not sure if I totally understand your criteria, but it seems to me that you basically need joining tables (representing pairings) and then a foreign key in your animal table that relates each animal to its parents. I would figure that this would be better than having a single pairing id for each animal, since as you said some animals may have more than one pair (and offspring will eventually pair as well). You could even track group sex this way (hey, I know it sounds kinky, but dog litters need to account for this).

Something like:

Table animals
primary id (aid) foreign pairing id (pid) birth date date of death other columns...

Table pairings
primary id (pid) female (fid) male (mid) start date end date other columns...

Just using this basic structure you could figure out which animals were any animal’s parents (looking up the related data from the pairing id) even if their parents were shacking up with multiple partners at the time and are now not together.

All animals would be in the animal table, so you wouldn’t be structurally distinguishing between parents and offspring.

You might want to duplicate some related info in the animal table for quicker lookups, but I usually don’t like to duplicate any data so that I don’t need to update it in more than one place.

Does that make sense for what you’re doing?

Yeah, I think you totally understand what I need... and I think my current database I did in workbench can handle all this. After I hear back on a few other forums I will need to dive in and see how broken it is! I need to learn how to handle all the db calls too.. long fun road ahead!

I’ll keep you posted

Thanks!

MySQL syntax is actually really easy:
http://www.w3schools.com/sql/sql_select.asp

You can use the MODx Evolution db API for queries and basic operations, or if you’re ready to jump to Revolution this would be a good excuse to learn xPDO.

ok quick question... sometimes the animal may eat more than one item per feeding. I decided to have up to three feeding tables, primary meal, secondary meal, and in the rare case a third meal. I could have stuck with just a single table for feedings, but I wanted to use multiple to make sure we didn’t confuse anything for a accidental double entry since the item is usually the same with a double feeding.

I could have gone with quantity, but in some cases the items are different. So with all that in mind do I need to have different names for the columns of the different tables; like pri_meal table would have column meal_type. well sec_meal will also have meal_type. Do I need to change that up to pri_meal; meal_type_pri | sec_meal; meal_type_sec?

I have never done anything on this level as you can tell. I guess this is more of a best practices issue or in this situation do I need to keep things more unique? If not I know it may complicate coding the routines if I am not paying attention

QE--

now that I am looking around I have about 8 event types I am recording in separate tables all of which have idx - animal id and date (not timestamp). the idx is constant through out the db, but now I have probably 15 or more date columns.

I’m not a database design expert, but my instinct would be not to create multiple tables for primary and other meals. You can have just one table for all meals and then an enum type column where you can set "primary", "secondary", or "additional". I don’t think that you want to structure your data based on how you will input it, since that’s properly controlled by your web interface (where you could display all feedings to date and enter new feedings, so you could see what’s already been entered before adding a new entry), but rather based on the relationships between your data elements.

So your relationship between the animals and feeding tables would be one to many (one animal gets multiple feedings), which is a perfectly fine and normal thing to do.

AFAIK, datetime and timestamp are functionally equivalent, so use whichever you prefer. Date is less specific, of course, but that may be fine for your purposes. I think that it’s fine to have multiple datetime columns as long as they’re not duplicating data that’s already stored elsewhere.

Yeah I am on the right track, just getting some mixed practices from the guys on irc - should have went there first!

There are all sorts of ways to model databases if you get into that, so pick whichever seems to make the most sense to you.