0

I have a setup where I have accounts and teams. One account can belong to multiple teams and one team can belong to multiple accounts.

How should I go about designing this in Postgres?

0

1 Answer 1

1

Use an intermediary table with two columns, each being a foreign key to one of the tables.

testdb=# create table teams(team_id bigint primary key);
CREATE TABLE
testdb=# create table accounts(account_id bigint primary key);
CREATE TABLE
testdb=# create table accounts_teams(account_id bigint, team_id bigint);
CREATE TABLE
testdb=# alter table accounts_teams add constraint accounts_teams_account_id foreign key (account_id) references accounts(account_id);
ALTER TABLE
testdb=# alter table accounts_teams add constraint accounts_teams_team_id foreign key (team_id) references teams(team_id);
ALTER TABLE
testdb=# \d accounts_teams
            Table "public.accounts_teams"
   Column   |  Type  | Collation | Nullable | Default 
------------+--------+-----------+----------+---------
 account_id | bigint |           |          | 
 team_id    | bigint |           |          | 
Foreign-key constraints:
    "accounts_teams_account_id" FOREIGN KEY (account_id) REFERENCES accounts(account_id)
    "accounts_teams_team_id" FOREIGN KEY (team_id) REFERENCES teams(team_id)
1
  • If you add a primary key constraint on (account_id, team_id), I'll upvote. Jun 15, 2021 at 22:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.