Mysql – How to improve the product facet search database model (EAV)

database-designinnodbMySQL

I have created a facet search for a product catalog like following screen image and wonder if I designed it correctly.

I found Joel Brown's post with an ERD that sketches an Entity-Attribute-Value (EAV) model. I tried to build this model in Sequel Pro and run following query:

select     distinct P.* 
from       attribute_product_values APV 
INNER JOIN products P
on         P.ID = APV.product_id
left JOIN  attribute_values AV
on         AV.ID = APV.attribute_value_id
and        AV.value_id = 6
left JOIN  attribute_values AV2
on         AV2.ID = APV.attribute_value_id
and        AV2.value_id = 3
where      AV.ID is not null 
or         AV2.ID is not null

The result of above query was as expected but the query looks very odd to me because of the following reasons:

  1. If I remove the distinct value I get the same product multiple
    times

  2. I have to use a left join instead of an inner join

  3. I have to add a 'where not null' statement for each value I am
    searching for

As of above reasons I feel that I have wrongly designed the relationships between the tables.

I was wondering if the below data model can be designed better in order to make the query simpler and faster ?

Here is the SQL dump of the tables:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


# Dump of table attribute_categories
# ------------------------------------------------------------

CREATE TABLE `attribute_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attribute_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_attribute_categories_cat` (`category_id`),
  KEY `fk_attribute_categories_att` (`attribute_id`),
  CONSTRAINT `fk_attribute_categories_att` FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`),
  CONSTRAINT `fk_attribute_categories_cat` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

LOCK TABLES `attribute_categories` WRITE;
/*!40000 ALTER TABLE `attribute_categories` DISABLE KEYS */;

INSERT INTO `attribute_categories` (`id`, `attribute_id`, `category_id`)
VALUES
    (1,1,1),
    (2,2,1);

/*!40000 ALTER TABLE `attribute_categories` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table attribute_product_values
# ------------------------------------------------------------

CREATE TABLE `attribute_product_values` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `attribute_value_id` int(11) DEFAULT NULL,
  `attribute_category_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_attribute_product_values_att` (`attribute_value_id`),
  KEY `fk_attribute_product_values_cat` (`attribute_category_id`),
  KEY `fk_attribute_product_values_pro` (`product_id`),
  CONSTRAINT `fk_attribute_product_values_att` FOREIGN KEY (`attribute_value_id`) REFERENCES `attribute_values` (`id`),
  CONSTRAINT `fk_attribute_product_values_cat` FOREIGN KEY (`attribute_category_id`) REFERENCES `attribute_categories` (`id`),
  CONSTRAINT `fk_attribute_product_values_pro` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

LOCK TABLES `attribute_product_values` WRITE;
/*!40000 ALTER TABLE `attribute_product_values` DISABLE KEYS */;

INSERT INTO `attribute_product_values` (`id`, `attribute_value_id`, `attribute_category_id`, `product_id`)
VALUES
    (1,2,1,1),
    (2,5,2,1),
    (3,3,1,2),
    (4,6,2,2),
    (6,3,1,3),
    (7,6,2,3);

/*!40000 ALTER TABLE `attribute_product_values` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table attribute_values
# ------------------------------------------------------------

CREATE TABLE `attribute_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attribute_id` int(11) NOT NULL,
  `value_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_attribute` (`attribute_id`),
  KEY `fk_value` (`value_id`),
  CONSTRAINT `fk_attribute` FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`),
  CONSTRAINT `fk_value` FOREIGN KEY (`value_id`) REFERENCES `values` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

LOCK TABLES `attribute_values` WRITE;
/*!40000 ALTER TABLE `attribute_values` DISABLE KEYS */;

INSERT INTO `attribute_values` (`id`, `attribute_id`, `value_id`)
VALUES
    (1,1,1),
    (2,1,2),
    (3,1,3),
    (4,1,4),
    (5,2,5),
    (6,2,6);

/*!40000 ALTER TABLE `attribute_values` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table attributes
# ------------------------------------------------------------

CREATE TABLE `attributes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

LOCK TABLES `attributes` WRITE;
/*!40000 ALTER TABLE `attributes` DISABLE KEYS */;

INSERT INTO `attributes` (`id`, `name`)
VALUES
    (1,'Television Resolution'),
    (2,'Television Feature');

/*!40000 ALTER TABLE `attributes` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table categories
# ------------------------------------------------------------

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

LOCK TABLES `categories` WRITE;
/*!40000 ALTER TABLE `categories` DISABLE KEYS */;

INSERT INTO `categories` (`id`, `name`)
VALUES
    (1,'Television');

/*!40000 ALTER TABLE `categories` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table products
# ------------------------------------------------------------

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  ` name` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  `price` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

LOCK TABLES `products` WRITE;
/*!40000 ALTER TABLE `products` DISABLE KEYS */;

INSERT INTO `products` (`id`, ` name`, `price`)
VALUES
    (1,'Sony ',298),
    (2,'Samsung',252),
    (3,'LG',45);

/*!40000 ALTER TABLE `products` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table values
# ------------------------------------------------------------

CREATE TABLE `values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

LOCK TABLES `values` WRITE;
/*!40000 ALTER TABLE `values` DISABLE KEYS */;

INSERT INTO `values` (`id`, `name`)
VALUES
    (1,'4K Ultra HD'),
    (2,'1080p'),
    (3,'1080i'),
    (4,'760p'),
    (5,'Smart TV'),
    (6,'3D');

/*!40000 ALTER TABLE `values` ENABLE KEYS */;
UNLOCK TABLES;



/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Best Answer

I understand the requirement as:

Return all products having attributes where attribute is of type Television Feature and value is 3D and attribute is of type Television Resolution and value is 1080i

First create this view:

create view v_product_attributes AS
select 
  V.name AS attribute_value,
  A.name AS attribute_name,
  APV.product_id AS product_id
  from
  `values` V -- note: bad practice to use reserved words as table/attribute names
  inner join
  attribute_values AV ON AV.value_id = V.id
  inner join
  attributes A ON A.id = AV.attribute_id
  inner join
  attribute_product_values APV ON APV.attribute_value_id = AV.value_id

This query should produce the desired result:

SELECT
   P.*
FROM
   products P
inner join
   v_product_attributes A1 ON A1.product_id = P.id AND A1.attribute_name = 'Television Resolution' AND A1.attribute_value = '1080i'
inner join
   v_product_attributes A2 ON A2.product_id = P.id AND A2.attribute_name = 'Television Feature' AND A2.attribute_value = '3D'
Related Question