1 reply

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

events

with columns:

create table events (
id SERIAL primary KEY,
title text,
day text,
start_time time(0),
end_time time(0)
);

create table events (
id SERIAL primary KEY,
title text,
day text,
start_time time(0),
end_time time(0)
);

and another table with

participants

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

  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.

TTC

Theo's Typesafe Cult•2y ago•

1 reply

Boris Povolotsky

help with DB schema

events

events

with columns:

create table events (
id SERIAL primary KEY,
title text,
day text,
start_time time(0),
end_time time(0)
);

create table events (
id SERIAL primary KEY,
title text,
day text,
start_time time(0),
end_time time(0)
);

and another table with

participants

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

  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

help with DB schema

Similar Threads

help with DB schema

Similar Threads

Similar Threads

Similar Threads