At the moment there is such a database structure: enter image description here

The main entity is the Product table. There is a ProductDetail table associated with it, in which more detailed information on products should be stored. The trouble is that these products are quite different among themselves and that is why a bunch of reference tables associated with it appear in the ProductDetail table.
What is the best way to reorganize this structure? For the fact that now in the picture is only a small part of the directories that we are going to add. In the future they will be even more ...
I understand that there should be some standard solution for my case, but I can not find it.

  • one
    In general, in my opinion, the directory structure is good: if you add / remove a field, you do not have to re-allocate the main table. And in general, clearer - Matty
  • @Matty here it seems that the table of product properties is supposed to be expanded, and each new property will pull a new directory. And the problem is likely to be that not all properties are inherent in every product. Some will have some properties, and some others. In this case, such an approach is really not the best option, and from the point of view of describing the domain, the very "property" of a product should be a separate entity, and not a "field" of a product. - teran
  • @teran, I wish I didn’t describe the whole problem, but could you explain what every feature of a product should be, not a field, but a separate entity? - MiXaiL
  • @ MikhailKabakov think about the fact that, on the one hand, there should be a list of products with a minimum set of properties, such as name, price, description, category. etc. On the other hand, there should be a list of product characteristics, such as color, length, material, that is, in principle, a separate table of characteristics. + add the ability to associate a product with characteristics in the sense of "a product has a characteristic", where the property of association is value. Here you can add some sets of characteristics for groups of goods. That is, if a product is categorized, then it inherits its characteristics. - teran

2 answers 2

In Product, directly enter the id of the directory associated with this product.

I see no point in the ProductDetail table yet.

    The structure is clearly denormalized.

    From the diagram it is clear that Product stores information and the very essence of the Product (code, name), and on individual instances of this entity (price). The same attribute (name) is stored both in the Product (ProductName) table and in the ProductDetail (ShortName) table. This is the result of a wrong analysis of the subject area or lack thereof. I recommend doing it (again).

    As for dictionary references, a large number of them should not confuse you. Even if everything is dumped into one table, it will not be better - what difference does it make to attach ten different directories or ten copies of one reference book to the main table in the query?