Спроектировать таблицы для системы MedClinic

79. Спроектировать таблицы для системы MedClinic

Условие задачи:
Необходимо спроектировать структуру базы данных для медицинской клиники MedClinic.

В системе:

работают врачи;
у одного врача может быть несколько специализаций;
пациенты записываются на приём к врачу;
на приёме врач ставит диагноз;
врач может сделать одно или несколько назначений;
назначением может быть лекарство или процедура;
необходимо хранить расписание врачей по дням недели.

Нужно определить основные таблицы, их столбцы и связи между ними.

Спойлеры к решению

Подсказки

💡 Связь между врачами и специализациями — many-to-many, поэтому потребуется промежуточная таблица.
💡 Приём должен быть связан с пациентом и врачом.
💡 Если важно понимать, по какой специализации проходит приём, удобно связывать его с записью doctor_specializations.
💡 Диагноз связан с конкретным приёмом.
💡 Назначений у одного приёма может быть несколько, поэтому их лучше вынести в отдельную таблицу.
💡 Расписание врача можно хранить как день недели, время начала и время окончания работы.

Решение

Основные таблицы:

doctors
----------------------------
id                BIGINT PK
full_name         VARCHAR

specializations
----------------------------
id                BIGINT PK
name              VARCHAR UNIQUE

Один врач может иметь несколько специализаций, а одна специализация может принадлежать нескольким врачам.

Поэтому нужна промежуточная таблица:

doctor_specializations
----------------------------
id                BIGINT PK
doctor_id         BIGINT FK -> doctors.id
specialization_id BIGINT FK -> specializations.id

UNIQUE(doctor_id, specialization_id)

Пациенты:

patients
----------------------------
id                BIGINT PK
full_name         VARCHAR
birth_date        DATE
phone             VARCHAR

Запись на приём:

appointments
----------------------------------------
id                       BIGINT PK
patient_id               BIGINT FK -> patients.id
doctor_specialization_id BIGINT FK -> doctor_specializations.id
appointment_at           TIMESTAMP
status                   VARCHAR

Поле:

doctor_specialization_id

позволяет сразу определить и врача, и специализацию, по которой пациент записался.

Например:

doctor_specializations

id | doctor_id | specialization_id
---+-----------+------------------
1  | 10        | 1
2  | 10        | 2

означает, что врач 10 работает сразу по двум специализациям.

Диагнозы:

diagnoses
----------------------------
id                BIGINT PK
appointment_id    BIGINT FK -> appointments.id
diagnosis         VARCHAR

Такая структура позволяет при необходимости хранить несколько диагнозов для одного приёма.

Назначения:

prescriptions
----------------------------
id                BIGINT PK
appointment_id    BIGINT FK -> appointments.id
type              VARCHAR
name              VARCHAR
instructions      VARCHAR

Поле type может содержать, например:

MEDICATION
PROCEDURE

Пример:

id | appointment_id | type       | name
---+----------------+------------+----------------
1  | 100            | MEDICATION | Амоксициллин
2  | 100            | PROCEDURE  | Ингаляция

Таким образом, один приём может содержать несколько назначений.

Расписание врачей:

doctor_schedule
----------------------------
id                BIGINT PK
doctor_id         BIGINT FK -> doctors.id
day_of_week       VARCHAR
start_time        TIME
end_time          TIME

Например:

doctor_id | day_of_week | start_time | end_time
----------+-------------+------------+---------
10        | MONDAY      | 09:00      | 18:00
10        | TUESDAY     | 09:00      | 18:00
10        | FRIDAY      | 10:00      | 16:00

Связи между таблицами:

doctors
   |
   | 1
   |
   | N
doctor_specializations
   |
   | N
   |
   | 1
specializations


patients
   |
   | 1
   |
   | N
appointments
   |
   +--------------------+
   |                    |
   | 1                  | 1
   |                    |
   | N                  | N
diagnoses          prescriptions


doctors
   |
   | 1
   |
   | N
doctor_schedule

Общая схема:

doctors
   |
   +----< doctor_specializations >---- specializations
   |                |
   |                |
   |                v
   |           appointments >---- patients
   |                |
   |                +----< diagnoses
   |                |
   |                +----< prescriptions
   |
   +----< doctor_schedule

Основные типы связей:

Doctor         N : M  Specialization
Patient        1 : N  Appointment
DoctorSpecialization 1 : N Appointment
Appointment    1 : N  Diagnosis
Appointment    1 : N  Prescription
Doctor         1 : N  DoctorSchedule

Промежуточная таблица doctor_specializations необходима из-за связи many-to-many: один врач может быть одновременно терапевтом и кардиологом, а специализация «Терапевт» может быть у многих врачей.