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:
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
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?