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 ?
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),
foreign key(meeting_id) references meetings(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.
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';
