Adding a column of type Array of INT's where each INT is a primary key from another table (POST-CodePudding

Given two tables like so

CREATE TABLE participants(
    id SERIAL PRIMARY KEY,
    Name TEXT NOT NULL,
    Title TEXT NOT NULL
);

CREATE TABLE meetings (
    id SERIAL PRIMARY KEY,
    Organizer TEXT NOT NULL,
    StartTime DATE NOT NULL,
    EndTime DATE NOT NULL,
    Participants INT[],
);

I want Participants column of 'meetings' table to contain set of integers which are all primary keys (specific participant) from 'participants' table.

How do I define this field in 'meetings' table ?

CodePudding user response：

The old fashioned way is to create a many-many table, with a couple of commonsense constraints:

CREATE TABLE meetings_participants(
    meeting_id int not null,
    participant_id int not null,
    primary key (meeting_id, participant_id),
    constraint fk_meeting_id foreign key(meeting_id) references meetings(id),
    constraint fk_participant_id foreign key(participant_id) references participants(id)
)

Now it is easy to add and remove people to meetings be inserting or deleting rows or query meetings that e.g. have 4 or more participants.

CodePudding user response：

A more common approach is to create a junction table for the meeting participants.

CREATE TABLE participants (
  participant_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  Name TEXT NOT NULL,
  Title TEXT NOT NULL
);

CREATE TABLE meetings (
  meeting_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  Organizer TEXT NOT NULL,
  StartTime DATE NOT NULL,
  EndTime DATE NOT NULL
);

CREATE TABLE meeting_participants(
  meeting_id INT NOT NULL, 
  participant_id INT NOT NULL, 
  PRIMARY KEY (meeting_id, participant_id), 
  FOREIGN KEY (meeting_id) REFERENCES meetings(meeting_id), 
  FOREIGN KEY (participant_id) REFERENCES participants(participant_id)
);

Which is then used to join the 2 tables.
For example:

SELECT m.*, p.*
FROM meeting_participants mp
JOIN meetings m USING(meeting_id)
JOIN participants p USING(participant_id)
WHERE m.Organizer = 'John Doe';