MySQL Design for Handling Products with Custom Fields

Answer 1

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.

Answer 2

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.