Hello,

I'm trying to set up a database for a website and need some help as I am starting to bang my head against the wall. Here is the situation:

There are 5 categories, each has between 5-10 subcategories

There are infinite amount of products.

Below are some of the options I have come up with. Which is the best and why? (Also Is there any difference between the first 2)

-------------------------------------

1. 
   Categories
   category_id (PK)
   category
   category_description
   
   Subcategories
   category_fk
   subcategory_id (PK)
   subcategory
   subcategory_description
   
   Products
   category_fk
   subcategory_fk
   product_id (PK)
   product_description
   
2. 
   Categories
   category_id (PK)
   category
   category_description
   
   Subcategories
   category_id(PK)
   subcategory_id (PK)
   subcategory
   subcategory_description
   
   Products
   category_id(PK)
   subcategory_id(PK)
   product_id (PK)
   product_description
   
3. 
   Categories
   category_id (PK)
   category
   category_description
   
   Subcategories
   subcategory_id (PK)
   subcategory
   subcategory_description
   
   crosswalk table
   category_subcategory_id (PK)
   category_id
   subcategory_id
   
   Products
   product_id (PK)
   category_subcategory_id
   product_description
   
   
   
   Also I would like the option to place a product under multiple category/subcategory combinations, if it is not too complicated.
   
4. 
   Categories
   category_id (PK)
   category
   category_description
   
   Subcategories
   subcategory_id (PK)
   subcategory
   subcategory_description
   
   crosswalk table
   category_subcategory_id (PK)
   category_id
   subcategory_id
   
   Products
   product_id (PK)
   product_description
   
   product_category_subcategory
   product_id (PK)
   category_subcategory_id
   category_subcategory_id2
   category_subcategory_id3
   

Any help would be greatly appreciated. Thanks in advance!

if you need subcategories I'd suggest you change you categories table
categories
CategoryID 'autonumber
Description
ParentCategoryID 'nullable

the parent category is optional if its not defined, then defacto that category is a top level 'master' category.

if you need a product to have mulitle categories then youneed and intersection table

productcategories
ProductID 'PK
categoryID 'PK

this article may help -- Categories and Subcategories

Thanks to both of you for your help. The link had the info I was looking for.
It's actually what I had originally planned to have all in one table. But then I figured it wasn't normalized unless categories and subcategories were in their own table.

The querying of the table is a bit complex. However, I think I will only go 2 deep category --> subcategory so hopefully not too bad.

The article had the queries to generate a menu as well as breadcrumbs. good stuff.

I'm working on the design now. When I get up to writing the queries I may be back .

thanks