Showing children in parent records and vice versa

[agross]

I need some further advice on how to set up a database whereby the entry for each animal whether it is a father, mother or offspring would show any relationships eg. the entry for an offspring would show the mother and father and the entry for a grown-up would show all the offsprings. At the same time the reporting would be able to produce the total inventory of animals.

[comment]

Maybe this could serve as a starting point:

http://fmforums.com/forum/showtopic.php?tid/174925/post/197881/#197881

[Colin Keefe]

Try a single table:

Animal

--------

_kp_RecordID - serial

_kf_ParentID

Gender

With a self join:

Animal -- Parent [base table: Animal]

_kf_ParentID = _kp_RecordID

And an additional self join, if you want to show both an animals parents and children from the same context:

Animal -- Children [base table: Animal]

_kp_RecordID = _kf_ParentID

Whether a parent is a mother or father is determined by gender.

This way there's a single table, with one record for each animal.

Edited January 10, 2008 by Guest
clarified that all TOs share same base table.