I am trying to figure out how to set up this database.
Basically, there are products with their associated fields. Each product can belong to multiple categories, and each category also has subcategories.
So far I have the following, but not sure if this is the best way to set it up...
TABLE Products:
product_id (int)(1-many relationship to product_id in Table Product_Category)
sku (int)
description
price
TABLE Category
category_id (int)(1-many relationship to category_id in Table Product_Category)
name
TABLE SubCategory
subcategory_id (int)(1-many relationship to subcategory_id in Table Product_Category)
category_id (int)
name
TABLE Product_Category
prodcat_id (int)
product_id (int)(many-1 relationship to product_id in Table Products)
category_id (int)(many-1 relationship to category_id in Table Category)
subcategory_id (int)(many-1 relationship to subcategory_id in Table SubCategory)
Thanks,
Mick
TABLE Products:
product_id (int)(1-many relationship to product_id in Table Product_Category)
sku (int)
description
price
TABLE Categories
category_id (int)(1-many relationship to category_id in Table Product_Category)
parent (int) (1-1 relationship with Category)
name
TABLE Product_Categories
prodcat_id (int)
product_id (int)(many-1 relationship to product_id in Table Products)
category_id (int)(many-1 relationship to category_id in Table Category)
Categories table would look like:
1,0,furnature
2,1,chairs
3,1,tables
4,0,automobiles
5,4,pickups
6,4,minivans
You can then also (If you need/want)
7,6,buick
Which would then be Category automobiles, Sub-Category minivans, Sub-Sub-Category buick
