There was a task to implement a mini online store. Feature - attributes of goods are dynamic, i.e. can be sold as a wing from Boeing, and shale. And the site should be made and given away, no technical support.

With such conditions, when building a database model, the EAV model was chosen as the most universal. Built a standard model, this is what it looks like:

SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for attributevalue -- ---------------------------- DROP TABLE IF EXISTS `attributevalue`; CREATE TABLE `attributevalue` ( `attribute_id` bigint(20) NOT NULL, `product_id` int(11) unsigned DEFAULT NULL, `attribute_value` text, KEY `product_id` (`product_id`), CONSTRAINT `product_id` FOREIGN KEY (`product_id`) REFERENCES `productentity` (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------- -- Table structure for productattribute -- ---------------------------- DROP TABLE IF EXISTS `productattribute`; CREATE TABLE `productattribute` ( `attribute_id` bigint(20) NOT NULL, `product_id` int(11) unsigned DEFAULT NULL, `attribute_name` varchar(255) DEFAULT NULL, KEY `product_id_attr` (`product_id`), CONSTRAINT `product_id_attr` FOREIGN KEY (`product_id`) REFERENCES `productentity` (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------- -- Table structure for productentity -- ---------------------------- DROP TABLE IF EXISTS `productentity`; CREATE TABLE `productentity` ( `product_id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

But, in addition to the output attributes themselves, you need to implement a search for them.

For example, divide attributes into groups, and assign a filter type ( checkbox , range, drop-down list).

How to do it better? And you don't want to reinvent the wheel in the age of space flight ...

    1 answer 1

    • The list of properties should be the same for the entire directory: attributes .
    • For each attribute, its type (checkboxes, ranges, etc.) is indicated.
    • The directory is divided into product_groups groups, i.e. each item in a separate group.
    • For each group, the list of properties group_attributes (what you have for products in the product attribute must be for groups).
    • You get the description of the properties for the products of the group, display the appropriate filtering widgets.
    • Build a query for the filter, see here , and here .

    The list of properties for groups is optional from the point of view of the filter, i.e. The list of properties can be obtained via select distinct attribute_id... for the goods in the group, however, it may be useful when filling in properties for new products. This list is not mandatory for all products in the group, some properties of individual products may be missing.

    Values ​​for drop-down lists in the filter, min-max values, etc. are obtained by aggregation by attributevalue , however, again this data can be stored in separate tables and or in the cache.

    The presence of groups allows you to make a hierarchical catalog, to make the goods belonging to several groups (an additional link table), to make the whole catalog in one group, everything as your heart desires, and each group will have its own set of properties ...