Database Design Question - Categories / Subcategories

Question

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/Subcategory. 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.

Answer 1

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.