3

for a current customer project, i've developed a CMS that stores products.

Products are entries that have some properities like title, text & etc.
However, some clients want to have different properties like price, color & etc.

I try to make my code as generic as possible so i don't want to modify the code
for each customer.

My Question:
How can i design such a database, that a product can have infinite, multiple properties?
How to create/generate forms for such a different design?

Thanks for reading.

Herr
  • 2,725
  • 3
  • 30
  • 36
  • This is a FAQ, you may start here and follow the links http://stackoverflow.com/questions/6768074/extendable-database-schema-how-to-store-the-extendable-attribute-values/6768919#6768919 – Damir Sudarevic Aug 02 '11 at 12:00

2 Answers2

8

You can have a table for properties and a table for owning a property, so your tables schema will be like this:

Products(id,title,text,...)

Properties(id,name)

Product_Properties(ProductId, PropertyId, value)

So you define list of desired properties on property table and assign them to products by inserting tuples in Product_Properties. Consider You have 2 products(A and B) which in your case, have these properties: height, color, price as this:

A: 180, Red, 200$
B: 170, Blue, 270$

Your tables will have these tuples:

Products(id,title,text):

(1,"A","desc1")
(2,"B","desc2")

Properties(id,name) 
(1,"height")
(2,"color")
(3,"price")

Product_Properties(ProductId, PropertyId, value) 
(1,1,"180")
(2,1,"179")
(1,2,"Red")
(2,1,"Blue")
(1,3,"200")
(2,3,"270")

And here is a query which retrieves products whit a 'price' equal to '200': 
SELECT P.title
FROM Product P,Properties Pr,Product_Properties PP
WHERE  P.id=PP.ProductId AND PP.PropertyId=Pr.id AND Pr.name='height' AND PP.value='200'
Saeed
  • 7,262
  • 14
  • 43
  • 63
  • Good, however with this method you would have 1 price for all variations of the product. Many businesses will need to charge a different price for different properties i.e. a size:large item may be more expensive than size:small item. – Ozzy Jul 27 '12 at 15:14
4

you need to have two tables:

CREATE TABLE products( product_id INT, product_name VARCHAR, ... );
CREATE TABLE product_properties( product_id INT, property_name VARCHAR, property_value VARCHAR );

The first table contains one product per row with their unique ID and their common properties

--------------------------------------------------------
|  id  |      name       |   other common properties...
--------------------------------------------------------
|  1   |      apple      |   ...
|  2   |      bentley    |
|  3   |      ...        |
|  ... |      ...        |

The second table relates each product to its non-common properties:

----------------------------------------------------
| id   |    property_name   |    property_value    |
----------------------------------------------------
|  1   |    color           |    red               |
|  1   |    size            |    large             |
|  2   |    wheel count     |    4                 |
|  2   |    top speed       |    200               |
|  2   |    airbags         |    yes               |
|  ... |    ...             |    ...               |

You get a product's information with two queries, first you get the common properties:

SELECT product_name, ... FROM products WHERE product_id = 2

then the non-common properties:

SELECT property_name, property_value FROM product_properties WHERE product_id = 2
nobody
  • 10,599
  • 4
  • 26
  • 43