Best database design for product with different price for each attributes

What's the best database design for the following problem:

I have a product with several attributes like color, size, material. Depending on these attributes I want to calculate the total price of a product. Every attribute can hold a specific price. So the price of the product is the sum of all its attribute's cos开发者_StackOverflow社区ts.

First idea would be 3 tables

• Product (name, description et)
• Attribute (colour, size, material etc)
• Many-many link ProductAttribute (productkey, attributekey, price)

A SUM on ProductAttribute per Product would give the price

If you have a fixed and finite set of attributes, then these could be separate columns in a single product table with a computed column to add them up. However, my feeling is that you have a variable number of attributes for each product. So factor then out into a separate table.

Try not to stray into EAV territory of course... :-)

Edit: after question update

I'd use Object Role Modelling to capture the model before I think about implementing.

The fact or size per colour changes the design considerably

A PRODUCT table with columns of ID,BASEPRODUCTID,COLORID,SIZEID,MATERIALID,PRICE would seem sensible. This way you can have green product X's but perhaps product Y isn't available in green. Product Z might cost considerably more to green-ize than product X so pairing colour + price most likely doesn't make sense - same for other attributes.

BASEPRODUCT would be a table holding the information for the product proper - i.e. the name, description etc.

You have a base PRODUCTS table. In that table you can have the product's base price.

You have a base ATTRIBUTES table. Just a list of possible attributes that can be applied to your products. Usually this is more complex than than a simple list, i.e. it is is usually keyed to product-type as well. In other words, not all attributes are applicable to all products. So some systems would also have a PRODUCTTYPE table and a PRODUCTTYPEATTRIBUTES table. And some attributes are applicable only if other attributes are in effect. See chair example below. There it can get very complex.

You have a linking table where PRODUCT is connected to one or more ATTRIBUTES. In that table the attribute add-on price is kept.

Cost of a product is its base price plus the sum of the prices of its attribute(s). Chair = 100. Leather = 75 upgrade. Brass tacks = 25 upgrade. Leather chair with brass tacks = 200. But you wouldn't need brass tacks if you don't have the leather upgrade. So chair + brass tacks is not a real-world option. Some databases enforce such rules in their structure. Others do it in the front-end. It can become quite complicated.

But this three-table structure allows you to have multiple attributes per product. The customer can order the base product, or the base product with one or more of the attributes linked to the product in your PRODUCTATTRIBUTES table.

Unique composite indexes in the PRODUCTATTRIBUTES table on (productid, attributeid) would prevent the user from applying an attribute more than once: Chair + leather + leather + brass tacks would not be possible, and so you're also preventing the user from saying that leather upgrade costs 100 in one row and 125 in another, for the same product.

You can design ProductMetadata to description product information, such as color, sharp, etc. PRODUCT is the product to be sale, saling, and sold out. The releationship between ProductMetadata and Product is, ProductMetadata(one) <---> Product (many)

Product will contain those fields,

• ProductID
• ProductMetadataID
• ColorID
• SizeID
• MaterialID
• Price
• Status(Saling, To be Sale, sold out, etc)

Then you can calculate what you want base on join table.

If you will add some more field in the future, you can add a Setting field to store XML description of the Product.