Help with my 'products' db structure

Is it appropriate to use entity-attribue-value model?

[coolclu3]

Hi all,

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?

[LifeinAsia]

That's exactly how to do it. There's absolutely nothing wrong with a "thin" table.

[rocknbil]

Agreed - AND - this will allow you to create an unlimited number of product attributes of any kind assigned to that product. Too often I see system that limit the products: size color, trim.

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

[coolclu3]

@lifeinasia Thanks for confirming :)
@rocknbill,

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?

[rocknbil]

Sorry, yes, this is exactly what I am implying - consider the possibility that the "attributes" many require more fields than just name and value, as explained in the paragraphs preceding that. The last line is an "example" of possible fields for your attributes.

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.