Hello you all,
I know this question was asked before but I'm just wanna be sure.

I need to build db for a farm, I will handle with the financial tables but I don't know how to deal with the cows.
each cow has it own age, immunization records, and many statistics, the farm divided to classes so I think about this structure:

Farm
|
|
+--------Financial_table
|
+----------------------Classes_table(id,Cows[id],Statics...)
|
|
Cow (id,age, immunization records...)

That way I will get hundreds of tables.
1. is there a better way?
2. is it normal to use a lot of tables like so, is it like it structure in Address Book?

thank you for taking the time to respond.
Shavit

First let me say I haven't got a clue as to what you mean. Given that here is my best attempt to answer.

I would first build the table Cow with attributes of CowID, Birthdate, and whatever other attributes are one per cow. Then I would build a table called CowImunization with attributes of CowID, ImmunizationDate, ImmunizationType.

I am not sure sure what you mean by "the farm divided to classes". My guess is that the cows belong in groups of some sort. So I would build a CowClass table with the attributes of ClassType, and CowID.

If the cow statistics are such things as current weight, current milk production, etc. then those attributes belong in the Cow table since they are one per cow. If they are things like weight on a specific date, milk production on a specific date, etc. then I would build a table with attributes of CowID, DateOfMeasure, and the various statistics on that date.

Hope that helps, but if not at least you can tell me where I have gone wrong and we will have more information to go on.

Teats, however, would need to be a sub-table with a foreign key to cow...

Mark - you've got me right, despite my english.
thanks

so you have a table for cattle (actually you'd probably have a table for livestock)
that stores anything relevant to a specific cow
you could consider some two columns to identify the parents of the cow (these would probably be FK's to to other cattle) heck you might even have 3 sire, dam and optionally surrogate

if you wanted to record multiple attributes then you would need a sub table (eg Milk yields)

if you went down the orute of a table for Livestock
then you would store anything relevant to the livestock in that table (eg Serial No, DoB, gender)
have a sub table for anything relevant to cattle eg breed, dam, sire,
have a sub sub table for anythign that is a multiple isntace for cattle (eg separate milkings, separate weighings and so on)

Hi

You might like to take a look at this Data Model I created a while ago :-
Cows Events Data Model

It has one Table for Cows or other Livestock on a Fram.

It also has one Table for Events in a Cow's Life.

This provides a flexible design for the kind of requirements you are discussing.

HTH

Barry

Out of interest I looked at your db and wanted to make a few small comments:

• The contact details of the owner of the bull are kept in the bull table. If someone owned more than one bull then this data would be duplicated. Would it be better to link to the existing Individuals table and just have a bull owner type?
• The db can hold data for many farms but it doesn't hold any info on who owns the farm ie their name or phone num etc. Could this data also be stored in the Individuals table?
• Shouldn't an individual be (optionally) associated with an event? ie the vet
• The livestock table uses an id to identify each animal. If this id is generated by the db then how do you associate it with the cow? Actually how do you identify a cow - is there a number printed on the side of them or do they use something like RFID tags?
• Would you ever need an event to apply to the whole farm ie government inspection or to a particular type of livestock ie sheep get sheered next Wednesday.

I'll admit I know know nothing of farms or how they run so forgive me if the questions appear a bit dumb.

Mike

PS I deleted the accidental duplicate post for you.

I agree with Mike, and I know a lot about farms because I've read Orwell.

Don't you read Pearls?

Pearls Before Swine free online comic strip library at comics.com

Pearls Before Swine free online comic strip library at comics.com

Pearls Before Swine free online comic strip library at comics.com

Barry

I was thinking some more about your design and was wondering whether it would be worth moving the Bulls table into your Livestock table. I appreciate the OP's question was about cows but your db is more generic so shouldn't it be able to cope with Stallions (horses) or whatever the male equivalent is in pigs etc. At the moment you need to create a new table for each type of male breeding animal that is required.

Cheers

Mike