13

I new in database design. What is better option for product attribute database design for cms?(Please suggest other options also).

option 1: 1 table

products{
id
product_name
color
price
attribute_name1
attribute_value1
attribute_name2
attribute_value2
attribute_name3
attribute_value3
}

option 2: 3 tables

products{
id
product_name
color
price
}

attribute{
id
name
value
}

products_attribute{
products_id
attribute_id
}
SagarPPanchal
  • 9,839
  • 6
  • 34
  • 62
Ben
  • 25,389
  • 34
  • 109
  • 165
  • How often do the attributes change ? Do you frequently add/delete new ones ? If not then use option 1 with the names of the columns being the attribute name. If your attributes are dynamic then use option 2. Of course for option 2 you will be taking a performance hit. – Romain Hippeau May 31 '10 at 18:20
  • Also as Bill says look at your previous answers and be a good citizen and accept the ones that worked for you. (Just a click of a mouse) – Romain Hippeau May 31 '10 at 18:21
  • I add voting, thanks for telling me.Our web application is dinamic- its mean that every user define his attributes.The problem with option 2 that it will be very slow and with option 1 we can give max like 10 attributes for product that we dont know the name and his value define to user. – Ben May 31 '10 at 21:19

4 Answers4

32

You're making a common mistake of database design, storing name in one column and value in another column. This is not a relational database design.

Each attribute should be named by the column name. Color, pages, shirt size, publish date, should be column names.

If each product type has a distinct set of attributes, there are other solutions. See my answers to:

Also please read this story: Bad CaRMa: Introducing Vision before you implement a database designed around name-value pairs as you are doing.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Hi Bill, I read You links Thanks, I have question: Why do you prefer to use **Class Table Inheritance** and not **Single Table Inheritance**? Please explain, Thanks, Yosef – Ben May 31 '10 at 23:00
  • @Yosef: With Class Table Inheritance it's more clear which groups of columns go together. Also you can add a new subtype table at any time, with its own set of type-specific columns, with no need to alter the parent table that contains columns common to all subtypes. This is important for example for MySQL, where `ALTER TABLE` is an expensive operation. – Bill Karwin Jun 01 '10 at 03:48
4

i think that the best implementation for product attribute you can get is 

Product_Tbl [
    ID
    Name
    more columns
]

Attribute_Tbl [
   ID
   Att_Name
]

Product_Attribute_Tbl [
    Product_ID
    Attribute_ID
    Value
]

if your products not have the same attributes you can use this structure

LPL
  • 16,827
  • 6
  • 51
  • 95
Esmaile
  • 41
  • 1
1

That depends on what do you want from your database. If all of your products are of the same type and have same attributes then you just need to do something like that:

products{id: integer, product_name: string, color: string, attribute_name1: string, attribute_name2: string...}. Attribute_name{} should a meaningful word, just like "color" (which is an attribute too).

parnas
  • 721
  • 5
  • 14
0

This is now an old topic, but thought it might be interesting to think about how this has evolved (especially with more processing speed means performance can be gambled at times).

Have you ever considered storing each attribute as a separate item in table ... lets say table "2", where the key back to the product would be an id:

    Product (table 1)
{
    Product ID
    Product Name
}

    Tags (table 2)
{
    Tag ID
    Higher Level tag ID
    Description
    Value
    Product ID
}

And that this table would contain also a field called "higher level" so you could find the unique ID within this table of which attribute was created as a higher level for this specific product. That way you have something called "omnilevel tagging".

Hope this helps

andyelgrand0
  • 1