CODEWITHSUNDEEP
sqldatabasedatabase-design
Tom
Tom

Reputation: 21

Category Database Design

I'm new to database design and I need some help to solve a problem. Sorry if this question has already been answered, i've been searching without success.

This is a simplified scenario of whats going on: It's an ITEM database and each item can belong to one, two, three... or 5 level category structure (hierarchy), examples are:

`Product     | Category1 | Category 2 | Category 3 | Category 4 | Category 5
 ClassicLens   Clothing    Men          Accesories   Summer
 Old Lens      Clothing    Men          Accesories
 Phone 2G      Cell Phones Locked       2G
 Pulp Fiction  Movie       DVD

Lets say I have three tables ITEM, CATEGORY and CATEGORY_HIERARCHY with the following schemas:

ITEM
-----------
Id_Item (PK),
Id_Category_Hierarchy (FK)
Name

CATEGORY_HIERARCHY
------------
Id_Category_Hierarchy (PK),
Id_Category1 (IX) (FK),
Id_Category2 (IX) (FK),
Id_Category3 (IX) (FK),
Id_Category4 (IX) (FK),
Id_Category5 (IX) (FK)

CATEGORY
------------
Id_Category (PK),
Name

As you can see, I force with the Unique Index that only one combination of category1, category2, ... category 5 can exist.

This approach can work but I bet there is a better solution for this scenario. ¿What do you think? Any help/comments will be appreciated!

I can't group all kind of categories in different tables because this would be endless.

Edit: I forgot to mention the database is Access + VBA, would a recursive solution work? I guess SQL Queries will be more complex.

Upvotes: 2

Views: 142

Answers (1)

Abe Miessler
Abe Miessler

Reputation: 85046

I would probably go with something that is more like this:

Product
----------
ProductId
Name

Categories
----------
CategoryId
Name
ParentCategory --Recursive Key

ProductCategories
-----------------
ProductId
CategoryId

Any time you find yourself adding something like columnName_{some number} over and over it's probably time to break that set of columns into it's own table. I can almost guarantee that at some point you will get a request to go from a max of 5 categories to something larger. Using the table structure above will allow you to do this without making any significant changes.

Upvotes: 3

Related Questions