How would one structure a table for an entity that can have a one to many relationship to itself? Specifically, I'm working on an app to track animal breeding. Each animal has an ID; it's also got a sire ID and a dame ID. So it's possible to have a one to many from the sire or dame to its offspring. I would be inclined to something like this:
ID INT NOT NULL PRIMARY KEY
SIRE_ID INT
DAME_ID INT
and record a null value for those animals which were purchased and added to the breeding stock and an ID in the table for the rest.
So:
- Can someone point me to an article/web page that discusses modeling this sort of relationship?
- Should the ID be an INT or some sort of String? A NULL in the INT would indicate that the animal has no parents in the database but a String with special flag values could be used to indicate the same thing.
Would this possibly be best modeled via two tables? I mean one table for the animals and a separate table solely indicating kinship e. g.:
Animal
ID INT NOT NULL PRIMARY KEY
Kinship
ID INT NOT NULL PRIMARY KEY FOREIGN KEY
SIRE_ID INT PRIMARY KEY FOREIGN KEY
DAME_ID INT PRIMARY KEY FOREIGN KEY
I apologize for the above: my SQL is rusty. I hope it sort of conveys what I'm thinking about.