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

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


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.


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.


1 Answer 1


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:

  (a few other necessary fields)


  product_id (FK to product.id)
  spec_id (FK to product_spec_types.id)

So you could have data that looks like this:

ID  |   NAME
1   |   TV
2   | kids boots

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

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.

  • "The downside of this approach is that it is not always fast for searching, since all attributes for all products are in one table" - why is that bad? If the table is properly indexed...
    – DanMan
    Dec 6, 2014 at 16:23
  • @DanMan what to do if we want to add columns to tables in future ? also dont you think it will create lot of Null values is that okay ? i am just newbie, please guide me Sep 22, 2016 at 7:50
  • if we use eav and we face performance problems than how to resolve this ? Sep 22, 2016 at 10:10
  • @user12345: It depends on what kind of performance problems. If you want to be able to query the data faster, you could build a denormalized data warehouse from your EAV data (often easier said than done ;) ). But in general, performance is one of the trade-offs you must make when you want the flexibility of EAV. Sep 22, 2016 at 13:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.