----- Original Message -----
From: Geoff Bowers
To: CF-Talk
Sent: Monday, October 13, 2003 7:15 AM
Subject: Re: ACL database design
Hugo Ahlenius wrote:
> As the SQL guru, how do I design a query with a recursive table (unlimited
> parent/child relationships). So that i.e. a group has a foreign key to the
> parent group. So that I can, in one query get all the parents of a
> specific group (a recursive join?)
Google "joe celko nested tree model".
This is a good start:
http://www.intelligententerprise.com/001020/celko.shtml
If you want to see this sort of thing in action we use it in FarCry CMS
(http://farcry.daemon.com.au/) to manage the n-depth tree model we use
for site management. We have a tree.cfc that wraps the code up in
methods allowing you to getdescendants(): all nodes under a single
parent to any depth, getAncestors(): all parents up the the root, and so on.
Its great. Instead of traversing the tree (look up the parent and the
parents parent, etc) you just need a single SELECT statement.
Lightening fast.
-- geoff bowers
Managing Director
Daemon Internet Consultants
Macromedia Premier Solutions Partner
http://www.daemon.com.au/
p. 02 9380 4162
f. 02 9380 4204
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
