Multiple products, categories, table setup?


I’m building a site with four different product types.

so I have a “products” table like this:
id, name, price, description, main_category, sub_cat1, sub_cat2, sub_cat3, price, weight, year

There are four main categories of Products:
pens, paper, pencils and erasers.

Each category of product has its own set of unique sub categories. For example,

the pens subcats would be like this:
color: red, blue, green, black
material: plastic, metal
size: small, medium, large

the paper subcats would be totally different:
size: b4, a4, b3, a3
usage: text, photos

the pencils would have its own unique:
typeOfWood: pine, ash, balsa
lead: 2b, 3b, 4b
color: natural, green, purple

the erasers would have its own set of cats as well…

The last few sites I’ve built have been based around one type of product, not multiple product types. I’ve used just one main “products” table to hold product info. What should I do here? Create for different tables for each product type?

what about each products’ categories? (which can get confusing quick, especially when it’s a real product and not just my simplified example here. ;))

any help greatly appreciated.


Normalize your database.

thanks Guido! that’s it.