Database Design Question - Categories / Subcategories

I have a question for how I would design a few tables in my database. I have a table to track Categories and one for Subcategories:

TABLE Category
    CategoryID INT
    Description NVARCHAR(500)

TABLE Subcategory
    SubcategoryID INT
    CategoryID INT
    Description NVARCHAR(500)

A category might be something like Electronics, and its Subcategories might be DVD Players, Televisions, etc.

I have another table that is going to be referencing the Category/Subca开发者_Python百科tegory. Does it need to reference the SubcategoryID?

TABLE Product
    SubcategoryID INT  -- should this be subcategory?

Is there a better way to do this or is this the right way? I'm not much of a database design guy. I'm using SQL Server 2008 R2 if that matters.

Your design is appropriate. I'm a database guy turned developer, so I can understand the inclination to have Category and SubCategory in one table, but you can never go wrong by KISS.

Unless extreme performance or infinite hierarchy is a requirement (I'm guessing not), you're good to go.

If being able to associate multiple subcategories with a product is a requirement, to @Mikael's point, you would need a set-up like this which creates a many-to-many relationship via a join/intersect table, Product_SubCategory:

CREATE TABLE Product (ProductID int, Description nvarchar(100))
CREATE TABLE Product_SubCategory (ProductID int, SubCategoryID int)
CREATE TABLE SubCategory (SubCategoryID int, CategoryID int, Description nvarchar(100))
CREATE TABLE Category (CategoryID int, Description nvarchar(100))

Hope that helps...

Eric Tarasoff

Having two separate tables for Categories and SubCategories depends on your situation.

If you keep it the way it is you are limited to a Category > Subcategory scenario, as in you can't have SubCategories of SubCategories.

If you make them into one table you need a column for ParentID. If a category is the top most it will have a ParentID of 0. If you want to allow unlimited sub categories foreach subcategory, e.g. Electronics > Recordable Media, Blueray, 4gb you will need to use recursive programming to display them.

Attach tags to the products in instead of a category hierarchy. It is much more flexible.

create table product (id, name,...)
create table tag (id, name, description)
create table product_tag (product_id, tag_id)

If categories and subcategories have the same attributes, then collapse them into one table.

If one 'sub' category can belong to more than one 'parent' category then add a link class, otherwise add a single column to point to a parent.

e.g. if you have Electronics > TV, can you also have Entertainment > TV ? etc.

Your other table should reference just the category_id (note - not parent_category_id)

hth

As long as Sub-Categories are never repeated in a different Category, and especially if they have different attributes, then your proposed method is good.

The one problem can come when you are adding/editing Products, and you don't have a field for Category, even though you probably want a control where the user can edit the Category.

It depends on your requirements. If every Product is linked to no more than one SubCategory you should have SubCategoryID in Products. There is no need to add CategoryID as well.

Other scenarios that require a different model might be that a Product could link directly to a Category instead of a SubCategory or that one Product could be linked to more than one SubCategory or that a SubCategory is linked to more than one Category.