Mark,
If you look at the Celko book _SQL For Smarties_ all of the options
discussed here are discussed at length.
Using parent-child relationships requires the use of recursive SQL
to traverse the tree, exept in cases where RDBMS vendors have
provided extensions like Oracle's CONNECT BY clause. The problem
is if you arrive at any particular point in the hierarchy how do you
determine who your parents, grandparents, etc are as well as your
children and which of those children has children. The nested set
model solves all of these problems and also allows you to easily
calculate other properties of the tree from any node on the tree.
I have walked through this same issue a number of times now in
different scenarios, the nested set model has always been able to
solve all of the problems I have encountered with moving nodes,
adding parents/children, even multiple inheritance (a map, not a
tree, Celko also writes at length about these models). The model is
also quite efficient, which any type of recursive SQL will never be.
I hate to say it, because it sounds trite, but trust me on this one.
Check it out, its worth the time spent understanding how it works
because its a classic problem (Bill of Materials Explosion or BOM)
that will appear over and over again in your career.
-dhs
--
Dean H. Saxe
[EMAIL PROTECTED]
http://www.FullFrontalNerdity.com/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
