I need to develop a tree-like pedigree of animals up to 5 generations.
I know there are other attributes to the animal; but do I need anyother
tables...assuming that I can develop all the code to generate these
ancestors.
Animal
ID
Name
Foal
ID
SireID
DamID
Would it be any more difficult to make a self-referential table? Like So? If the information is unknown then the fields are blank otherwise the SireID and DamID point back to the ID of another record in the same table.
Animal
ID
Name
SireID
DamID
I'm in the conceptual stage of a similar project for humans and this is the approach I'm leaning towards. I'm planning on using something like this...
Person
ID
Name
MotherID
FatherID
SpouseID
The reason I chose this is that even though a lot of the Mother, Father and Spouse fields might be "blank wasted space" I figured I'd be doing joins on the two tables (three in my case with the additional one for spouse) so often I'd be saving data storage space at the expense of data processing power creating the joins on the fly every time I needed them.
Just a thought...
Matt
P.S. If anyone sees any "evil" inherent in my design I'd love to know...