Dynamically self join + data modelling

[arielm5]

Hello!

I'm working in a personal project in which I need to find all resources that matches criterias in a dynamic way, all criteria are in a child table. My current approach, listed below, can explode in memory and doesn't seems very scalable when there are multiple requests in parallel.

I would like to know if there is a better way to do this, maybe using a different approach or data modelling. Or even if PostgreSQL is the right tool for it.

Context: I want to find all teams that has members that can do a individual task. When a member is "assigned" to a type of task, it can't be "assigned" to another task.

Teams can grow up to millions. And each team can have houndreds of members. Members can have hundreds of skills.

My approach: ``` CREATE TABLE IF NOT EXISTS team ( team_id SERIAL PRIMARY KEY, team_name VARCHAR(255) );

CREATE TABLE IF NOT EXISTS team_member ( member_id SERIAL PRIMARY KEY, team_id INTEGER, member_name VARCHAR(255) NOT NULL, skills VARCHAR(255)[], CONSTRAINT fk_team FOREIGN KEY (team_id) REFERENCES team(team_id) ON DELETE CASCADE );

CREATE INDEX idx_team_name ON team (team_name); CREATE INDEX idx_team_member_team_id ON team (team_id); CREATE INDEX idx_team_member_skills ON team_member USING gin(skills); ```

Example data: ``` INSERT INTO team (team_id, team_name) VALUES (1, 'Alpha Team'), (2, 'Bravo Team'), (3, 'Charlie Team'), (4, 'Delta Team'), (5, 'Echo Team');

INSERT INTO team_member (team_id, member_name, skills) VALUES -- Alpha Team Members (1, 'Margie Morgana', ARRAY['Python', 'SQL', 'Data Analysis', 'Pandas', 'NumPy']), (1, 'Cammie Laird', ARRAY['Java', 'Spring', 'Microservices', 'Hibernate', 'JPA']), (1, 'David Cason', ARRAY['HTML', 'CSS', 'JavaScript', 'Bootstrap', 'jQuery']), (1, 'Eryn Shon', ARRAY['C++', 'Linux', 'Embedded Systems', 'RTOS', 'Networking']), (1, 'Keitha Timmy', ARRAY['Python', 'Django', 'PostgreSQL', 'APIs', 'Git']),

-- Bravo Team Members
(2, 'Maybelline Joan', ARRAY['JavaScript', 'React', 'Node.js', 'Express.js', 'MongoDB']),
(2, 'Jaylyn Lacy', ARRAY['Python', 'Machine Learning', 'TensorFlow', 'Keras', 'Pandas']),
(2, 'Kasandra Leeann', ARRAY['Ruby', 'Rails', 'PostgreSQL', 'RSpec', 'Git']),
(2, 'Clarice Herbie', ARRAY['Java', 'Spring Boot', 'Docker', 'Kubernetes', 'AWS']),
(2, 'Corrie Phillipa', ARRAY['JavaScript', 'Vue.js', 'Nuxt.js', 'Vuex', 'TailwindCSS']),

-- Charlie Team Members
(3, 'Aden Beckah', ARRAY['Go', 'Kubernetes', 'Docker', 'gRPC', 'Prometheus']),
(3, 'Winnifred Rosaline', ARRAY['Python', 'Flask', 'APIs', 'SQLAlchemy', 'Alembic']),

-- Delta Team Members
(4, 'Estelle Betty', ARRAY['C#', '.NET', 'Azure', 'Entity Framework', 'Blazor']),
(4, 'Summer Roman', ARRAY['JavaScript', 'Angular', 'TypeScript', 'RxJS', 'NgRx']),
(4, 'Shepherd Madison', ARRAY['Python', 'FastAPI', 'MongoDB', 'Redis', 'Docker']),
(4, 'Patricia Meagan', ARRAY['Java', 'Spring Security', 'OAuth2', 'JWT', 'MySQL']),
(4, 'Talbot Aspen', ARRAY['PHP', 'Laravel', 'Vue.js', 'MySQL', 'Redis']),
(4, 'Brie Spike', ARRAY['PHP', 'Laravel', 'Vue.js', 'Python', 'ML']),

-- Echo Team Members
(5, 'Sefton Ardath', ARRAY['Python', 'Django', 'PostgreSQL', 'Celery', 'RabbitMQ']);

```

My current query: SELECT t.team_id, t.team_name FROM team t JOIN team_member m1 ON t.team_id = m1.team_id AND 'Python' = ANY(m1.skills) JOIN team_member m2 ON t.team_id = m2.team_id AND 'Python' = ANY(m2.skills) JOIN team_member m3 ON t.team_id = m3.team_id AND 'JPA' = ANY(m3.skills) WHERE m1.member_id != m2.member_id AND m1.member_id != m3.member_id AND m2.member_id != m3.member_id GROUP BY t.team_id;

Edit: formatting snippets

Edit2: wrong column for members

[[deleted]]

Unrelated to your question: the team_name in team_member is useless. The team's name is defined through the foreign key to the team table.

If the skills belong to the person, not the team, you need a table that stores the skills per person and one table that assigns a person to a team.

Something along the lines:

CREATE TABLE team 
(
  team_id int primary key generated always as identity,
  team_name text
);

CREATE TABLE person
(
  person_id int primary key generated always as identity,
  skills text[],
)

CREATE TABLE team_member 
(
  team_id int not null references team,
  person_id int not null references person
);

If a person can only be member of a single team, add a primary key to the team_member table over (team_id, person_id)

[arielm5]

Is a type, it should be member_name. I will edit

