Design sql tables with list of foreign keys

Question

I want to create an application for rotating pairs in a team every day. I need to store this in the database. Requirments are:

• A team should be assigned to one ore more members.
• Each team can have multiple tabs and different members allocate in them.(If team consist of 4 members for the particular tab only 3 should be part of it)
• Each tab will have a pair of members or list of pairs per day stored.

I have ended up designing something like the example below:

create table if not exists team (
    id serial not null primary key,
    name text not null
);

create table if not exists member (
    id serial not null primary key,
    team_id integer references team(id),
    nickname text
);

create table if not exists team_tab (
    id bigserial not null primary key,
    team_id integer references team(id) on delete cascade,
    name text not null,
    member_ids integer[],
);

create table if not exists team_tab_pairs (
    id bigserial not null primary key,
    team_tab_id integer not null references team_tab(id) on delete cascade,
    tab_date date not null,
    pair_ids integer[][],
);

I need an advice and suggestions how could I achieve this without having a list of references ids stored in the 2 tables below.

Answer 1

You need an extra table to design an M:N relationship. This is the case, for example, between "team tab" and "member". In addition to both main entities:

create table member (
    id serial not null primary key,
    team_id integer references team(id),
    nickname text
);

create table team_tab (
    id bigserial not null primary key,
    team_id integer references team(id) on delete cascade,
    name text not null
);

...you'll need to create a table to represent the M:N relationship, as in:

create table team_tab_member (
  team_tab_id bigint not null,
  member_id int not null,
  primary key (team_tab_id, member_id) -- optional depending on the model
);