I searched all over stackoverflow but I can't seem to find an answer to my problem.
This problem is related to an e-commerce website
Here's the problem:
A product from a specific category has a specific set of attributes. For example, a Computer has Ram, CPU, screen size ...etc and a shirt has Size,Color ...Etc
There are attributes common to all products (e.g. price and stock etc.).
But how do I manage attributes specific to each product within its category?
At first I made a table for each category of a product where all the attributes are listed, but then I had a problem with filtering each product with its attributes. I'm sure that this is not a good idea because I have to create a new table when getting a new category. For example, if I wanted to sell books in my website I would need to add a books table
What way would be "best practice"?
Here's an example of what i did:
product (idproduct, name,price,...(other attributes))
category (idcat,name,idParentCategory)
ParentCategory(idPcat)
PC(idPc,Ram,CPU,screenSize..etc)
Shirt(idShirt,size,Color)
Pants(idPant,waist,color)
The problem here is that I cannot get a connection between the product table and its category in order to extract the propreties.
Is there a better solution ?
Thank you for your help in advance
There are attributes common to all products (e.g. price and stock etc.).
Create a table for this with an appropriate real life key e.g. EAN (consider using a third party data source for this).
But how do I manage attributes specific to each product within its category? ... if I wanted to sell books in my website I would need to add a books table
Yes and the attributes specific to books would go here. Remember to create a reference to your 'attributes common to all products' table e.g. ensure the EAN correcponds to an valid ISBN. Also consider that some book attributes might be common to other similar products, so you could have various subtypes modelled in your database.