The one table design that Michael is describing will hold all your categories and sub-categories, as well as any further nesting that you might need.
For example: Cat_id, category_name, parent_id 1, category 1, NULL 2, category 2, NULL .... 25, sub-category 1, 1 26, sub-category 2, 1 .... 49, sub-category 49, 2 .... 200, sub-sub category 1, 2 Etc. This single table design lets you nest sub-categories as deep as you need to go. As someone previously stated, the major drawback to this design is that you can only have one parent for each item. If you need an item to have more than one parent, you would have to develop a two table design. Hope that clears things up a bit. Rich Kroll -----Original Message----- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Monday, August 21, 2006 3:26 PM To: CF-Talk Subject: Re: Database design question Thanks michael, but if I do it that way, will I not have hundreds of tables? I currently have about 24 categories and each category has prob 10-50 sub_categories and then each sub_category has it's own set of sub_categories. ----- Original Message ----- From: "Michael E. Carluen" <[EMAIL PROTECTED]> To: "CF-Talk" <cf-talk@houseoffusion.com> Sent: Monday, August 21, 2006 12:53 PM Subject: RE: Database design question > Doug, yYou can actually use a single table for that. One way is to create a > field that serves as a "parent_id". > > Example: > > ID: 1, NAME: Antiques and Vintages, PARENT_ID: 0 > ID: 2, NAME: Antique Furniture, PARENT_ID: 1 > ID: 3, NAME: Vintage Cars, PARENT_ID: 1 > ID: 4, NAME: Hutches, PARENT_ID: 2 > ID: 5, NAME: DeSoto, PARENT_ID: 3 > > Hope that makes sense, Doug. > > Michael > > > > -----Original Message----- > > From: Doug Brown [mailto:[EMAIL PROTECTED] > > Sent: Monday, August 21, 2006 11:16 AM > > To: CF-Talk > > Subject: Database design question > > > > On my classifieds database it will have... > > > > categories and corresponding sub_categories and corresponding > > sub_sub_categories. How would you design the table names and relationships > > to avoid confusion? Kinda new to database design!! > > > > IE: > > > > Antiques-category > > antique furniture - sub_category > > hutches - sub_sub_category > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250500 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
