Theo's Typesafe Cult•10mo ago

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.

1 Reply

ImEgg•10mo ago

https://www.studytonight.com/dbms/database-normalization.php I would say if you can get your database to be at the 3rd Form of normalization, you'll be just fine. "over-engineering" is a pretty weird term to use. I'd try to focus more on best-practices and why normalization is important and other basics of SQL database design. Tons of resources online to learn.

Normalization in DBMS - 1NF, 2NF, 3NF, BCNF, 4NF and 5NF | Studyton...

Learn Normalization in DBMS and how to implement 1NF in DBMS, 2NF in DBMS, BCNF in DBMS, 4NF in DBMS, and 5NF in DBMS with examples.

Gaming

Programming

help with DB schema