Modelling user preference system with categories I am modelling a user preference system with the following requirements 


a user can have attributes (foods they eat)
a user can have dynamic categories of preferences
users which have all their categories with at least one intersect can match


So far I have used Postgres's arrays to build a system to avoid joins. NB. I'm not using proper keys at the moment for simplicity.

CREATE TABLE public.users (
    name text,
    food text[]
);

CREATE TABLE public.preferences (
    name text,
    category text,
    food text[]
);

INSERT INTO public.preferences VALUES ('John', 'Breakfast', '{toast,cereal}');
INSERT INTO public.preferences VALUES ('John', 'Dinner', '{ham}');
INSERT INTO public.preferences VALUES ('Jane', 'Breakfast', '{toast,eggs}');
INSERT INTO public.preferences VALUES ('Jack', 'Breakfast', '{grapefruit}');

INSERT INTO public.users VALUES ('John', '{peas,ham,"ice cream",toast}');
INSERT INTO public.users VALUES ('Jane', '{eggs,ham,"ice cream",cereal,toast}');
INSERT INTO public.users VALUES ('Jack', '{toast,cereal,eggs,peas}');


I have a query that seems to work, however was wondering if anyone had any feedback. It works by asserting that there "are not any categories, which don't match".

select * from users u where name &lt;&gt; 'Jane'
and not exists (select from preferences p where name = 'Jane' and not u.food &amp;&amp; p.food)
and not exists (select from preferences p where u.name = p.name
                and not (select u.food from users u where u.name = 'Jane') &amp;&amp; p.food);

 name |             food             
------+------------------------------
 John | {peas,ham,"ice cream",toast}
 (1 row)
I have used Postgres's arrays to build a system to avoid joins. NB. I'm not using proper keys at the moment for simplicity.


One person's simplicity is another person's denormalized nightmare. Are you avoiding joins because of some already-analysed performance rationale? If so, fine. But if it's based on superstition, this is probably premature optimization.

All of that is to say, the "traditional" normalized relational approach to this would be:


A user table with an integer primary key
A food table with an integer primary key
A meal table with an integer primary key
A preference table with foreign keys to user, food and meal


Don't be afraid of join. Or if you are, consider using a denormalized or "tabular" database like Mongo.