[Resolved] advice on DB design

[chucklarge]

Hi,
I am trying to figure out how to design my DB. I am using mysql.
On my site, I want to sell a number of products. Each product is unique will fit into at least one category but might also fit into multiple categories. The number of products will change, either new ones added or some deleted. The categories may change over time as well, either new ones added, or some deleted.

At the moment, I have designed the products table to have a unique id and other relevant info about the product.

Now This is where i am having trouble with the category. If the categories were constant, i would just add a few fields to the products table and check where appropriate, but the categories can change. So I am thinking I want a seperate table for all the categories but don't know how to design it so that it can handle all the dynamic products.

Any help is appreciated,
chuck

[ahundiak]

Might want to read up a bit on relational database theory. Lot's of tutorials out there. But basically you need three tables to handle many-to-many relations

TABLE product
INT product_id,
CHAR name

TABLE category
INT category_id
CHAR name

TABLE product_category
INT product_category_id
INT product_id
INT category_id

There will be one record in product_category for each combination of product and category.