EAV - how to make the structure more versatile and lightweight?

Question

MySQL

Hello! There was a task to implement a mini online store. Feature - product attributes are dynamic, i.e. can be sold as a wing from a Boeing, and slates. And the site must 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, here's 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 displaying the 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). Who solved similar problems - tell me how best to do it? And I don’t want to reinvent the wheel in the age of spaceflight ...

Answer 1

if you use EAV only to contain products, then everything is fine, but if you need to keep attribute groups in the same place, then the table names are a little different.
If the groups are still an attribute in EAV then the names should be the same as the EAV transcript.
element>attr_group atribut>child_attr