The best database design for a product with a different price for each attribute

Question

What is the best database design for the following problem:

I have a product with several attributes, such as color, size, material. Depending on these attributes, I want to calculate the total cost of the product. Each attribute may contain a specific price. Thus, the price of a product is the sum of all its attribute costs.

thanks

Answer 1

The first idea would be 3 tables

• Product (name, description et)
• Attribute (color, size, material, etc.)
• Many, many ProductAttribute links (productkey, attributekey, price)

SUM by ProductAttribute for the product will give the price

If you have a fixed and finite set of attributes, then these can be separate columns in the same product table with a calculated column for adding them. However, I feel that you have a variable number of attributes for each product. Thus, the coefficient then goes to a separate table.

Try not to get lost on the EAV territory, of course ... :-)

Edit: after updating the question

I would use Object Role Modeling to capture the model before I think about its implementation.

Fact or size per color changes design significantly

Answer 2

A product table with the columns ID, BASEPRODUCTID, COLORID, SIZEID, MATERIALID, PRICE will seem reasonable. So you can have green product X, but maybe product Y is not available in green. Product Z can cost significantly more for green than product X, so color matching + price probably doesn't make sense - the same goes for other attributes.

BASEPRODUCT will be a table containing information for the product itself, i.e. name, description, etc.

Answer 3

You have a basic product table. In this table you can get the base price of the product.

You have a base table ATTRIBUTES. Just a list of possible attributes that can be applied to your products. This is usually more complicated than a simple list, i.e. Usually it is also tied to the type of product. In other words, not all attributes apply to all products. Therefore, some systems also have a PRODUCTTYPE table and a PRODUCTTYPEATTRIBUTES table. And some attributes are only applicable if other attributes are valid. See the example below. There it can become very difficult.

You have a link table in which PRODUCT is connected to one or more ATTRIBUTES. In this table, the price of adding the attribute is added.

Cost of a product is its base price plus the sum of the prices of its attribute (s). Chair = 100. Leather = 75. Brass tacks = 25 update. Leather chair with brass pads = 200. But you do not need brass tips if you do not have a leather upgrade. So stool + brass is not a real option. Some databases apply such rules in their structure. Others do this in an interface. It can get quite complicated.

But this three-table structure allows you to have multiple attributes for each product. A customer can order a base product or a base product with one or more attributes associated with the product in your PRODUCTATTRIBUTES table.

The unique composite indexes in the PRODUCTATTRIBUTES table (productid, attributeid) will not allow the user to apply the attribute more than once: chair + leather + leather + brass plates will not be possible, and therefore you also prevent the user from updating the skin costs 100 in one row and 125 in another, for the same product.

Answer 4

You can create ProductMetadata to describe product information, such as color, sharpness, etc. PRODUCT is a product that is sold, sold and sold. The relationship between ProductMetadata and Product is, ProductMetadata (one) <---> Product (many)

The product will contain these fields,

• Productid
• ProductMetadataID
• Colorid
• Sizeid
• Materialid
• Price
• Status (sale, sale, sale, etc.)

Then you can calculate what you want in the connection table.

If you add another field in the future, you can add a settings field to store the XML description of the product.