The Correct Way To Store Items With A Filterable Attributes?

I recently had an issue with some SQL logic over here: MySQL multiple WHERE AND/OR condition logic and realised that its most likely the way I structure my data. I'm dribbling at t

Solution 1:

I like using aggregation for this with conditional aggregation. For instance, for your first bullet:

select a.carid
from attributes a
groupby a.carid
havingsum( (attribute, value) in ( ('color', 'red'), ('color', 'blue') ) ) >0andsum( (attribute, value) in ( ('wheels', '2') ) >0;

The > 0 means that the attribute/value combination exists. Use = 0 to specify that it does not work.