Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

(Database Design - products attributes): What is better option for product attribute database design?

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
}
like image 640
Ben Avatar asked May 31 '10 17:05

Ben


2 Answers

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:

  • Product table, many kinds of product, each product has many parameters for details.
  • How do you model custom attributes of entities?
  • Design question: Filterable attributes, SQL
  • How to design a database schema to support tagging with categories?
  • How to define structure in a tag-based organization?

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

like image 106
Bill Karwin Avatar answered Oct 05 '22 09:10

Bill Karwin


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

like image 28
Esmaile Avatar answered Oct 05 '22 10:10

Esmaile