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:
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 asnull
. 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.)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 fieldsid
(primary key),productType
(to find the right table) andproductTypeId
(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 fromProduct
- Which means that, for each product, you'd have one line in
Product
and one line in the correspondingProductType
table.
- and, speaking as classes/objects, those
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.
精彩评论