Results 1 to 10 of 10

Thread: Farm db

  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Farm db

    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

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by MarkATrombley View Post
    I would first build the table Cow with attributes of CowID, Birthdate, and whatever other attributes are one per cow.
    Teats, however, would need to be a sub-table with a foreign key to cow...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2009
    Posts
    5
    Mark - you've got me right, despite my english.
    thanks

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2003
    Location
    London, England
    Posts
    42

    Events in a Cow's Life ;)

    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

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by barryw
    You might like to take a look at this Data Model I created a while ago :-
    Cows Events Data Model

    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I agree with Mike, and I know a lot about farms because I've read Orwell.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •