2 Replies Latest reply on Nov 28, 2011 7:06 PM by JCrawford

    White Paper or Best Practice for Pedigree or Geneology



      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.


        • 1. Re: White Paper or Best Practice for Pedigree or Geneology

          Could that last structure be renamed:


          ParentAnimal::CattleID = Relationship::ParentID
          Calf::CattleID = Relationship::CalfID

          Where Calf and ParentAnimal are occurrences of the same table?

          For a given record, there could be any numer of "calf" records, but only two "ParentAnimal" records.

          That should be all the data entry needed to trace back multiple levels of inheritance as long as the Parent to Calf relationships are intact for each generation--all in the same table.

          Like you, I'm currently mulling over how you would display successive generations on the same screen, but I see that as a Layout design issue not one that requires additional data tables

          The trick here is that you can keep just these two tables, but use multiple occurrences to show successive generations for a particular animal:


          The G1, G2, G3, G4 tables are all occurences of the same table of animal records. G1Relationship, G2relationship and G3Relationship are all occurrences of the same "relationship" table.

          You can then create a layout based on G1 and use portals to G2, G3 and G4 to list the succeding generations of offspring from a particular "ancestor" animal. If you start from G4, you can list an animal's pedigree with two row portals to recreate the typical heredity chart.

          • 2. Re: White Paper or Best Practice for Pedigree or Geneology

             Thanks for your great reply. I did not try (or even think of) the string of table occurrences to replicate generations. Very much appreciated!!