[EMAIL PROTECTED] wrote on 06/30/2005 11:59:43 AM: > news <[EMAIL PROTECTED]> wrote on 30/06/2005 16:47:43: > > > > I'm designing a simple family tree db which is at present just a flat > table > > In which each record everyone has a father & mother, a variable > > number of wives,and variable number > > of children. > > The links to other family tree members is always the record_id of > > another record. > > > > At present I'm manually inserting the "links" eg my father is > > record_id 52, my mother recordid 60 my > > children records 100,101,102 > > > > I can write queries to display everyone's father mother children etc > > no problem. > > > > * But My question is > > > > Is it possible/useful to define any sort of relationships/linking > > the wife/father/mother/children > > fields and the recordid of other records > > > > I'm fogging on this, your advice pls > > This is a link, previously recommended on this list, which I have found > very useful: > > http://www.sitepoint.com/article/hierarchical-data-database > > Alec >
I agree with Alec. Why try to flatten a tree when you don't need to? However, you are going to need to expand on that article to make it fit into a genealogy application. (more on that in a sec) To answer your question about storing the "relationship" information I would give a qualified "yes" as you only need to distinguish between parent-child relationships (births and adoptions) and same-level relationships (marriages) and when they existed (emancipated minors, placed for adoption, marriage, or divorce). I didn't say anything about sibling relationships (brothers, sisters), aunts/uncles, or cousins. Those are "relationships" based on relative positions in the tree and don't necessarily require that a direct linkage between any two people to exist. What relationship someone is to someone else is related to how you navigate from one person node to another: PersonA is a first cousin to PersonB if you navigate from A to B along the following path: Move up two levels (grandparents) then down to a node not already visited (you would now be at a sibling node to one of PersonA's parents, an Aunt or Uncle). Move down one more level. Here is another. PersonB is the sister-in law of PersonA if you can navigate either of the following paths from A to B: Change nodes laterally through a marriage (the spouse of A). Move up one level. Move down one level to a female node. You would now be at the sister of A's spouse. -- or -- Move up one level (a parent of A). Move down one level to any node other than PersonA( a sibling). Move laterally by marriage to a female node. Now you are at the wife of a sibling to A. If you wanted to try to avoid the UP-DOWN navigation required to locate to a sibling, I think that including that direct linkage in your tree could get messy. What if a family had 8 children. That means that (worst case) each of the 8 children would need an entry "sibling-linking" them to each of the other 7. That's 56 additional records to avoid one up-link navigation and a conditional down-link navigation. The "sibling" links would probably mean faster lookups (like an index) but they would definitely add more data overhead (also like an index). It's your call. The major complexity with making a true family tree isn't in the down-links or the side-links, it's in navigating both up and down the tree starting from a single person (2 parents, 2 sets of grandparents (and their descendants), 4 sets of great-grandparents(and their descendants), etc) that turns this kind of relationship database from a single tree into multiple sets of interlocking trees (especially if you have cousins that marry each other). Odds are your database will need to be less tree-oriented and more network-oriented . The article is a great place to start (I very highly recommend it, too) but you are going to find the need to quickly expand your single tree into a network (someone help me here. I know that may not be the "correct" term for what he is modeling but it's the closest name I can think of) and that will probably change your node numbering algorithms (read the article) in significant ways. Still, start with the tree so that you get used to thinking in terms of nodes and links. It's going to work loads better than the flat design you have now. I will do some research (it's related to what I need for my job anyway) about storing and modeling networks in relational databases and reply again with links when I find some good ones (may not be today). Does anyone else already have links on this topic? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
