Given: attributes of goods (color, height, manufacturer), which are selected from the proposed choice, not free editing. How in this case is it better to build a database? I think to bring each attribute into a separate entity, and then with the table M-M to link a specific product with attributes, i.e. table colors, products, product_has_colors . How correct is this and what are the alternatives?

In the future, you will need to make a filter for products by attributes, so the option to add attributes to json for each product, I think, is not the best idea.

  • I think to put each attribute into a separate entity. And when will a new attribute appear - change the structure of the database ??? Look, for example, in the direction of EAV. Yes, and JSON is not to say that it is not applicable, after all, it is indirectly indexed ( dev.mysql.com/doc/refman/5.7/en/… ). - Akina
  • @Akina why? With each attribute, only 2 tables are added - the essence itself + communication with the product. I must immediately say that IM is done only under flowers, so there can be no unlimited number of attributes. Regarding EAV read, thanks!) - Alexxosipov
  • With each attribute, only 2 tables are added - the essence itself + communication with the product. Harden Aki "Our Father" - the client MUST NOT perform (and even have the right to perform) DDL, his competence is solely data. Exceptions are rare, and this is clearly not your case. - Akina

3 answers 3

Here I found on the Internet, it seems that it suits your task:

https://dba.stackexchange.com/questions/123467/schema-design-for-products-with-multiple-variants-attributes

enter image description here

    Usually, DIC_PRODUCER , DIC_PRODUCER , etc. dictionaries are created in the database. In the main table (journal), properties are linked by dictionary keys: JOR_TOVAR(NAME,ID_COLOR,ID_PRODUCER,...) For internal control, you can specify the relationship of fields by foreign key, but usually it is just a fuse, since the interface will still limit the input to the samples from the dictionary tables. The number and content of dictionaries is dictated by the task itself. But ideally, if in magazines almost everything will be ID_ - with links (except for dates and count). That, if anything, will give you a hint on the division of dictionaries.

      1) If all your products are of the same type and have a finite number of attributes, then the best way out would be to simply make a wide table including the fields of these attributes.

      2) If an unlimited growth in the diversity of attributes is assumed, then a separate directory of attributes should be made and a table of product bindings with attributes be created

       CREATE TABLE products{ id INT ... } CREATE TABLE attributes{ id INT, name VARCHAR, .... } CREATE TABLE productAttributes{ productId INT, <- внешний ключ для products.id attrId INT, <- внешний ключ для attributes.id value VARCHAR }

      This method is extremely versatile, but more difficult in terms of working with data.