I have been working on developing genealogical database. One of the things I would like to do is show a family tree like relationship between generations. For example Generation 1 could have multiple generation 2's, and each generation 2 could have many generation 3's. ultimately I would like to develop this to the point that if I want to create a report for someone in Generation 6 that will also give me a listing of blood line parent back to generation 1. Is this best done using a separate table for each generation each linked to each other with each table having a one to many relationships for each successive table? Or is there a simpler way to link one generation to the next?
Coming from a manufacturing background, I would think of this as two tables: an item master and an assembly parts list.
The item master lists every single item in the book. In your case, each person would be given an ID number (autonumber) in the (for example) tblPeople. The record would include all his particulars: name, gender, date of birth, location, ad infinitum.
The parts list (for example, tblTreeData) has a single item from tblPeople, given an ID number (autonumber). Every person from tblPeople that is on the tree can be added to the list by adding his id number as a foreign key. You also need additional fields to identify generation, relation (spouse or sibling), etc.
Think this way: I myself am a member of a family tree on a genealogy website that has over 2,000 people, and at least 7 generations, on the tree.
Take a look at some of the genealogy websites for some ideas as to format, etc.
From a database perspective, in its simplest form, you only need to know the items parent to create the tree hierarchy. This works great for a list of parts, but families are not that simple and therefore neither is the structure.
A person can have biological parents and adoptive parents. The adoptive parents could get divorced and remarried creating more decisions. There are people who are married, divorced, and remarried to a sibling of their first spouse. We have single parent families where one parent is unknown, families with two parents of the same sex, and children who live in foster families. There are family relationships that don't even have proper names... what do you call the children of your siblings spouse from a previous marriage?
The point is, you can make this as complicated or as simple as you want. You just need to be aware that when you go for a simpler path you may end up with scenarios that don't fit your database. The problem occurs when people decide to remove the database constraints to fit in data that the system wasn't designed to hold.