I think Clint's description was pretty good--the direction of the relationship doesn't really matter so long as you account for one person being related to another in some way.
Completely off the top of my head you might do something like this for your tables. ROLE TABLE: Columns: id, role Sample Data: 1 Father 2 Mother 3 Uncle PERSON TABLE: Columns: id + the usual stuff (first name, last name, etc.) RELATIONSHIP TABLE: Columns: person_id, relation_id (another person id), role_id Sample Data: 1 2 1 -- this would indicate that the person with id 1 is the father of the person with id 2 Now where this gets cumbersome is do you also have to account for (using the example above) the fact that if the person with id 1 is the father of the person with id 2, that makes the person with id 2 the son or daughter of person id 1? Either you'd have to explicitly set that (not necessarily make the user do it, but behind the scenes insert another record in the relationship table to account for this), or you'd have to do some pretty heavy coding in the app itself so it "understands" what a family relationship is and the inverse relationships involved. Obviously others have thought through this before so you might want to check out some of the open source applications available. Even if you don't know Java or PHP you could study the databases involved. http://sourceforge.net/projects/phpmyfamily/ http://sourceforge.net/projects/lifelines/ http://sourceforge.net/projects/genj/ http://sourceforge.net/projects/genealogy/ http://sourceforge.net/projects/gdbi/ http://sourceforge.net/projects/poplar/ http://sourceforge.net/projects/mynukegenealogy/ http://sourceforge.net/projects/sqlfamily/ http://sourceforge.net/projects/phpfamilytree/ There is also a standard export format for geneology files called GEDCOM that might shed some light on how these are organized. Matt On Tue, 01 Mar 2005 08:53:40 -0600, Jake McKee <[EMAIL PROTECTED]> wrote: > Yeah, except that there aren't cross relationships in an org chart. It's > really just up and down, not side to side. There are rare exceptions and > dotted lines, of course. But on a family tree, the part (at least in my > mind) that makes it tricky is the fact that there are 4 directions of > solid, constant relationships. > > Jake > > Ken Ferguson wrote: > > >Think of it in another way: a family tree is just an org chart. There > >are tons of apps out there that present org charts. Grandparents may be > >the equivalent of executives, parents of managers, children of > >workers... It's also very similar to thinking of products within > >subcategories within categories within product lines. > > > >Like Clint says, it's just a matter of getting the relationships > >straight in the db and figuring out the hierarchy from there. I would do > >something like linking every person's record with his owner or parent. > >So if I select the family tree for Ferguson, I'm going to start with > >someone at the top and I'll be able to get all of the people with > >him/her (or a couple) as their owner. You could have two "owner" fields > >to get mom and dad... It should be simple enough to follow that query > >down to little Lauren who, as the youngest Ferguson, has nobody calling > >her the owner. I would fall easily in line with my Brother, as we've the > >same two owners... It's also now very simple to see, specifically who my > >cousins are by querying for a list of people whose owners have the same > >owners as my owners (this would be much easier on a whiteboard). > > > >Do you get where I'm coming from??? > >--Ferg > > > > > > > > > >-----Original Message----- > >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > >Behalf Of Clint Tredway > >Sent: Tuesday, March 01, 2005 8:26 AM > >To: [email protected] > >Subject: Re: Family Tree? > > > >this is done by setting up the people relationships correctly. I can > >think of 2 ways off the top of my head on how to do this, but it > >depends on your SQL knowledge as to which one I would use. > > > >The most basic way would be to have a main Person table and a linking > >table PersonPerson table. The linking table can store the link between > >people and the type of relationship (uncle,cousin,friend,etc)... > > > >The more advanced way would be to have a vertical table where all > >people are stored and you would loop through the table to get the > >relationships... > > > >Granted this is an high level desc but you should see what I am talking > >about. > > > >On Tue, 01 Mar 2005 08:12:48 -0600, Jake McKee <[EMAIL PROTECTED]> > >wrote: > > > > > >>All, > >> > >>Has anyone seen a CF app that allows you to build your family tree? > >> > >>If not, how would you go about structuring a database to allow an > >>application to build a family tree, and show the connections between > >>people... meaning how can I show that Joe is not only my cousin, but > >>Sue's uncle, and Jimmy's son... etc.? > >> > >>Thanks! > >>Jake > >> > >>---------------------------------------------------------- > >>To post, send email to [email protected] > >>To unsubscribe: > >> http://www.dfwcfug.org/form_MemberUnsubscribe.cfm > >>To subscribe: > >> http://www.dfwcfug.org/form_MemberRegistration.cfm > >> > >> > >> > >> > > > > > > > > > > ---------------------------------------------------------- > To post, send email to [email protected] > To unsubscribe: > http://www.dfwcfug.org/form_MemberUnsubscribe.cfm > To subscribe: > http://www.dfwcfug.org/form_MemberRegistration.cfm > > -- Matt Woodward [EMAIL PROTECTED] http://www.mattwoodward.com ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm
