Q:

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.

A:

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.

