MySQL table relationship and how to query a Key/value table

Question

I have the following table structure:

Product (id, name, ...)

+-----+------------+
| id  | name       |
+-----+------------+
|   1 | Product #1 |
|   2 | Product #2 |
|   3 | Product #3 |
|   4 | Product #4 |
+-----+------------+

Attribute (id, title, ...)

+-----+------------+
| id  | title      |
+-----+------------+
|   1 | shape      |
|   2 | colour     |
|   3 | height     |
|   4 | weight     |
+-----+------------+

Option (id, title ... )

+-----+------------+
| id  | title      |
+-----+------------+
|   1 | round      |
|   2 | square     |
|   3 | oval       |
|   4 | red        |
|   5 | blue       |
|   6 | green      |
|   7 | tall       |
|   8 | short      |
|   9 | heavy      |
|  10 | light      |
+-----+------------+

and a fourth one (ProductAttribute - id, product_id, attribute_id, option_id), hoping to get "all the red round products which are also tall and heavy":

+-----+------------+--------------------+
| id  | product    | attribute | option |
+-----+------------+--------------------+
|   1 | Product #1 | shape     | round  |
|   2 | Product #2 | shape     | oval   |
|   3 | Product #3 | shape     | round  |
|   4 | Product #4 | shape     | square |
|   5 | Product #1 | color     | green  |
|   6 | Product #2 | color     | red    |
|   7 | Product #3 | height    | tall   |
|   8 | Product #4 | height    | short  |
|   9 | Product #2 | weight    | heavy  |
|  10 | Product #1 | weight    | light  |
+-----+------------+--------------------+

I'm by far not an sql master and maybe my idea can't work.

Edit:

Q1. The question is how do I achieve that? Getting all the red, tall, heavy products for instance.

The following queries don't achieve my purpose:

1:

SELECT ProductAttributes.product_id, ProductAttributes.id FROM ProductAttributes 
WHERE (ProductAttributes.attribute_id = 1 AND ProductAttributes.option_id = 1) 
AND (ProductAttributes.attribute_id = 3 AND ProductAttributes.option_id = 4);

2:

SELECT DISTINCT ProductAttributes.product_id, ProductAttributes.id FROM ProductAttributes 
WHERE (ProductAttributes.attribute_id = 1 AND ProductAttributes.option_id = 1) 
OR (ProductAttributes.attribute_id = 3 AND ProductAttributes.option_id = 4);

Note: I'm purposely putting 2 variable in my query, as the real one has many more.

Answer 1

Fot the key/value approach I'd use composite keys to improve consistency:

attribute (attribute_no, title), PK = attribute_no

+--------------+------------+
| attribute_no | title      |
+--------------+------------+
| 1            | shape      |
| 2            | colour     |
| ...          | ...        |
+--------------+------------+

attribute_option (attribute_no, option_no, value), PK = attribute_no, option_no

+--------------+-----------+------------+
| attribute_no | option_no | value      |
+--------------+-----------+------------+
| 1            | 1         | round      |
| 1            | 2         | square     |
| 2            | 1         | green      |
| 2            | 2         | red        |
| ...          | ...       | ...        |
+--------------+-----------+------------+

product (product_no, product_name, ...), PK = product_no

+------------+--------------+
| product_no | product_name |
+------------+--------------+
| 7352871    | Product #1   |
| 8956443    | Product #2   |
| ...        | ...          |
+------------+--------------+

product_attributes (product_no, attribute_no, option_no), PK = product_no, attribute_no

+------------+--------------+-----------+
| product_no | attribute_no | option_no |
+------------+--------------+-----------+
| 7352871    | 1            | 1         |
| 7352871    | 2            | 1         |
| 8956443    | 1            | 2         |
| 8956443    | 2            | 1         |
+------------+--------------+-----------+

