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

Question

Hi, 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
}

Thanks, Yosef

Answer 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).

Answer 2

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.