Forum Moderators: open
I'm really having a headache, some webmaster here please help me
I want to have a db describing all the PRODUCTS possible, categorized.
Now, each product category will have its own set of attributes, therefore not all the attributes should be table rows . I read about entity-attribute-value model , and if i understood it right, the `products` table will have all the basic info about the product, and the category-specific product attributes will be stored in a sparse table
TABLE `product_attribute_value`
(product_id, attribute_name, attribute_value);
This way, the category-specific attributes are search-able
The problem i'm thinking is the table `product_attribute_value` can be potentially very thin, but with lots of rows.
My question: Is there a better way to design a db describing data like this?
Additionally, consider if there are more attributes than name and value. For example, if the attribute is SIZE, maybe the price will be different for the three sizes? This being the case, your programming would want to get the price from the attributes table, not the "base price" of the product.
Or if this attribute is required or not? Let's say you have a widget, and the attribute is widget cover. So it's an "add-on" and adds to the price of the product if selected. So this item would have a value of 0 for "not required."
You may also want weight, dimensions for shipping calculations. Small, medium, and large are not only going to cost differently, they are going to weigh and measure differently.
The programming is not that difficult; if the attributes have a price value > 0 and the item is required, use these prices (and other values) and output a select list; otherwise use the product base price. If the item is not required, selection of this item adds to the base price.
record_id¦product_id¦attribute_name¦attribute_value¦price¦weight¦width¦length¦height¦required
Yep, there will be another table to attach which attribute to which product category
TABLE `product_category_attributes`
(category_id, attribute_id, required)
However, table `product_attribute_value` will still have only 3 columns, and no more
The last line you wrote kinda confused me. It seems you might put more fields into `product_attribute_value` table?
You can join that "extra data" on other tables, or add more fields to the attributes table. It's up to you to decide what's more efficient; more tables to join=more complex select statements.
