Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007
    Posts
    1

    Unanswered: Create Database / Tables / Keys...

    Hi,
    I'm in the process of setting up a database with about 17000 horses.
    Some of the attributes of a horse are (Sample):

    ...
    foaled: 1999-03-23
    died: n/a
    sire: Madu Gold Dust
    dam: SH Contessa
    sire line: Skowronek (via Naseem)
    dam line: Wadduda db (branch: Markada 1925)
    strain: Saklawiyah Al Abd
    ...

    As you can see, each horse (same as for humans) has ONE sire and ONE dam.
    There will be a large tree structure up and down the sire / dam line.
    Any horse may be selected in a web page (link) and all others should be retrieved from database and the tree structure should be build dynamically.

    How to ideally set-up the tables and keys to quickly find mothers/fathers as well as childs up and down the line?

    Best regards

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a horse has details relevant to that horse
    a horse has two parents
    a horse may compete in many races
    a horse racing event may have many races
    a horse racing arena may hold many events
    a series may comprise many events from many or one arena over a period of time
    a horse may change its name over time
    a horse may have many owners over time

    a horse may have many riders over time, but only one rider per race
    a horse may have many trainers over time, but only one trainer at anyone time

    so for your horse table you may see somehting like
    HorseID autonumber PK
    HorseName
    OwnedBy bigint 'fk to table containing details of owners
    HorseType 'identifies the type of horse could be M/F, or could be fill, stallion, gelding orwhatever silly names horsey people give
    Mare 'fk to to the mare of this horse to another row in this table
    Sire 'fk to to the sire of this horse to another row in this table
    DoB 'date of the horse's birth
    ....etc


    ..you would need to iterate up or down the tree using a series of SQL calls

    one problem you are going to have is recursion and re-entry. its quite feasible for the same sire line to re-appear in the progeny, especailly as AI is increasingly used.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by go4java
    How to ideally set-up the tables and keys to quickly find mothers/fathers as well as childs up and down the line?
    use the nested set data model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2007
    Posts
    5

    Cool

    Here is an good article with examples on Hierarchical data

    http://dev.mysql.com/tech-resources/...ical-data.html

Posting Permissions

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