How can I improve my product facet search database model (EAV)

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:

• If I remove the distinct value I get the same product multiple

times

-
I have to use a left join instead of an inner join

-
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

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'

StackExchange Database Administrators Q#169276, answer score: 3