MySQL Design for Handling Products with Custom Fields

I'm building an web application where an product can be of a few different types (furniture types, actually) and depending upon those types, the product has certain fields associated with it.

Quick Background: I'm using MySQL and PHP with CakePHP as my framework. The answer I'm looking for doesn't necessarily need to be "CakePHP-like"; I'd prefer a general MySQL best database design answer first and formost.

So, I can think of two options:

1. Single Table Option – I have one table products with all of the possible fields and just leave the fields that aren't used for that specific product as null. This is I think the simplest design, but would take a bit of shoe horning in the integration (as my model would need to keep track of type and what fields are to be actually updated based on that type.)

2. Multiple Product Table Options – Each product gets its own table with the proper fields for that specific product type. There is a master table (products) that has at least three fields id (primary key), productType (to find the right table) and productTypeId (which joins the product table to the specific product table.

I'm leaning heavi开发者_如何学Goly on #1, but wanted to get some other people's thoughts.

Let me know if you need more details! Thanks!

Update: I would only be expecting a small-ish set of furniture types (<10 to start, max ever would be 20).

I suppose a solution to your problem, at least id you have many different kind of attributes for your products, might be to use Entity-attribute-value model.

Basically, you could have :

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

Of course, you'll also need some kind of "reference system", that defines :

• The list of possible attributes
• For each attribute, the list of possible values
• And for each type of product, the different attributes that can be associated with it.

If you don't want to put in place that kind of system, I would go with something like your second solution :

• I don't like the idea of the first solution -- those NULL fields are not great
• With multi-table options, I would use :
  • One Product table, that contains one line for each product
  • Several ProductTYPE tables
    • and, speaking as classes/objects, those ProductTYPE tables would inherit from Product
    • Which means that, for each product, you'd have one line in Product and one line in the corresponding ProductType table.

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

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

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