A family Tree ? Say that word again .... tree . That is exactly what you
want a tree structure.
Go out and buy Joe Clecko book "SQL for Smarties"... He has examples of the
exact thing you are after.. with optimised querys etc...
Basically you need to de-normalise the tables a bit , Joe adds a row called
"left" and "Right"
You then use these values to get data .
For eg select * from tree where left = 1 gets you the root node.,
Buy the book, lots of great tip in there...
Justin
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 17, 2001 3:30 PM
To: CF-Talk
Subject: Database Design Challenge!
If anyone can solve this one, I'll give you a million
bucks. Or maybe not, but I'll certainly be impressed...
Let's say, for instance, I'm storing data about a family
tree. Every person has a record in the People table. Every
person has a Mother and a Father, linked with their unique
ID.
Now the challenge: How can I create the list of all male
ancestors, all the way back to the root level (i.e. Adam &
Eve)? I've managed to do this with a CFLOOP that keeps
running a query, going up the tree, until the FatherID is
zero, but this is expensive on the database end.
For the record, I'm not actually keeping track of people
all the way back to Adam & Eve, but this is the best way to
explain this database design issue.
Thank you very much!
Norman Elton
Jefferson Labs
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
