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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280059 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
