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:250491 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
