Ok, I think I am understanding. So are you saying that I will need another table if this is the case? Say I have antiques and collectible and it has a sub_category of furniture. Since not all furniture is antique or collectible would that require another table?
----- 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:250516 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