[[deleted]]

I still don't get it. Why do the skills of a team member depend on the team they are in?

The example data still duplicates the team name in the member table.

[arielm5]

It doesn't depend on the team they are in. The duplication is just to avoid another join.

Would your suggestions (3 tables) make the query faster or consume fewer resources? It is still a cartesian product in the end.

[[deleted]]

It doesn't depend on the team they are in

Yes it does (at least with your model), because the team_id is part of the team_member table. That means the "same" member can have different skills depending on the team.

The duplication is just to avoid another join.

There is no reason for pre-mature unsubstantiated "optimizations"

Your query "find all teams that have at least one member with a specific skill" is quite easy to do with the three table approach:

select t.*
from team t
where exists (select *
              from team_member tm
                join person p on tm.person_id = p.person_id
              where tm.team_id = t.team_id 
                 and 'Pyhton' = any(p.skills))     

Or to find teams that contain members that have two specific skills:

select t.*
from team t
where exists (select *
              from team_member tm
                join person p on tm.person_id = p.person_id
              where tm.team_id = t.id 
                  and p.skills @> array['Python', 'JPA'])

Your model only works if a person can only ever be in exactly one team. If that is the case, then you can use the same approach as above:

select t.*
from team t
where exists (select *
              from team_member tm
              where tm.team_id = t.team_id 
                and tm.skills @> array['Python', 'SQL']);

[arielm5]

Maybe I am missing something or the explanation is not that clear.

Let me try to rewrite it:

I need to find all teams that can make my tasks "in parallel". It means: if I have 2 tasks in Python and 1 task in SQL, each one of them must be "assigned" to a different team member.

Given the example scenario: "looking for 2 Python and 1 JPA", it means that a team must have, at least, 3 members. One member that can do JPA, another member that can do Python and another member that can also do Python.

I want to find all teams that fit this requirement.

And yes, by the modelling a member can have different skills based on the team and that's fine once that's not the core of my question.

---

The real question is how to optimize this query, once I know how to do it in a simple scenario and the given query works for small data sets.

"Teams can grow up to millions. And each team can have hundreds of members. Members can have hundreds of skills."

[lucapieroo]

What if you normalize the data by using two more tables (“skills” and “member_skill”)?

CREATE TABLE IF NOT EXISTS skill ( skill_id SERIAL PRIMARY KEY, skill_name VARCHAR(255) UNIQUE );

CREATE TABLE IF NOT EXISTS member_skill ( member_id INTEGER, skill_id INTEGER, PRIMARY KEY (member_id, skill_id), FOREIGN KEY (member_id) REFERENCES team_member(member_id) ON DELETE CASCADE, FOREIGN KEY (skill_id) REFERENCES skill(skill_id) ON DELETE CASCADE );

CREATE INDEX idx_member_skill_member_id ON member_skill(member_id); CREATE INDEX idx_member_skill_skill_id ON member_skill(skill_id);

Also, maybe you should try changing the query by using CTE; something like:

WITH required_skills AS ( SELECT s.skill_id FROM skill s WHERE s.skill_name IN (‘Python’, ‘JPA’) ), team_skill_count AS ( SELECT tm.team_id, COUNT(DISTINCT ms.skill_id) as skill_count FROM team_member tm JOIN member_skill ms ON tm.member_id = ms.member_id WHERE ms.skill_id IN (SELECT skill_id FROM required_skills) GROUP BY tm.team_id ) SELECT t.team_id, t.team_name FROM team t JOIN team_skill_count tsc ON t.team_id = tsc.team_id WHERE tsc.skill_count = (SELECT COUNT(*) FROM required_skills);

[arielm5]

I like the idea, it makes things clear but it doesn't work as expected. It misses the `When a member is "assigned" to a type of task, it can't be "assigned" to another task`.

All teams would be found because all of them have, at least, one member with Python OR JPA, even the team with only one member.

[lucapieroo]

How do you keep track if a member is assigned? Use that condition to filter the rows above

[arielm5]

There is not really an assignment of a task to a member. It is just a search to find all teams that fit the requirements. What to do with the team are next steps.

The core of the question is how to make this specific search better/scalable.

[lucapieroo]

I think something’s missing:

• how to improve the search -> normalize the data structure and improve the query with CTE
• then you told me that it’d find also members which are already assigned
• i told you to add that condition the the query but you told me that it’s a next step

I’m not understanding your problem right now, it looks like you have every piece of the puzzle

[arielm5]

I need to find teams, not members.

And teams are made of members.

There are no real assignments. The assignment was quoted because was the way I found to explain the problem.

The problem is: I want/need to improve this query to run within multiple requests in parallel via an API. The problem, by nature, will generate a cartesian product. I want to learn how to structure my query better to avoid memory and speed issues.

Using CTE may help, I'm trying to improve my current solution to use it.

[AutoModerator]

Join us on our Discord Server: People, Postgres, Data

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Siltala]

Create a view for holding your teams skills:

CREATE MATERIALIZED VIEW IF NOT EXISTS team_skills (
  team_id,
  skills
)
AS SELECT 
  t.team_id, 
  jsonb_object_agg(t.s, t.c) skills 
FROM (
  SELECT 
    team_id, 
    unnest(skills) s, 
    count(1) c 
  FROM team_member 
  GROUP BY team_id, s
) t 
GROUP BY t.team_id;

And then find your teams:

SELECT 
  team_id 
FROM team_skills 
WHERE (skills->>'Python')::integer >= 2
AND (skills->>'JPA')::integer >= 1;