Mysql – Design a website database structure

database-designMySQL

I am really stuck on the database structure of the shopping cart website. I am trying to create a sample shopping project for learning purpose where I am getting heavily stucked.

Here is my sample database design:

Products Table:

id    name           description    price   productType   ....
1     Sony Mobile    Sony Mobile    15000    mobile
2     Sony TV        Sony TV        35000    tv
3     LG WM          LG WM           7500    Washing Machine
4     Levovo         Lenovo Laptop  20000    Laptop

When the user search for any particular product, he will also get the filter options which will different according to productType.

For example, if the product, the user is searching is mobile smartphone, then the filters available to him should be 

price range: 1000 - 100000
Camera:      2px  - 20px ..
Wifi:        yes/no
Bluetoooth:  yes/no
GPS:         yes/no
..

if the product is Laptop, then the filters will be different from mobile phone i.e.

price range: 15000 - 300000
Harddisk:    320GB - 2TB
RAM:         1GB   - 64GB
OS:          Linux
..

Same, the filters will vary from washing machine to TV. The problem is, where could these filters should be stored in the database. What could be the design of the database?

Another problem is, when the user visits the product's page like mobile, he will see the complete details of mobile. For example:

Size:
Battery:
images:
Removable battery:
...

I cannot store all these details of mobile in the above products table since the product table not only contains mobile but also other types of products where these details doesn't match. Example, Washing machine doesn't contains the parameter Removal battery. So, there will be a number of unnecessary columns get generated in the products table.

Now I am confused, where should I store this product details in the database?

First way could be to create one column in the products table which store all these information as json object.

Second could be to generate tables dynamically for each product type i.e. a table for all the mobiles, a table for all the laptops, etc. which should contains all its necessary fields. But this solution is quite complex & I am trying to avoid it.

Third would be to generate tables statically while coding for each possible product type. Here, the problem is that, suppose in future, if any new product type is introduced like books, then again, I will have to code it in the program while the site is live. So, this solution is not flexible.

So, how can I solve these two problem i.e. filters & showing product's details to the user?

Thanks

Best Answer

What you could do is a design like this:

Product(id,name,description,price,productType)
ProductAttribute(productId,attributeName,value)

Then you would have the possibility to filter for all attributes that are currently used in your database.

If you want to be able to filter for all attributes, even if they are not used in any product at the moment, you'll have to add a table

AttributesForProductType(productType,attributeName)

which contains all possible attributes, and then set up referential integrity (foreign keys) between AttributesForProductType and ProductAttribute. This way you lose the risk of misspelling an attribute name in the ProductAttribute table.

edit: If you take this design this also solves your second question: How to display a details page for every possible product type?

All possible attributes for this product are

select attributeName
from AttributesForProductType
where productType=?

all real attributes for a product with their values are 

select attributeName, value
from ProductAttribute
where id=?

and those combined are (off of my head, untested)

select apt.attributeName, pa.value
from Product as p
left outer join AttributesForProductType as apt on p.productType=apt.ProductType
left outer join ProductAttribute as pa on apt.attributeName=pa.attributeName
where p.id=?