Need to design a database for the product catalog. Now there is a problem with the relationship of attributes and products. It is required to invent a table for storing attributes of goods. Attributes can have different types, such as String, Number and List. That is, an attribute with the String type for each product stores any text, an attribute of the Number type stores a number for each product, and an attribute of the List type stores one or several values ​​for each product (note the Purpose attribute is for a bath, for a shower stall, and t .P.)

Here are the tables that I could come up with:

Product Product table

Attribute Attribute table

Product Attributes Attribute Relationship Table

Since the Product-Attribute pair can have several values, I did not do the primary key, but simply marked the composite index product_id and attribute_id.

Hope for tips to improve the solution.

    1 answer 1

    So, the decision was made in the Attributes of goods table to add a search_hash column that contains a crc32 hash of the Value field and make the primary key in three columns (product_id, attribute_id, search_hash).