Tell me, please, the main ways of storing dynamic parameters for goods in the database. Those. There are several parameters that may or may not be in different products. How to make it take up less space so that you can quickly and simply remove, add and delete them. We should not forget about the values ​​of the parameters themselves. I think that this question is of interest to many, because product groups may be completely different for online stores, respectively. and parameters too. What decision do you think is close to the truth? (EAV, 1 parameter to 1 column, well, etc.).

And also so that they can be sorted and compared

    2 answers 2

    Yes, EAV for a generic online store is the best option. Filters, comparisons, setting different properties for different groups, all this is best implemented and, moreover, without changing the database structure.

    It will be acceptable to work, it is achieved by creating the correct indexes (it will depend on the size of the catalog and the number of properties. In fact, the choice of two indices will be there - composite or one field). Plus, of course, query caching will generally fly.

    Hardcoding properties is possible only if there is a programmer on the salary and will change the structure of the base for any sneeze. Well, either there should be an unobvious procedure for the restructuring of the base, how many offline will be also not clear, maybe too much.

    • did not understand the last paragraph, like EAV and was created so that there were no problems with it - vlukham
    • one
      @majatu is an alternative. There are systems in which the database structure is edited by power-users by changing the metadata, on the basis of which tables are rebuilt, triggers, stored procedures are recreated. In this case, a higher query speed is achieved, but each intervention in the structure leads to a system downtime - with large volumes of the database it can be long-lasting. Well, when a little change and rarely they occur. Participated in the development of such a system, but not for the web. Therefore, I vote for EAV as more simple to implement. - Yura Ivanov

    Usually, to solve such problems, a table with a large number of nullable-fields and an additional table describing the fields that are used are used. But this is not very economical for the database, and if you try to keep the margin as low as possible, there may not be enough fields.

    You can also create a View based on INFORMATION_SCHEMA or other system tables / DBMS views, from which you can learn the structure of the table and dynamically change it. This is the most effective, but also the most difficult way. In addition, it requires that the user, under which the application accesses the site, had the right to change the database structure.

    The easiest way is to create metatables. One table contains a list of parameters, another - the values ​​of these parameters with foreign keys for this list and for an expandable table. It is necessary to work with such a table through dynamic SQL. Since the data is obtained by complicated queries, it will work slowly.