At 08:54 PM 6/4/2007, you 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
Also something to consider is a 'sortNum' tblTier1 --------- tier1code PK tier1title [more fields] tier2code / parentID sortnum varchar(50) * 01 02 0201 020101 020102 0202 020201 03 04 0401 05 0501 050101 05010101 0501010101 050101010101 050101010102 0501010102 0501010103 0501010104 0501010105 050101010501 050101010502 05010102 05010103 0501010301 0501010302 0502 0503 06 07 Using "like" in your queries allows for all sorts of familial ancestry and descendants revelations. It may be a little redundant considering you've already got parentID, but for displaying deep descendantry quickly, it's pretty easy. Also it helps sort your entities. Moving them up and down can be tricky, though, especially when it involves a lot of children and siblings. Boy, I'll tell you, that 05 entity was BUSY. Mik * I think 25 levels deep is probably enough, no? And up to 99 children per entity? -------- Michael Muller Admin, MontagueMA.net Website work (413) 863-0030 cell (413) 320-5336 skype: michaelBmuller http://www.MontagueMA.net Eschew Obfuscation ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:280070 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
