SQL Filter by attributes (Design)

Question

I have a database of products which I'd like to filter by arbitrary categories. Let's say for the sake of an example that I run a garage. I have a section of products which are cars.

Each car should have a collection of attributes, all cars have the same number and type of attributes; for instance colour:red, doors:2, make:ford ; with those same attributes set to various values on all the cars.

Gut feeling tells me that it would be best to add "colour", "doors" and "make" columns to the product table.

HOWEVER: Not ALL the products in the table are cars. Perhaps I would like to list tyres on the page of cars. Obviously, "colour" and "doors" won't apply to tires. Even so, if a user selects colour=red as a filter, I would still like the tires to be shown as they lack the colour attribute.

Mulling it over (and I'm really not a database guy so I apologise if this approach is horrible) I considered having a single "attributes" column which I could fill with an arbitrary number of arbitrarily named attributes, then use SQLs string functions to do the filtering. I guess you could even use a bit field here if you planned carefully. This seems hackish to me though, I'd be interested to know how some of the larger sites such as Amazon do this.

What are the issues with these approaches, can anyone recommend any alternatives or shed any light on the subject for me?

Thanks in advance

Answer 1

Anyone reading this in the future, I managed to get the results I wanted thanks to luksch taking the time to help me out!!! Thanks!!!

Using this layout:

CREATE TABLE product (id int, name varchar(200));
INSERT INTO product (id, name) VALUES (100, "Red Porsche");
INSERT INTO product (id, name) VALUES (101, "Red Ferrari V8");
INSERT INTO product (id, name) VALUES (102, "Red Ferrari V12");
INSERT INTO product (id, name) VALUES (103, "Blue Porsche");
INSERT INTO product (id, name) VALUES (104, "Blue Ferrari V8");
INSERT INTO product (id, name) VALUES (105, "Blue Ferrari V12");
INSERT INTO product (id, name) VALUES (106, "Snow Tires");
INSERT INTO product (id, name) VALUES (107, "Fluffy Dice");


CREATE TABLE attr (id int, product_id int, a_name varchar(200), a_value varchar(200));

INSERT INTO attr (id, product_id, a_name, a_value) VALUES (1,  100, "colour", "red");
INSERT INTO attr (id, product_id, a_name, a_value) VALUES (1,  101, "colour", "red");
INSERT INTO attr (id, product_id, a_name, a_value) VALUES (1,  101, "cylinders", "8");
INSERT INTO attr (id, product_id, a_name, a_value) VALUES (1,  102, "colour", "red");
INSERT INTO attr (id, product_id, a_name, a_value) VALUES (1,  102, "cylinders", "12");

INSERT INTO attr (id, product_id, a_name, a_value) VALUES (1,  103, "colour", "blue");
INSERT INTO attr (id, product_id, a_name, a_value) VALUES (1,  104, "colour", "blue");
INSERT INTO attr (id, product_id, a_name, a_value) VALUES (1,  104, "cylinders", "8");
INSERT INTO attr (id, product_id, a_name, a_value) VALUES (1,  105, "colour", "blue");
INSERT INTO attr (id, product_id, a_name, a_value) VALUES (1,  105, "cylinders", "12");

I achieved the result I wanted; which was two things:

Firstly I wanted to be able to select products by attribute, say by colour and cylinders, but also show any products which have neither the colour nor cylinders attribute, which I achieved with this query:

SELECT DISTINCT product.id, name, a_value
FROM product 
LEFT JOIN attr 
ON product_id=product.id 
WHERE
(
    (a_name="colour" AND a_value="blue")
    OR
    (a_name IS NULL)
)
AND product.id IN
(
  SELECT product.id
  FROM product 
  LEFT JOIN attr 
  ON product_id=product.id 
  WHERE
  (a_name="cylinders" AND a_value="12")
  OR
  (a_name IS NULL)
)

This lists all the blue cars with 12 cylinders, and also lists the tires and fluffy dice since they have neither a colour or cylinder count. This can easily be adapted to filter on one attribute, or you can add more AND / IN clauses to add more filters

And I also wanted to be able to list all relevant attributes (I use WHERE 1 in this example, but in practise this would be WHERE idfolders=? to list all attribute relevant to a specific folder)

SELECT DISTINCT a_value, a_name 
FROM product 
INNER JOIN attr
ON product_id=product.id
WHERE 1

Answer 2

You should read about database normalization. It is generally not a good idea to use concatenated strings as values in a single column. I made a very small sqlfiddle for you to start playing around. This does not really solve all your problems, but it may lead you in the right direction.

Schema:

CREATE TABLE product (id int, name varchar(200), info varchar(200));
INSERT INTO product (id, name, info) VALUES (100, "Porsche", "cool");
...
INSERT INTO product (id, name, info) VALUES (103, "Tires", "you need them!");


CREATE TABLE attr (id int, product_id int, a_name varchar(200), a_value varchar(200));
INSERT INTO attr (id, product_id, a_name, a_value) VALUES (1,  100, "color", "black");
INSERT INTO attr (id, product_id, a_name, a_value) VALUES (2,  100, "doors", "2");
...

A Query:

SELECT * FROM product INNER JOIN attr ON attr.product_id=product.id 
WHERE attr.a_name="doors" AND attr.a_value = "2"