4

This question already has an answer here:

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.

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.

marked as duplicate by Philᵀᴹ, Michael Green, JNK Jan 16 '15 at 17:16

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

11

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.

  • "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 '14 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 – Baby in Database Sep 22 '16 at 7:50
  • if we use eav and we face performance problems than how to resolve this ? – Baby in Database Sep 22 '16 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. – FrustratedWithFormsDesigner Sep 22 '16 at 13:48

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