To simplify what I was getting at in my previous reply, what I was suggesting is this single table (in place of the three you are suggesting):
[categories] cat_id category parent_id [FK_categories] This method allows for unlimited levels of subcategories using only a single table. The top level category would have a null value for the parent_id (it has no parents) The parent_id of any subcategories would be the cat_id of the category of which it is a sub. A couple of simple usage examples: To get all top-level categories: Select * from categories Where parent_id is NULL To get all direct subcategories of a particular category: Select * from categories Where parent_id = 1 (assuming 1 is the cat_id of the parent category in question) Doug B. -----Original Message----- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Monday, August 21, 2006 3:23 PM To: CF-Talk Subject: Re: Database design question Doug thanks for the help. By the way I am Doug too. Right now I have this [categories] cat_id category [sub_categories] sub_cat_id cat_id [FK sub_categories_categories] sub_categories I am also going to be needing to add a table for the sub_categories of the sub_categories table. Hopefully this makes more sense. Would it be something like so [categories] cat_id category [sub_categories] sub_cat_id cat_id [FK sub_categories_categories] sub_category [sub_cat_categories] sub_cat_sub_id sub_cat_id [FK sub_cat_categories] cat_id [FK sub_cat_categories_categories] sub_cat_category Thanks for the help again. ----- Original Message ----- From: "Doug Bezona" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Monday, August 21, 2006 12:43 PM Subject: RE: Database design question > This is going to be a bit tricky to explain without diagrams, but I'll > try and hope it's reasonably clear. > > One approach is use a single category table that is self-referential. > > For example, a table named category, with a categoryid, and a parentid. > Parentid would be a foreign key relationship to categoryid. > > Using your example categories, let's say "Antiques" is categoryid = 1, > with parentid = NULL. Antique Furniture would be, say, categoryid = 2, > with parentid = 1, indicating its parent category is "Antiques" and so > on. > > The only downside here is that if you ever decide you want a subcategory > to be attached to multiple parent categories, you are kind of out of > luck. In that case, you can use a slightly different model, in this case > with two tables: > > 1. A Category table with your categoryid > 2. A CategoryRelationship table with a categoryid which is the id of a > given category record (category), and a parentid which represents the > parent record (subcategory). This allows you to have the same > parent-child hierarchy as the first example, but a given category can > have multiple parents, with each record in the table defining a > particular relationship. > > There are other ways to approach hierarchical data like this, and it's > probably worth Googling around a bit with that in mind. Working with > hierarchical data like this can be tricky depending on how comfortable > you are with SQL, and what RDBMS you are using (Oracle has some great > proprietary methods for dealing with hierarchies - other databases, not > so much) > > What you do absolutely want to avoid is having separate category, sub > category, sub-sub category, etc. tables. > > Doug B. > > > -----Original Message----- > From: Doug Brown [mailto:[EMAIL PROTECTED] > Sent: Monday, August 21, 2006 2:16 PM > 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:250501 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
