Database design for eCommerce website with multiple products having huge specifications index I am designing a web site for online shopping and I have a question related to that. As you know that shopping website may have different product with different statistic/specification. What is the best way to design a database? Below are the thoughts which comes to my mind


Creating different tables for different product type. Ex. Phone, Tv, Video Games, Watches, Clothes, Computer etc.



  Concern:


but let's say a to show mobile specification [just like this example] I need to have around 40-50 columns to store a mobile specification. And let's say tomorrow one new feature comes to mobile then I probably have to add one more column which will hold the value for that specific model and for every other mobile that column will be NULL. 

2: Having major product tables ex. Electronics, clothing and accessories, health etc.. and a separate specification table for each table. Ex. Electronics table will hold the master details for tablets, mobile, land line etc. and Electronics_Spec will contains the columns with related specification.


  Concern:


There are many characteristic between products which does not match with others. Ex. land line phone will have a specification about wire length while as tablets won't. 


  General concerns:


There are certain products which has quite less specifications. Ex: bed, toys , footwear.

Is there any other better way to design a database to hold huge specifications related to multiple products? 

My intention is to save database space to the best.
I sincerely apologize for my bad English.
You could have a basic product table, then also a table to store different types of specs, then another table to store the specs themselves:


products
--------
  id
  name
  (a few other necessary fields)

product_spec_types
------------------
  id
  name

product_spec_values
-------------------
  id
  product_id (FK to product.id)
  spec_id (FK to product_spec_types.id)
  value


So you could have data that looks like this:


product
ID  |   NAME
----|-----------
1   |   TV
2   | kids boots

product_spec_types
ID  |  name
------------------
1   | refresh rate
2   | size (diagonal)
3   | shoe size
4   | colour

product_spec_values
ID  | Product_ID   | Spec_ID  | Value
-------------------------------------
1   | 1            | 1        | 60 HZ
2   | 1            | 2        | 32 inches
3   | 2            | 3        | 4
4   | 2            | 4        | blue w/grey



This is a very simple implementation of the Entity-Attribute-Value pattern. You could go further if you want, and add data type restrictions (such as numeric, alpha-numeric, enumerations, etc...) to product_spec_types

The main benefit of this approach is that it can be extrmemely flexible.

The downside of this approach is that it is not always fast for searching, since all attributes for all products are in one table. Sometimes it is better to create separate tables that your user's searches will run off of, and those tables will have real columns for all the attributes you want them to be able to search.