sql - 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

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