Hello.

There is a product catalog containing product groups, product_group:

id name 1 Автомобили 2 Мотоциклы

Properties have been created for each group, for example, for Cars, product_properties:

 id name parent gid 1 Брэнд 0 1 2 BMW 1 1 3 Audi 1 1 4 Модель 0 1 5 X5 4 1 6 X5 4 1 7 A1 4 1 8 А2 4 1 9 Мотор 0 1 10 xDrive35i 9 1 11 xDrive50i 9 1 12 1.2 TFSI 9 1 13 1.4 TFSI 9 1

Those. properties are related to each other:

 БМВ / X6 / xDrive35i AUDI / A1 / 1.2 FTSI

And it can not be, for example,

 БМВ / А1 / xDrive35i AUDI / A1 / xDrive35i

How to properly associate them with commodity items, taking into account that when opening a category of goods, we should see their attributes in the following form: How best to associate them with commodity items given that we will have to make the following conclusion of the Cars group:

 BMW Х5 Х6 Audi A1 A2

    2 answers 2

    It was better to make a tree with the is_category flag in the first table (Cars [1] - BMW [1] - X5 [0]), in the second property of the form [element_id, name, value]

    • I did as you wrote, in my case a very simple and convenient solution, thank you very much for your help. - Sharpness

    In your example, you confused the attribute name and attribute value, so put them in 1 column. Attributes must be moved to a separate table (brand, model, motor, etc.). and on topic:

     /* группы продуктов*/ table product_groups: group_id, group_name, ... /* свойства продукта (а еще лучше названия хранить отдельно т.к. бывают разные одинаковые названия)*/ table product_properties: product_id, product_name, ... /* связки свойств по группам и продуктам. где все поля часть первичного ключа */ table product_properties_def: product_group_id, product_id, related_product_id бинд конкретных брендов: insert into product_properties_def values (1, 1, 2); -- привязка БМВ к брендам insert into product_properties_def values (1, 1, 2); -- привязка Ауди к брендам бинд конкретных моделей автомобилей: insert into product_properties_def values (1, 4, 5); insert into product_properties_def values (1, 4, 6); привязка моторов: insert into product_properties_def values (1, 9, 10); insert into product_properties_def values (1, 9, 11);

    If you are planning a tree-based product walk:
    1) first select the brand (top of the tree)
    2) select the model (the model is tied to the brand)
    3) other attributes are selected (tied to a specific model)

    then at every step you get a simple query like:

     select * from product_properties where product_id in (select product_id from products where name = ? /* brand name */)

    If you need to create complex filters for data, then you need another product_properties_def_flat table product_properties_def_flat similar to product_properties_def table. the difference is that the latter will store the expanded data:

     связывание атрибутов без прямой зависимости (): insert into product_properties_def_flat values (1, 1, 5); insert into product_properties_def_flat values (1, 1, 6); insert into product_properties_def_flat values (1, 1, 10); insert into product_properties_def_flat values (1, 1, 11);

    the product_properties_def_flat table must be updated with the triggers hung on product_properties_def and contain all valid attribute combinations, its size will be much larger and it will contain all the data from product_properties_def .

    • Intensive decision, I'll keep it in mind, thank you :) - Sharp - witted