An example of the perils of EAV (entity-attribute-value)

eav.sql

BEGIN;

CREATE TABLE thing (
  id serial primary key,
  name text
);

CREATE TABLE stuff (
    id serial primary key,
    key text,
    value text,
    thing_id int references thing(id)
);

insert into thing (name) values ('iPod');

insert into stuff (key, value, thing_id) VALUES ('price', '$299', currval('thing_id_seq'));
insert into stuff (key, value, thing_id) VALUES ('color', 'black', currval('thing_id_seq'));

insert into thing (name) values ('iPhone');

insert into stuff (key, value, thing_id) VALUES ('price', '$299', currval('thing_id_seq'));
insert into stuff (key, value, thing_id) VALUES ('color', 'black', currval('thing_id_seq'));
insert into stuff (key, value, thing_id) VALUES ('phone', 'yes', currval('thing_id_seq'));

insert into thing (name) values ('iPod Shuffle');
insert into stuff (key, value, thing_id) VALUES ('price', '$99', currval('thing_id_seq'));
insert into stuff (key, value, thing_id) VALUES ('color', 'black', currval('thing_id_seq'));


-- All things with color "black".
SELECT thing.*
  FROM thing
  JOIN stuff ON thing.id = stuff.thing_id
   AND stuff.key = 'color'
   AND stuff.value = 'black';

-- All things with color "black" or price "$299"
SELECT DISTINCT thing.*
  FROM thing
  JOIN stuff ON thing.id = stuff.thing_id
   AND (
           (stuff.key = 'color' AND stuff.value = 'black')
        OR (stuff.key = 'price' AND stuff.value = '$299')
   );

-- All things with color "black" and price "$299".
SELECT DISTINCT thing.*
  FROM thing
  JOIN stuff s1 ON thing.id = s1.thing_id
   AND s1.key = 'color' AND s1.value = 'black'
  JOIN stuff s2 ON thing.id = s2.thing_id
   AND s2.key = 'price' AND s2.value = '$299';

-- All things with color "black" and price "$299" and phone not "yes"
SELECT DISTINCT thing.*
  FROM thing
  JOIN stuff s1 ON thing.id = s1.thing_id
   AND s1.key = 'color' AND s1.value = 'black'
  JOIN stuff s2 ON thing.id = s2.thing_id
   AND s2.key = 'price' AND s2.value = '$299'
  LEFT JOIN stuff s3 ON thing.id = s3.thing_id
   AND s3.key = 'phone' AND s3.value = 'yes'
 WHERE s3.id IS NULL;

ROLLBACK;

\q
BEGIN;

CREATE TABLE product (
  id serial primary key,
  name text
);

CREATE TABLE features (
    id serial primary key,
    name text,
    product_id int references product(id)
);

insert into product (name) values ('iPod');

insert into features (name, product_id) VALUES ('music', currval('product_id_seq'));
insert into features (name, product_id) VALUES ('games', currval('product_id_seq'));

insert into product (name) values ('iPhone');

insert into features (name, product_id) VALUES ('music', currval('product_id_seq'));
insert into features (name, product_id) VALUES ('games', currval('product_id_seq'));
insert into features (name, product_id) VALUES ('phone', currval('product_id_seq'));

insert into product (name) values ('iPod Shuffle');
insert into features (name, product_id) VALUES ('music', currval('product_id_seq'));


-- All products with feature "music".
SELECT product.*
  FROM product
  JOIN features ON product.id = features.product_id
   AND features.name = 'music';

-- All products with features "music" or "games"
SELECT DISTINCT product.*
  FROM product
  JOIN features ON product.id = features.product_id
   AND features.name IN ('music', 'games');

-- All products with features "music" and "games".
SELECT DISTINCT product.*
  FROM product
  JOIN features f1 ON product.id = f1.product_id and f1.name = 'music'
  JOIN features f2 ON product.id = f2.product_id and f2.name = 'games';

-- All products with features "music" and "games" but not "phone"
SELECT DISTINCT product.*
  FROM product
  JOIN features f1 ON product.id = f1.product_id and f1.name = 'music'
  JOIN features f2 ON product.id = f2.product_id and f2.name = 'games'
  LEFT JOIN features f3 ON product.id = f3.product_id and f3.name = 'phone'
 WHERE f3.id IS NULL;

ROLLBACK;

BEGIN;

CREATE TABLE members (
    id  serial primary key,
    nick text not null,
    expires timestamptz not null,
    data text not null
);

INSERT INTO members (nick, expires, data)
VALUES ('theory', now(), '{"wizard_level":3,"groups":["users","admins","wankers"],"active":1}');

INSERT INTO members (nick, expires, data)
VALUES ('xof', now(), '{"wizard_level":10,"groups":["users","admins","wankers","root"],"active":1}');

INSERT INTO members (nick, expires, data)
VALUES ('selena', now(), '{"wizard_level":4,"groups":["users"],"active":0}');


ROLLBACK;