3
\$\begingroup\$

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 <> 'Jane'
and not exists (select from preferences p where name = 'Jane' and not u.food && 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') && p.food);

 name |             food             
------+------------------------------
 John | {peas,ham,"ice cream",toast}
 (1 row)
\$\endgroup\$
6
  • \$\begingroup\$ What's the difference between "a food that a user eats" and "a food that a user prefers to eat"? \$\endgroup\$ Commented Apr 11, 2020 at 19:43
  • \$\begingroup\$ Also, can you describe what you want that query to actually do? \$\endgroup\$ Commented Apr 11, 2020 at 19:45
  • \$\begingroup\$ @Reinderien return the users where they share at least one food in each category (meal). The "prefers to eat" is in a particular category. \$\endgroup\$ Commented Apr 13, 2020 at 13:58
  • \$\begingroup\$ I still do not understand the difference between your users.food and preferences.food. Does users.food just track a food to which a user is not allergic (an "acceptable" food)? Or are you trying to capture the difference between "foods a user prefers without association to a category" and "foods a user prefers within a category"? \$\endgroup\$ Commented Apr 13, 2020 at 17:17
  • \$\begingroup\$ Put another way: is there ever a case where a users.food will not have a corresponding entry in preferences.food, or vice versa? \$\endgroup\$ Commented Apr 13, 2020 at 17:19

1 Answer 1

2
\$\begingroup\$

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.

\$\endgroup\$
1
  • 1
    \$\begingroup\$ I reworked the code into a relational style, and used left joins coupled with group by count distinct on the meal table to ensure that there was one match in at least every meal. It does use a subselect, to ensure that both user's preferences are matched. \$\endgroup\$ Commented Apr 16, 2020 at 8:17

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.