MySQL Design for processing products with custom fields

Question

I am creating a web application where the product can have several different types (types of furniture, actually) and depending on these types, the product has certain fields associated with it.

Fast background . I use MySQL and PHP with CakePHP as my framework. The answer I'm looking for does not have to be "CakePHP-like"; I would prefer that the first MySQL is best suited for database design, and formost.

So, I can present two options:

• Single table variant - I have one products table with all possible fields and just leave fields that are not used for this particular product as null . This, I think, is the simplest design, but integration will require a few shoes for shoes (since my model will need to track the type and which fields should actually be updated based on this type.)

• Table options for multiple products . - Each product receives its own table with the appropriate fields for a particular type of product. There is a master table ( products ) that contains at least three fields id (primary key), productType (for finding the right table) and productTypeId (which connects the product table to a specific product table.

I am very inclined to # 1, but would like to get other people's thoughts.

Let me know if you need more details! Thanks!

Update: I would expect only a small set of types of furniture (<10 to start, the maximum would ever be 20).

Answer 1

I believe that the solution to your problem is, at least you have many different attributes for your products, perhaps you should use the Entity-attribute-value model .

Basically, you could:

• One simple table Product , containing one row per product, and stores the data that each product has (examples: name, price, quantity in stock).
• And another table, which for each product stores the attributes that a product can have - one attribute per row, which means several rows per product.

Of course, you will also need some kind of “reference system” that defines:

• List of possible attributes
• For each attribute, a list of possible values
• And for each type of product there are different attributes that can be associated with it.

If you do not want to implement such a system, I would go with something like your second solution:

• I do not like the idea of ​​the first solution - these NULL fields are not large
• With options with multiple tables, I would use:
  • Product table containing one row for each product
  • Multiple ProductTYPE Tables
    • and, speaking as classes / objects, those ProductTYPE tables inherit from Product
    • This means that for each product you will have one row in the Product and one row in the corresponding ProductTYPE table.

Answer 2

Why not use an object or array or even xml?
1.Use an array or object, and then serialize:

 $details = serialize(array('width'=>'1meter','color'='beatiful')); 

Or use xml and save only the path to the XML file in the database.