There is an architectural solution of the node responsible for the registration of the movements of the mat. values ​​in stock:

The architecture of the node registration of the movement of material values

Since each type of material values ​​has a large number of unique attributes, it was proposed to create a table for each type of product in order not to pile all attributes into one common table.

Tell me, please, why this solution can be bad and good, as well as whether to use it?

UPDATE

Because The solution proposed above is a violation of many important things and an option that prevents many other important things was proposed using the EAV model:

EAV architecture model

  • one
    you need not one table with attributes wide by columns for all products, but to create a table in which attributes and their type will be listed. Then the table of relations of values ​​and attributes. And then write the data to the item-attribute-value table. - teran
  • one
    1) Denormalization; 2) To obtain information about several types, you need either multi-view or dynamic SQL at the same time; 3) It is difficult to build an integrity control system. === Better look towards EAV. - Akina
  • @teran why are you sure your solution is the best? - Pavel Mayorov
  • @PavelMayorov but somewhere it said that it is the best? It will be in normal form, unlike the variant in question. It will allow you to easily expand the list of attributes and products, attach attributes to them. make samples of the same attributes for different types of goods, etc. - teran

2 answers 2

What is bad in your decision:

  • Under each new product that appears, you will have to create a new table.
  • under each table you have to write your own methods of working with it. Those. you will have to process each item with your own unique SQL query

As usual, this problem is solved.

It creates one (1) additional table with fields:

  • Item ID
  • Attribute type
  • Attribute value

And a sample of all attributes of any product turns into one request:

SELECT type, value FROM attributes WHERE id_parent=:id

    what this decision can be bad

    There is an opinion that a properly built database should under no circumstances require the client application to execute a DDL code. At least for security reasons: I can create - it means I can delete it. In your case, the appearance of a new type of product with a new set of attributes will require such an implementation, especially if you have not seen any attribute with such characteristics before.