Data model, saving product properties in a separate table?

Question

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

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