[DB] What is the best way to store product attributes if their quantity is known and will not change?

Question

Database design

Good afternoon.

Need advice from people who understand database design. The initial conditions are as follows:
1) there is one type of product (and there will be no others)
2) the product has a fixed and known number of properties (maximum 2 different colors, shape, material)

In the future, according to these properties, it is necessary to search for products.

The most obvious solution is to add as many fields to the product table as we have properties:

PRODUCT
- id
- name
-color_one
-color_two
- style
- fabric

But then the question arises - how to deal with the presentation of properties? Making VARCHAR fields and allowing the user to enter values ​​on their own is not very correct - there will be typos, and duplicate values ​​will inevitably appear. In addition, the number of possible values ​​for each of the properties is not so large (i.e. it is easier to pre-compile a list of possible values ​​for each property and then just select a value from the list).

Creating separate tables for each property (COLOR, FABRIC, STYLE) also seems redundant. So for now, I came up with this solution:

PROPERTY
- id
- property_name
- property_value

And the fields in the PRODUCT table simply contain links to records from the PROPERTY table. Is this a normal solution? Perhaps there are ways to do it better?

PS: yes, I understand that, in fact, a MANY-TO-MANY relationship is obtained between PRODUCT and PROPERTY and you can generally throw out the fields representing properties from the PRODUCT table, and implement the relationship through the PRODUCT_PROPERTY associative table, but such a solution already seems too cumbersome for this tasks.

Answer 1

I would make style_id int instead of style varchar in the main table and keep the styles(int, varchar/unique) table. This way there will be no duplicates, and the main table will only hold field id's.
When a new style appears, it is added to the styles table.

Answer 2

Well, the classics of the genre Entity-attribute-value model

Answer 3

Look towards document-oriented DBMS like MongoDB .

Answer 4

I would do it in the standard way:
Product
- id
- name
- color_one_id
- color_two_id
- style_id
- fabric_id
And the corresponding tables: COLOR, STYLE, FABRIC. If you don’t have a super-visited project, then you don’t need to invent anything else.
Optimization 1. If, for example, the style is clearly defined, then it can be stored as ENUM.
Optimization 2. If additional requests bother you, then store lists of colors, styles and materials in the form of server configs (in the form of simple arrays).
For the client side, use <select></select>.

Answer 5

My God. Looking at the advice, I was horrified. You will intimidate the author of the question now. To answer this most banal question, it is enough for him to read not just a book, but some article on database design, this is the basis of the basics.

Answer 6

Judging by the question:
1. You obviously do not have thousands of items of goods there.
2. you are not a programmer
Proceeding from this - do it as it is easier, clearer and faster to do it for you. “Correctness” in this case is absolutely useless.

Answer 7

SQL ENUM && HTML SELECT?

Answer 8

Wanted to change the list — wrote ALTER TABLE. And to store everything in ENUM - the simplicity is obvious, and the styles are the same, although they are not known in advance, but they do not change every 3 minutes. And you don't have to write any JOINs. JOIN'y are actual not when in directories <100 records, and when it is really the DIRECTORY. Otherwise, ENUM and ALTER TABLE are quite a silver bullet.