Create Database / Tables / Keys...

go4java · #1 (**permalink**) 06-12-07, 03:30

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

healdem · #2 (**permalink**) 06-12-07, 05:30

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.