|
| I'm not trying to argue or cause problems, but I think there are issues with your structure.
I know your structure is just a quick example, but I don't think the structure is correctly set up. There may be many 'working' ways to set this up, but few 'right' ways to do it. If the designer doesn't know and understand Cobbs 12 rules, it probably won't be done 'right.'
For this, one would want a table set of of "Animals," and this table will have to relate to itself---because each animal will have parents within this table. I 1) don't remember the name for this and 2) am too lazy to google until it pops up and 3) don't really care if I look stupid for not knowing. :) :) so............ I'll reference this link that has a similar issue:
http://www.databasedev.co.uk/self-join_query.html
In the "Animal" table, each animal will have a unique ID, and one-time, non-changing "events" will be recorded--such as SireID, DamID, BirthDate, BirthWt, WeanDate, ShipDate, SaleDate, SellPrice. Technically, I shouldn't have put in the Purchase information in each animals record, as not every animal is purchased.....however to simplify things, I did, and that data will be left blank for all animals raised, not purchased. Doing this technically violates one of the rules, but simplifies the structure......plus we are not dealing with super-expensive storage requirements in this day and age.
There are obviously complications not addressed....for starters, how do we track AI?
Items that occur--or have a *possibility* of occuring multiple times should be kept in another table. This would include Medication and Weighing....... and I have added feeding.
The big BIG problem here is that by the time Dean gets this stuff learned (assuming he is a rookie on database design), he'll have to most likely have to live with a faulty-designed database, or start all over with the knowledge he gained from the school of hard knocks.
Edit note: I'd be happy to email my structure to anyone--it is about 600K.
Another edit note: Notice on the relationships structure that the Dam and Sire tables do not really exist, they are actually the same table (Animals), with relationships to itself.
Edited by Chip 4/21/2007 15:26
 (Relationships.JPG)
 (MyHerdInnerJoinQueryRelationships.JPG)
Attachments
----------------
 Relationships.JPG (71KB - 340 downloads)
MyHerdInnerJoinQueryRelationships.JPG (72KB - 351 downloads)
| |
|
Next question in building a dbase...
