CODEWITHSUNDEEP
sqlsql-serverdatabase-designrelational-database
Alvin
Alvin

Reputation: 109

Database Design - Multiple Category and Multiple Sub Category

I have a question on how to design for multiple category and multiple sub category. A store will have multiple Categories and multiple sub categories.

For example, Stores will have Women's clothing, Men's Clothing as categories and have Accessories and bottoms as sub categories

If it matters, I'm using SQL Server 2016. These are the tables I have so far:

TABLE Store
    StoreID INT
    Description NVARCHAR(500)

TABLE Category
    CategoryID INT
    Description NVARCHAR(500)

TABLE Subcategory
    SubcategoryID INT
    CategoryID INT
    Description NVARCHAR(500)

Upvotes: 0

Views: 5762

Answers (2)

Jerrad
Jerrad

Reputation: 5290

I don't think there's any value to having separate Category and SubCategory tables. Just have a single Category table, with a nullable ParentCategory column.

TABLE Category      
  CategoryID INT
  ParentCategory INT
  Description NVARCHAR(500)

Your top-level categories will be the ones with null for ParentCategory.

You say that there will not be multiple levels of subcategories now, but requirements like that have a tendency to change down the road. If you do end up needing sub-sub-categories at some point, you won't have to rewrite a bunch of queries with a design like this.

Then you will need a table to store the categories for the stores:

TABLE StoreCategory
  StoreID INT
  CategoryID INT

Upvotes: 1

Hybris95
Hybris95

Reputation: 2400

Just create another table StoreCategories, that will keep the StoreID and CategoryID to allow each Store to have multiple Categories and each Category to have multiple Stores.

Make sure Foreign Keys are properly placed for each tables. UML Stores/Categories Another intermediary table can be placed if a SubCategory can be used on multiple Categories.
Obviously to avoid double SubCategories.

Upvotes: 1

Related Questions