1

I have a product table "products"

products
- proID (PK)
- proName
- proDescription
- proPrice
- proSize
- proStatus

Now I need to add two extra properties (type and color) and in the future even more. In the futere I can get more different products, each with their own properties.

The two extra properties only apply to some (half) of the products in the products table.

Is it better to add the properties to the products table or create a seperate table to store these properties and values?

I could store them like:

product_properties
- pprID (PK)
- pprName

product_properties_values
- pprID (PK)
- proID (PK)
- ppvValue

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Roy Roes
  • 55
  • 6

4 Answers4

1

If you want to have a normalized schema, you could have a product_type table listing all these properties a product can have.

product_type table:

  • type_id
  • type_name
  • type_property_1
  • type_property_2
  • etc.

and the product table:

  • product_id
  • type_id
  • etc.

The benefit of having a separate type table is that it will be probably small hence it will be relatively easier to make changes like adding new properties in the future. Also it will save disk space.

The benefit of not having separate type table is you do not have to join two tables just to list products with properties, that is the query performance will be better.

bpgergo
  • 15,669
  • 5
  • 44
  • 68
1

Another way is used field with TEXT which stores all fields in xml or json format. In our project we use xml because we need searching by property. You can search by any property using ExtractValue mysql function.

See http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_extractvalue

But if you needn't search json will be more fast.

Andrej
  • 7,474
  • 1
  • 19
  • 21
1

It's a Entity - Attribute - Value problem. For a better scalability you should use one table for the products, one for the properties and one for the values. As you wrote:

product_properties

  • pprID (PK)
  • pprName

product_properties_values

  • pprID (PK)
  • proID (PK)
  • ppvValue

You can easily query the tables with mysql left join statement. For example what are the product X properties?

select ppr.pprName, ppv.ppvValue
from products as pro 
left join product_properties_values as ppv on pro.proID=ppv.proID 
left join product_properties as ppr on ppr.pprID=ppv.pprID 
where pro.proName='X' order by ppr.pprName desc
jbrond
  • 727
  • 8
  • 20
0

This sounds like an EAV model (Entity-Attribute-Value).
You can google for more info about it.
Some people like EAV, but many think it is a poor design of the database.

I would suggest you read this thread about database design and EAV:
single fixed table with multiple columns vs flexible abstract tables

Community
  • 1
  • 1
Andrey
  • 1,808
  • 1
  • 16
  • 28
  • 1
    In my personal opinion there is absolutely nothing wrong with EAV as long as it is used properly. – Andrey Aug 25 '11 at 13:22
  • How would you make an EAV property `NOT NULL`? Or link a property to a lookup table? Or make sure no application can enter a string in a property that you intended to be an integer? – Bill Karwin Aug 25 '11 at 14:22