(And you'd want an index on attribute_no + option_no for this table.)

The product_attributes primary key guarantees that each product only gets one value per attribute. Well, this is good for height, weight, etc. If you want to have multiple colors etc. for a product however, you need such product_attributes table including the option_no in the primary key. You may end up with separate tables for unique attributes and multiple attributes. Maybe later you even want to introduce product groups with optional and obligatory attributes (a freezer has an energy class, a t-shirt doesn't). So this whole concept may grow, but above tables should give you an idea how to best approach this.

A query for all the red round products which are also tall and heavy:

select * 
from product
where product_no in
(
  select product_no
  from product_attributes
  where (attribute_no, option_no) = 
  (
    select ao.attribute_no, ao.option_no 
    from attribute_option ao
    join attribute a on a.attribute_no = ao.attribute_no
    where a.title = 'colour'
    and ao.value = 'red'
  )
)
and product_no in
(
  select product_no
  from product_attributes
  where (attribute_no, option_no) = 
  (
    select ao.attribute_no, ao.option_no 
    from attribute_option ao
    join attribute a on a.attribute_no = ao.attribute_no
    where a.title = 'shape'
    and ao.value = 'round'
  )
)
and product_no in (...)
and product_no in (...);

Or shorter with aggregation:

select * 
from product
where product_no in
(
  select pa.product_no
  from product_attributes pa
  join attribute a on a.attribute_no = pa.attribute_no
  join attribute_option ao on a.attribute_no = pa.attribute_no
                           and a.option_no = pa.option_no
  group by pa.product_no
  having sum(a.title = 'colour' and ao.value = 'red') > 0
     and sum(a.title = 'shape' and ao.value = 'round') > 0
     and sum(a.title = 'height' and ao.value = 'tall') > 0
     and sum(a.title = 'weight' and ao.value = 'heavy') > 0
)

Answer 2

After searching the web for "mysql key value table" (thank you @Thorsten Kettner for the keywords, as I lack the terminology), I've end up with something like:

SELECT Product.id FROM Product
INNER JOIN ProductAttributes PA_1 ON
    Product.id = PA_1.product_id
INNER JOIN ProductAttributes PA_2 ON
    Product.id = PA_2.product_id
WHERE
    (PA_1.attribute_id = 1 and PA_1.option_id = 1)
AND
    (PA_2.attribute_id = 3 and PA_2.option_id = 4);

Basically whenever a new attribute is used in the query, a different INNER JOIN condition is needed.

Which in terms of "performance" a rather noticeable hit will happen.

According to this and this a key/value table should not be used for filtering, but at this point I have no choice, so it will be up to the caching server to save the day.

I've based my answer of this (no need for GROUP BY in my case as I don't use aggregate functions) Filtering and Grouping data from table with key/value pairs

Answer 3

So you want make select based on option from ProductAttribute table

Better way to store data in table is to use unique/id/primary key value for fourth columns and then you can so that

SELECT * FROM ProductAttribute as attr
INNER JOIN Product as product ON product.id=attr.product_id 
INNER JOIN Attribute as attr2 ON attr2.id=attr.attribute_id
WHERE attr.option=“round” OR attr.option=“red”

I hope this help you!

Answer 4

Key/value tables are a nuisance. So avoid them, if you can. You'd have these tables then:

table shapes

+--------+
| shape  |
+--------+
| round  |
| oval   |
| round  |
| square |
+--------+

table colors

+--------+
| color  |
+--------+
| green  |
| red    |
+--------+

table heights

+--------+
| height |
+--------+
| tall   |
| short  |
+--------+

table weights

+--------+
| weight |
+--------+
| heavy  |
| light  |
+--------+

table products

+-------------+--------------+--------+--------+--------+--------+
| product_no  | product name | shape  | color  | height | weight |
+-------------+--------------+--------+--------+--------+--------+
|   14214     | Product #1   | round  | red    | tall   | heavy  |
|   22312     | Product #2   | oval   |        | short  | heavy  |
|   35757     | Product #3   | square | green  | tall   | heavy  |
|   42468     | Product #4   |        | red    | short  | light  |
+-------------+--------------+--------+--------+--------+--------+

The query

select * 
from products
where shape = 'round' 
  and color = 'red' 
  and height = 'tall' 
  and weight = 'heavy';

You can do the same with IDs by the way. So all lookup tables would get an ID (round = 1, oval = 2, ... green = 1, red = 2, ...) and the product table would no longer contain the words, but the IDs. The query would then be:

select * 
from products
where shape_id = (select id from shapes where shape = 'round')
  and color_id = (select id from colors where color = 'red')
  and height_id = (select id from heights where height = 'tall')
  and weight_id = (select id from weights where weight = 'heavy';