DB Structure for a shopping cart

Question

I like to develop a shopping cart website with multiple products. (ex.: mobile phone, furniture etc.,) here mobile phone specification will cover

• size of display
• memory
• operating system
• camera etc.,

but for furniture - its specification is entirely different from above electronic product.

• type of wood
• color
• weight
• shape
• glass or mat finish etc.,

My question is: how to handle a common database-table for product specification ? each & every category of product & its spec will be differ - so how to have a common table ProductSpecificationTable ?

I searched many site including google.. but cant able to get the perfect soultion. Please help me to move to next step.

Answer 1

Ask yourself the question: How can I accomplish this kind of database? First of all you need products.. Every product has to be in some kind of category and every category has to have his own properties. So, you've to create a product table with unique id and every product needs a category id. At this moment it is time to link from your property table to your category table(by id) and to set the values you need a 'property_value' table.

**table:**         **id**
product        --> category id
property       --> category_id
property_value --> property_id

I hope you will understand my explanation otherwise just ask :)

Answer 2

You can achieve this with a single table within a database but that will complicate the CRUD operations over the table. So I will recommend you to create one database like ‘Inventory’ which can have multiple tables (one table for each of the Product Type).

First Table could be list of Product Types you have (mobile phones, accessories, furniture):

You can use this table to populate your list of items available. Here the column _table_name will contain the actual name of the Tables.

Then for each of the product you can have different tables with different number of columns:

Table for Product Type Mobile Phones:

Table for Product Type Furniture:

I hope this will help.

Answer 3

You can add 1 more table to accomplish that. Table that contains cat_id, product_id and the property. That is a many to many relationship. I believe this way you can accomplish thst.