I am currently working on a Database for my school. I was wondering if someone had advice on the best way to set up a relational database that track and link family members. In my first attemp I created a contacts file with id and family id. I created a self join and put a portal on the student record to show all the related family members. Is this the right way to do it or is there a better way.. say with an additional file. My first DB I had trouble extracting data for example a letter to the parents that contain the students names. I couldn't get three records into one record in the format I wanted...maybe because my initial set up was screwed up. I would much appreciate any suggestions. I want to make sure that this new DB is done correctly. [email protected]

I would probably built it similar to how you have, although I wouldn't use a family ID. I would make each record have fields like SpouseID, FatherID, MotherID. Then I would create self-joins from each of these to the PersonID, as well as self-joins with MotherID on both sides and FatherID on both sides. This would allow what you need, as well as give the ability to easily see siblings.

Chuck

Thanks CHuck for you suggestion.

When you mean self-joins on both side...do you mean MotherID to MotherID. Why do I need this?

Thanks

Novine

Self-join refers to the fact that the file is related to itself.

For instance, I'm assuming that every record has a PersonID. Add to every record a MotherID and a FatherID. Then create a new relationship from the person file to itself. Use MotherID as the match field on the left side and PersonID as the match field on the right. Create anoter relationship linking FatherID to PersonID.

Create another field called SpouseID and create a self-joing from SpousID to PersonID. This will track married couples.

Now, as to why you might want to create a couple of more relationships with MotherID as the match field on both sides and FatherID as the match field on both sides. This is so that you can easily see the siblings of a person. For instance. I'm person PER101. My mother is person PER102. My brother is person PER103. My record's MotherID is set to PER102 and my brother's is also set to PER102. If I had more siblings, all of their MotherIDs would be set to PER102. I could then place a portal to this MotherID based relationship on a layout and see all of the siblings for any person.

Chuck