White Paper or Best Practice for Pedigree or Geneology
I am trying to create a database for a cattle breeder. To prevent inbreeding and to document which animals produce the highest quality offspring, each animal needs a table of "related animals" where the relationship could be sire, dam, calf, etc. The dataset starts with 6 generations, but the number of generations grows each year.
First I tried tables of generations F1, F2, F3 ... that didn't work. It might work with humans who have tidier social relationships, but not so with cows who may mate and reproduce with someone new each year, and whose offspring mate just two years later. A bull belonging to Generation 1 might be sire to a Generation 6 calf.
Then I tried just two tables, "Cattle" and "Breeding Relationship" where:
Cattle > Breeding Relationship < Cattle (Self)
This one works but was tedious, since I had to build the geneology of at least 14 ancestors for each animal, plus their descendents. I created a button to speed things along, so that when I create a Sire to Calf relationship, a Calf to Sire relationship is created too. But I still found it so tedious I quit this model as well.
Finally, I tried one more method which I'm using now. I created a container field to hold a copy of the paper pedigree, and then created a table of calves only, but no further descendents beyond the first generation (no "grandcalf" displayed on the cow record). This was much quicker for data entry, but forces the user to look in the container file to see the ancestors, or to look at the calf record to see 2nd generation descendents. This relationship is similar to the above
Cattle > Calf Relationship < Cattle (self)
Thus all cattle are entered into one table, and each cow or bull has a portal view of its first generation calves. Using "go to related records" I can obtain aggregate data for that cow or bull about the quality of its 1st generation offspring, and then use go to related records again to get aggregate data for 2nd generation offsprint.
Bottom line, I suspect there is a better model for breeding records out there, and wondered if FileMaker has a white paper or best practice on the subject.