Tree structure in Relational DB design
Date: Fri, 7 Jun 2002 10:25:11 +0100
Message-ID: <adpubd$enp$1_at_hamble.qinetiq.com>
I need to model a hierarchical, treelike relationship in Access. A simple example would be genealogy...lets say we have the following tables:-
tblPeople
ID Name other data
1 Paul ... 2 Peter ... 3 Steve ... 4 Mary ... 5 Emily ... 6 Sarah ...
tblRelationships
ParentID ChildID
3 1 3 5 1 4 6 1 5 2
This seems to me to be a logical, normalised way of structuring the data.
My question, however, is how do I write a query that will list the people by
generation? So the output I'd like would be
Steve or Sarah
Sarah or Steve ... the grandparents (order within the generation doesn't
matter)
Paul or Emily
Emily or Paul ... parents
Peter or Mary
Mary or Peter ... children
I guess I need to somehow calculate a generation value and then simply order
by that...it would be nice if I could get the database to calculate it
instead of asking the user to input it. Or would I be better off with a
different database structure? Any ideas please?
Thanks,
Sean
Received on Fri Jun 07 2002 - 11:25:11 CEST