How to model teams with 3 (semi-)fixed positions, and search for same / different teams efficiently?

Question

I want to represents a team in my Postgres database. These teams have always 3 members. Each member have a slot, 1 for leader, and 2 and 3 for member (also useful to display the members in the good order) Each member have a level but for this team only (A character can appear in different team with a different level.

Though, one of my features, I want to select all teams that has a different composition which means, a different leader and members. (teams with member A, B and C are considered as the same as A, C and B but are different from team with C, A and B)

In another feature, I want to select all teams that contains the exact same composition.

For now, I have 3 tables,

CREATE TABLE teams (
    "teamID" integer NOT NULL,
    "remainingTime" double precision NOT NULL,
    "desc" text NOT NULL,
    difficulty character varying NOT NULL
);

CREATE TABLE public.team_member (
    "teamID" smallint NOT NULL,
    "charID" smallint NOT NULL,
    "level" smallint NOT NULL,         /*can be from 1 to 5*/
    "slot" smallint DEFAULT 1 NOT NULL /*1, 2 or 3*/
);

CREATE TABLE public."character" (
    "charID" integer NOT NULL,
    name character varying NOT NULL,
    /* others attribute */
);

My question, is now, what would be the best way to write a query that corresponds to my features, i.e. one for different teams and another for teams with the same composition ?

I guess I could create an array for each team representing the ID of its members and then compare them, but wouldn't that cause problems in terms of performance with a lot of teams ?

Is there, maybe, a more optimised model for this case ? I thought of creating a sort of table storing the ID of its 3 members in one line and then create another table team_variation that store the level of the 3 members, and the related desc and time, but I don't know if that's really a good idea

final note, I use this database on a Javascript server so I can, if necessary, process the data received by a query that would have done a part of what I want

Answer 1

I propose this relational design:

CREATE TABLE team (
  team_id        int2 PRIMARY KEY
, remaining_time float NOT NULL  -- ?? use date/time type for temporal data!
, description    text NOT NULL   -- ? description?
, difficulty     text NOT NULL
);

CREATE TABLE person (
  pers_id   int2 PRIMARY KEY
, full_name text NOT NULL
  -- other attributes
);

CREATE TABLE team_member (
  team_id int2 NOT NULL REFERENCES team
, pers_id int2 NOT NULL REFERENCES person
, level   int2 NOT NULL CHECK (level BETWEEN 1::int2 AND 5::int2)  -- can be from 1 to 5
, slot    "char" NOT NULL DEFAULT 'l'::"char"
, pos     "char" NOT NULL DEFAULT '1'::"char"
, CONSTRAINT team_member_pkey PRIMARY KEY (team_id, pers_id)
  -- team can have 1 leader and 2 members
, CONSTRAINT chk_1lead_2members CHECK (slot = 'l' AND pos = '1'
                                    OR slot = 'm' AND pos IN ('2', '3'))
  -- 'l' ... leader, 'm' ... member
  -- leader must have pos '1', member must have pos '2' or '3'
, CONSTRAINT uni_team_slot_pos UNIQUE(slot, pos, team_id) INCLUDE (pers_id)
  -- team must have distinct persons; plus: provides useful index
);

-- add idx with reversed cols
CREATE UNIQUE INDEX team_member_pers_id_team_id ON team_member(pers_id, team_id);

fiddle

The combination of NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints enforces your model. You can still enter incomplete teams (not all slots filled), allowing for intermediary states. But those are excluded in my queries below.

There is good reason for the mix of constraints and indexes for table team_member. See:

• Is a composite index also good for queries on the first field?

I kept your choice of smallint, but made the PKs of tables team and person match. Don't introduce a type mismatch here. I even kept the minimal payload size of team_member rows at 8 bytes (maybe that's what you were optimizing for?) But unless you have good reasons, plus can reliably rule out overflow, use integer (or even bigint) instead. Less error prone. Adds only little cost.

Note the data type "char" for cheap enumeration. (Optional.) Not to be confused with char or varchar! See:

• Any downsides of using data type "text" for storing strings?

Use a date/time data type for date/time data (column remaining_time). Not float.

Don't mix singular and plural forms for table names without good reason.

Use legal, lower-case, unquoted identifiers. In particular, avoid reserved words. See:

• Are PostgreSQL column names case-sensitive?

Queries

Already somewhat optimized for performance.

Distinct teams

Full list of distinct teams. Pick team with smallest ID from set of dupes. Exclude incomplete teams.

SELECT DISTINCT ON (l.pers_id, m.m1, m.m2)
       team_id, l.pers_id AS lead, m.m1, m.m2
FROM   team_member l
JOIN  (
   SELECT team_id, min(pers_id) AS m1, max(pers_id) AS m2
   FROM   team_member m
   WHERE  m.slot = 'm'
   GROUP  BY 1
   HAVING count(*) = 2  -- exclude incomplete teams
   ) m USING (team_id)
WHERE  l.slot = 'l'
ORDER  BY l.pers_id, m.m1, m.m2, team_id;  -- pick team with smallest ID from dupes

See:

• Select first row in each GROUP BY group?

Same teams

Select same teams as given team (1, 2, 3) - 1 being the lead. Again, exclude incomplete teams.

SELECT team_id, l.pers_id AS lead, m1.pers_id AS m1, m2.pers_id AS m2
FROM   team_member l
JOIN   team_member m1 USING (team_id)
JOIN   team_member m2 USING (team_id)
WHERE  l.slot = 'l'
AND    l.pers_id = 1  -- lead
AND    m1.slot = 'm'
AND    m1.pers_id = LEAST (2,3)  -- member 1
AND    m2.slot = 'm'
AND    m2.pers_id = GREATEST (2,3)   -- member 2
ORDER  BY lead, m1, m2;

fiddle