Improve setup of table structure for comparison website

Question

I have created a database containing specifications of products, product names, shops. The setup of the specification table (product_specs_koppel) is the following:

pid | sid | value
1   | 1   | Y
2   | 1   | N
1   | 2   | 30
2   | 2   | 30

Where pid = product ID, sid = specification ID and value is the value of that specification. So far no problem, I store the names of the specification is a different table which can be linked on sid.

The problem comes with the selection out of the database. As an example, I want to select all pid's, with the following condition:

sid 1 -> Y
sid 2 -> 30

I can create two subqueriesy in the main query, but this would not be a scalable solution:

SELECT 
    p.naam, k.pid, waarde
FROM 
    product_producten p
INNER JOIN product_specs_koppel k ON (k.pid = p.id)
WHERE 
    k.pid IN
        (Select k1.pid from product_specs_koppel k1 where k1.sid = '1' AND
k1.waarde = 'Y') AND
k.pid IN
    (select k2.pid from product_specs_koppel k2 where k2.sid = '2' AND
 k2.waarde = '30')

Any suggestions if there is a better way to setup the database or change/update my query to a more scalable solution?

This question appears to be off-topic because it is better suited for http://codereview.stackexchange.com — jonrsharpe, Jan 05 '14 at 17:12
Reading the links, it's an EAV model. I see a lot of times mentioning that there are downsides on EAV models. Is there a better alternative for an EAV model? — Ralf, Jan 05 '14 at 18:08

score 1 · Answer 1 · edited May 23 '17 at 10:32

1

This model is OK in case your specifications keep changing and growing. You do not really need the inner selects though:

SELECT p.naam, p.id, waarde
FROM product_producten p
INNER JOIN product_specs_koppel k1 
   ON k1.pid = p.id AND k1.sid = 1 and k1.value = 'Y'
INNER JOIN product_specs_koppel k2 
   ON k2.pid = p.id AND k2.sid = 2 and k2.value = '30'

This may be useful as well: Best beginner resources for understanding the EAV database model?

edited May 23 '17 at 10:32

Community

1
1

answered Jan 05 '14 at 17:16

Galz

6,713
4
33
39

What does GROUP BY do? – Strawberry Jan 05 '14 at 17:18
@Strawberry Probably nothing here (assuming one product can't have the same spec twice..) – Galz Jan 05 '14 at 17:21
So for every spec I want to filter on, I create an additional inner join? Doesn't that limit the scalability of the lot? – Ralf Jan 05 '14 at 18:06
@Ralf How may specs do you expect? 10-20 inner joins is not so bad (all joins are indexed). This is the best way I know to query EAV... – Galz Jan 05 '14 at 18:18
@Galz, wasn't planning on more then 20 joins, so I should be safe. Thanks for the help. – Ralf Jan 05 '14 at 19:29

score 1 · Accepted Answer · answered Jan 05 '14 at 18:09

The design you've created is commonly known as "Entity/Attribute/Value" or EAV. Your product is the entity, the attribute is the specification, and the "value" column is indeed the value.

This design has benefits and drawbacks - frequently discussed on Stack Overflow - and you've found one of the big drawbacks: it's really hard to build queries on more than one attribute. Another one in the design you've created is that your "value" column won't necessarily play nicely with numeric comparison - imagine how you would fine value between 25 and 40.

As far as I know, there's now way around this with the standard EAV model.

Most people use EAV because they have heterogeneous, unpredictable data schemas. This kind of data is inherently difficult to accommodate in relational model.

You might want to look at a document-centric solution instead (e.g. XML), or a NoSQL solution.

Improve setup of table structure for comparison website

2 Answers2