Theo's Typesafe CultTTC
Theo's Typesafe Cult•2y ago
Boris Povolotsky

help with DB schema

Hi, I need help with designing a database, I'm fairly new to SQL (postgres) and I'm terrified of over-engineering.

Essentially, I'm building an app for an organization that has dozen of workplaces, each consisting of a team of therapists with the following requirements:
  • Each workplace/location needs a weekly schedule, which therapist in which room, and with which client.
  • they don't need a yearly calendar.
  • No one-off events/sessions.
There are usually between 4-6 therapists and 8-9 clients.

My thoughts:
Create a table 
events
with columns:
create table events (
id SERIAL primary KEY,
title text,
day text,
start_time time(0),
end_time time(0)
);


and another table with 
participants
with cols:
  id ...,
  event_id INTEGER references events (id),
  participant_id INT,
  participant_type, -- 'user/therapist' or 'client'
  FOREIGN KEY (participant_id) REFERENCES clients (id) DEFERRABLE INITIALLY DEFERRED, -- ChatGPT's suggestion to make a column refer one of TWO tables (clients/users)
  FOREIGN KEY (participant_id) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED


I'm unfamiliar with SQL and don't know how many tables is too many for a small application, so I'm afraid of committing horrible design mistakes now.
Is this a good approach to the problem?

A 'stupid' alternative is to shove everything in the events table. I'm not sure how, use an array of IDs in a "participants" column? I read everywhere that using arrays is dangerous in DB, idk why.
Was this page helpful?