I do something similar and the nice thing is depending on the database being used you can sometimes take advantage of the built in hierarchy features. Oracle has some of these features and it makes it rather easy for pulling things out of our hierarchy for display purposes.
On 6/4/07, Brad Wood <[EMAIL PROTECTED]> wrote: > > If I were you I would just use a single table with a parent_id concept. > That is how we store our contact manager information. All companies, > departments, and contacts are all an "entity" and they can be nested n > levels deep. We have an entity table with a type (contact, department, > company etc.) and a entity_id_parent column which points to the parent > record. Records with a NULL parent at top level. > > Depending on how many levels you have it can get tricky to figure out > ancestor or descendant relationships, but recursion lends itself nicely > to this kind of data. The best part is it has no restrictions. > > ~Brad > > -----Original Message----- > From: Will Tomlinson [mailto:[EMAIL PROTECTED] > Sent: Monday, June 04, 2007 7:30 PM > To: CF-Talk > Subject: OT: How best to represent this hierarchy of data > > I'm trying to replace a rigidly built db of data that includes > divisions, which contains departments, which contains programs. > > I need my users to have the ability to build their own tiers of data. > Ex. Some users might not even HAVE divisions. They might call it > something entirely different, so this system should allow for that kind > of flexibility. > > What I've come up with is adding 5 tables of data. > > tblTier1 > --------- > tier1code PK > tier1title > more fields > tier2code > > tblTier2 > --------- > tier2code PK > tier2title > more fields > tier3code > > tblTier3 > --------- > tier3code PK > tier3title > more fields > tier4code > > etc.. etc.. > > This would allow you to add a bottom tier of data. Then add a tier above > containing codes from the tier just underneath, and so on. > > has anyone else tackled this? Are there better solutions? > > Thanks, > Will > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280064 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
