ER Diagram

Diagram

---

Data Requirements

Entities

Doctor_Level

A reference entity holding the set of rank levels a doctor can hold (e.g. Resident, Specialist, Consultant). Modeled as its own entity so the list of valid levels can be maintained centrally and referential integrity is enforced through the FK instead of relying on consistent free text.

• Candidate keys:

level_id (surrogate)

level (natural, UNIQUE).

level_id was chosen as PK because it is stable, renaming a level would otherwise cascade an update to every Doctors row.

• Attributes:

level_id (PK, bigint) — required

level (text) — required, unique

Doctor_Specialization

Doctor_Specialization is an entity listing the medical specializations doctors can practice. It is kept separate from Doctors so Procedures can reference which specializations are allowed to perform them.

• Candidate keys:

specialization_id (surrogate)

specialization_name (natural, UNIQUE).

specialization_id was chosen as PK so a rename doesn't cascade into Doctors and Specialization_Procedures.

• Attributes:

specialization_id (PK, bigint) — required; supplied by the application

specialization_name (text) — required, unique

Departments

Departments is an entity representing the clinic's organizational departments. Departments both employ doctors (works_in) and offer procedures (performs), so it needed to be a first-class entity.

• Candidate keys:

department_id (surrogate)

department_name (natural, UNIQUE).

department_id was chosen as PK so a rename doesn't cascade into Doctors and Department_Procedures.

• Attributes:

department_id (PK, bigint) — required

department_name (text) — required, unique

Doctors

Doctors is the central entity for doctors who use the system to see patients, record diagnoses, order tests and perform procedures. Level, specialization and department are FKs to lookup entities rather than free text to prevent inconsistent values and allow filtering.

• Candidate keys:

doctor_id (surrogate)

email_address (natural, UNIQUE).

doctor_id was chosen as PK because an email address can change, and using a mutable value as PK would force cascading updates across every table referencing a doctor.

• Attributes:

doctor_id (PK, bigint) — required

first_name (text) — required

last_name (text) — required

email_address (text) — required, unique, must contain an "@" (LIKE '%@%' - format check)

level_id (FK, bigint) — required, references Doctor_Level

specialization_id (FK, bigint) — required, references Doctor_Specialization

department_id (FK, bigint) — required, references Departments

Patients

Patients represents the people receiving care, it is the anchor entity for almost all clinical data like appointments, diagnoses, medical records, tests, procedures, billing.

• Candidate keys:

patient_id (surrogate),

embg (natural - the Macedonian unique master citizen number, required and unique).

patient_id was still chosen as PK rather than embg: embg is sensitive personal data that is better not propagated as a foreign key into every dependent table (Diagnosis, Appointments, Medical_Records, etc.), and a numeric surrogate keeps those relationships smaller and independent of a nationally issued identifier.

• Attributes:

patient_id (PK, bigint) — required

first_name (text) — required

last_name (text) — required

email_address (text) — optional, unique if provided

date_of_birth (date) — required

blood_type (text) — optional, restricted to one of: A+, A-, B+, B-, AB+, AB-, O+, O-

gender (text) — optional, restricted to one of: MALE, FEMALE

phone_number (text) — optional, free text

embg (text) — required, unique, 13-digit format

Admin

Represents clinic administrative staff who manage billing and back-office operations. Kept separate from Doctors and Lab_Technician since admins have no clinical attributes.

• Candidate keys:

admin_id (surrogate),

username (natural, UNIQUE),

email (natural, UNIQUE).

admin_id was chosen as PK so a username change doesn't cascade into Users and Billing.

• Attributes:

admin_id (PK, bigint) — required

username (text) — required, unique

name (text) — required

lastname (text) — required

email (text) — required, unique, must contain "@adminmedora" (LIKE '%@adminmedora%' - restricts admins to the internal admin email domain)

Lab_Technician

Lab Technician entity is the lab staff who conduct the tests ordered by doctors and record results in Performed_Lab_Tests. Modeled separately from Doctors since technicians don't diagnose or prescribe.

• Candidate keys:

technician_id (surrogate), username (natural, UNIQUE), email (natural, UNIQUE).

technician_id was chosen as PK for the same reason as Admin.

• Attributes:

technician_id (PK, bigint) — required, supplied by the application

username (text) — required, unique

name (text) — required

lastname (text) — required

email (text) — required, unique, must contain "@labmedora" (LIKE '%@labmedora%' - restricts technicians email domain)

Users

Users is the authentication and login entity implementing role based access. A Users row is meant to serve exactly one of Patients, Doctors, Admin or Lab_Technician via whichever of the four nullable FKs is populated.

• Candidate keys:

user_id (surrogate)

username (natural, UNIQUE).

user_id was chosen as PK so a username change doesn't break session/audit data referencing the account. user_id is the one PK in this schema that is DB-generated (SERIAL).

• Attributes:

user_id (PK, serial) — required, auto-generated by the database

username (varchar(255)) — required, unique

password (varchar(255)) — required, expected to be stored as a hash

role (varchar(50)) — required, intended to be one of patient/doctor/admin/technician

first_name (varchar(100)) — optional

last_name (varchar(100)) — optional

patient_id (FK, bigint, nullable) — references Patients

doctor_id (FK, bigint, nullable) — references Doctors

admin_id (FK, bigint, nullable) — references Admin

technician_id (FK, bigint, nullable) — references Lab_Technician

is_active (boolean) — optional, defaults to true

Appointments

Appointments represents a scheduled meeting between one patient and one doctor. No natural key exists, so a surrogate key is mandatory.

• Candidate keys:

appointment_id (surrogate) only.

• Attributes:

appointment_id (PK, bigint) — required

appointment_date (date) — required

appointment_time (time) — required

status (text) — required, restricted to one of: SCHEDULED, COMPLETED, CANCELLED, IN_PROGRESS

patient_id (FK, bigint) — required, references Patients

doctor_id (FK, bigint) — required, references Doctors

Diagnosis

Diagnosis entity represents a diagnosis a doctor makes for a patient. It is the pivot the clinical model turns on since procedures, referrals and medical records all connect back to it.

• Candidate keys:

diagnosis_id (surrogate) only.

the same diagnosis name can legitimately recur for the same patient over time.

• Attributes:

diagnosis_id (PK, bigint) — required; supplied by the application

name (text) — required

description (text) — optional

patient_id (FK, bigint) — required, references Patients

doctor_id (FK, bigint) — required, references Doctors

Procedures

Procedures is a catalog entry describing a type of procedure with its cost and the diagnosis it was primarily created for. Each row carries a direct diagnosis_id FK for its main diagnosis (1:N), while Diagnosis_Procedures (M:N) additionally lets the same catalog procedure be linked to other diagnoses without duplicating the row.

• Candidate keys:

procedure_id (surrogate) only.

procedure_type alone isn't unique, since the same type can be cataloged for different diagnoses at different costs.

• Attributes:

procedure_id (PK, bigint) — required; supplied by the application

procedure_type (text) — required, must not be blank/whitespace-only

procedure_date (date) — required

description (text) — optional

cost (decimal) — required, must be ≥ 0

doctor_id (FK, bigint) — required, references Doctors

diagnosis_id (FK, bigint) — required, references Diagnosis

Procedure_Results

Procedure_Results stores the outcome of a performed procedure. Kept separate from Procedures since one procedure can generate more than one result over time.

• Candidate keys:

result_id (surrogate) only.

• Attributes:

result_id (PK, bigint) — required

result_description (text) — optional, must not be blank

result_date (date) - required

procedure_id (FK, bigint) - required, references Procedures

Prescriptions

Prescriptions is an entity for medications that can be prescribed. Kept minimal because everything patient specific like dosage, frequency, duration lives on Prescription_Medical_Records instead, since it varies per medical record.

• Candidate keys:

prescription_id (surrogate) only.

medication_name is required but has no UNIQUE constraint in this schema, so it is not a true candidate key as implemented.

• Attributes:

prescription_id (PK, bigint) - required

medication_name (text) - required not enforced unique

Prescription_Restriction

Entity that records a restriction attached to a prescription. Separate entity because one prescription can carry multiple independent restrictions.

• Candidate keys:

restriction_id (surrogate) only.

• Attributes:

restriction_id (PK, bigint) - required

description (text) - required, must not be blank

prescription_id (FK, bigint) — required, references Prescriptions

Lab_Tests

A catalog entity describing the lab tests the clinic offers, with standard cost. Separate from Performed_Lab_Tests, which records each actual instance of a test being run.

• Candidate keys:

test_id (surrogate) only.

• Attributes:

test_id (PK, bigint) — required

test_name (text) — required, must be blank, not enforced unique

description (text) — optional

cost (decimal) — required, must be ≥ 0

Lab_Results

Entity that stores the outcome of a catalog lab test, linked directly to the Lab_Tests catalog entry so the result stays reusable across reports (Medical_Report_Lab_Results, Medical_Record_Lab_Results).

• Candidate keys:

result_id (surrogate) only.

• Attributes:

result_id (PK, bigint) - required

results (text) - required, must not be blank

result_date (date) — required, must not be later than the current date

test_id (FK, bigint) — required, references Lab_Tests

Medical_Records

Acts as a folder aggregating everything about a patient's clinical episode, by being the hub that other entities connect back to. Kept deliberately minimal so record specific detail lives on the appropriate junction table instead of being duplicated here.

• Candidate keys:

record_id (surrogate) only.

a patient can have multiple records over time, so patient_id alone isn't unique.

• Attributes:

record_id (PK, bigint) — required

patient_id (FK, bigint) — required, references Patients

Allergies

An entity listing known allergies with a general severity classification, so the same allergy can be linked to many patients without re-entering its description each time.

• Candidate keys:

allergy_id (surrogate)

name (natural, UNIQUE).

allergy_id was chosen as PK so it survives a naming correction.

• Attributes:

allergy_id (PK, bigint) - required

name (text) — required, unique, must not be blank

allergy_severity (text) - required, restricted to one of: LOW, MEDIUM, HIGH, CRITICAL

Symptoms

A catalog entity listing known symptoms so the same symptom can be reused across many patients and diagnoses rather than typed freely each time.

• Candidate keys:

symptom_id (surrogate)

name (natural, UNIQUE-enforced).

symptom_id was chosen as PK for consistency with other catalog entities.

• Attributes:

symptom_id (PK, bigint) — required

name (text) — required, unique, must be blank

description (text) — optional, if present, must not be blank

Referrals

Records one doctor referring a patient's medical record to another doctor. Carries two separate FKs to Doctors (from_doctor_id, to_doctor_id) because a referral inherently involves two distinct doctor roles.

• Candidate keys:

referral_id (surrogate) only.

• Attributes:

referral_id (PK, bigint) — required

reason (text) — required, must be blank

referral_date (date) — required

record_id (FK, bigint) — required, references Medical_Records

from_doctor_id (FK, bigint) — required, references Doctors

to_doctor_id (FK, bigint) — required, references Doctors

Medical_Report

Represents a formal written report a doctor produces based on a medical record, which can reference multiple lab results as supporting evidence.

• Candidate keys:

report_id (surrogate) only.

a record can generate more than one report over time.

• Attributes:

report_id (PK, bigint) — required; supplied by the application

description (text) — required

report_date (date) — required

record_id (FK, bigint) — required, references Medical_Records

doctor_id (FK, bigint) — required, references Doctors

Billing

Represents an invoice generated for a medical record, covering whichever procedures/tests it includes, processed by an administrator. payment_date is nullable since a bill can be Pending before it's ever paid.

• Candidate keys:

bill_id (surrogate) only.

• Attributes:

bill_id (PK, bigint) — required

total_cost (decimal) — required, must be ≥ 0

payment_status (text) — required, restricted to one of: PENDING, PAID, CANCELLED

payment_date (date) — optional

record_id (FK, bigint) — required, references Medical_Records

admin_id (FK, bigint) — required, references Admin

Performed_Procedures

Records a specific, actual occurrence of a catalog procedure carried out on a patient. Distinct from Procedures, the catalog definition. diagnosis_id is nullable since a performed procedure isn't always tied to a newly recorded diagnosis.

• Candidate keys:

performed_id (surrogate) only.

• Attributes:

performed_id (PK, bigint) — required

procedure_id (FK, bigint) — required, references Procedures

doctor_id (FK, bigint) — required, references Doctors

patient_id (FK, bigint) — required, references Patients

diagnosis_id (FK, bigint, nullable) — references Diagnosis

procedure_date (date) — required

notes (text) — optional

Performed_Lab_Tests

Records a specific, actual occurrence of a catalog lab test carried out on a patient that is ordered by a doctor, conducted by a technician.

• Candidate keys:

performed_test_id (surrogate) only.

• Attributes:

performed_test_id (PK, bigint) — required

test_id (FK, bigint) — required, references Lab_Tests

patient_id (FK, bigint) — required, references Patients

doctor_id (FK, bigint) — required, references Doctors

technician_id (FK, bigint) — required, references Lab_Technician

test_date (date) — required

notes (text) — optional

Associative entities (M:N junctions that carry their own attributes)

Prescription_Medical_Records

An associative (M:N) entity linking Prescriptions to Medical_Records. Carries its own attributes because dosage/frequency/duration are specific to the record a medication was prescribed within, not to the medication itself.

• Candidate keys:

the composite (prescription_id, record_id) — a medication should appear at most once per record.

• Attributes:

prescription_id (FK, bigint) — required, part of composite PK, references Prescriptions

record_id (FK, bigint) — required, part of composite PK, references Medical_Records

dosage (text) — required

frequency (text) — required

duration (text) — required

notes (text) — optional

Medical_Record_Symptoms

An associative (M:N) entity linking Medical_Records to Symptoms. Intended to carry a per-record severity, since how severe a symptom is is specific to that record/visit, not the symptom catalog entry.

• Candidate keys:

the composite (record_id, symptom_id) - a symptom should appear at most once per record.

• Attributes:

record_id (FK, bigint) — required, part of composite PK, references Medical_Records

symptom_id (FK, bigint) — required, part of composite PK, references Symptoms

severity (text) — optional; no restricted value set enforced in the database

Medical_Record_Allergies

An associative (M:N) entity linking Medical_Records to Allergies. Intended to carry the reaction and severity observed for that patient within that specific record, distinct from the general allergy catalog entry.

• Candidate keys:

the composite (record_id, allergy_id) — an allergy should appear at most once per record.

• Attributes:

record_id (FK, bigint) — required, part of composite PK, references Medical_Records

allergy_id (FK, bigint) — required, part of composite PK, references Allergies

reaction (text) — optional

severity (text) — optional

---

Relations

is (Doctor_Level ↔ Doctors, 1:N)

each doctor has one level, a level applies to many doctors.

has_specialization (Doctor_Specialization ↔ Doctors, 1:N)

each doctor has one specialization, a specialization applies to many doctors.

works_in (Departments ↔ Doctors, 1:N)

each doctor belongs to one department, a department employs many doctors.

identifies (Users ↔ Patients / Doctors / Admin / Lab_Technician, 1:1)

a user account links to exactly one underlying person record, depending on role.

has_allergies (Patients ↔ Allergies, M:N via Patient_Allergies)

patients can have multiple allergies, an allergy can affect many patients.

affects_prescription (Allergies ↔ Prescription_Restriction, M:N via Allergy_Prescription_Restrictions)

an allergy can be tied to multiple restrictions; a restriction can apply to multiple allergies.

has_symptoms (Patients ↔ Symptoms, M:N via Patient_Symptoms)

patients can report multiple symptoms, a symptom can be reported by many patients.

indicates (Symptoms ↔ Diagnosis, M:N via Diagnosis_Symptoms)

a diagnosis can involve multiple symptoms, a symptom can appear in multiple diagnoses.

books (Patients ↔ Appointments, 1:N)

a patient books many appointments, each appointment belongs to one patient.

attends (Doctors ↔ Appointments, 1:N)

a doctor attends many appointments, each appointment is handled by one doctor.

diagnoses (Doctors ↔ Diagnosis, 1:N)

a doctor makes many diagnoses, each diagnosis is made by one doctor.

diagnosed_with (Patients ↔ Diagnosis, 1:N)

a patient can have many diagnoses; each diagnosis belongs to one patient.

performs_allowed (Doctor_Specialization ↔ Procedures, M:N via Specialization_Procedures)

a specialization is linked to the procedures it's allowed to perform.

requires (Diagnosis ↔ Procedures, M:N via Diagnosis_Procedures)

a diagnosis can require multiple procedures, and the same procedure can be linked to multiple diagnoses ,in addition to the direct diagnosis_id FK each procedure row already carries.

produces (Procedures ↔ Procedure_Results, 1:N)

a procedure can produce multiple results, each result belongs to one procedure.

performs (Departments ↔ Procedures, M:N via Department_Procedures)

a department can perform multiple procedures, and a procedure can be performed in multiple departments.

billed_for (Procedures ↔ Billing, M:N via Billing_Procedures)

a bill can include multiple procedures, a procedure can appear on multiple bills.

restricts (Prescriptions ↔ Prescription_Restriction, 1:N)

a prescription can have multiple restrictions, each restriction belongs to one prescription.

tested_as (Lab_Tests ↔ Performed_Lab_Tests, 1:N)

a catalog test can be performed many times; each performed test refers to one catalog test.

takes_test (Patients ↔ Performed_Lab_Tests, 1:N)

a patient can undergo many performed tests, each performed test belongs to one patient.

orders_test (Doctors ↔ Performed_Lab_Tests, 1:N)

a doctor can order many tests, each performed test is ordered by one doctor.

conducts (Lab_Technician ↔ Performed_Lab_Tests, 1:N)

a technician can conduct many tests, each performed test is run by one technician.

produces_test (Lab_Tests ↔ Lab_Results, 1:N)

a catalog test can produce multiple results, each result belongs to one test.

billed_for_test (Lab_Tests ↔ Billing, M:N via Billing_Lab_Tests)

a bill can include multiple tests, a test can appear on multiple bills.

performed_as (Procedures ↔ Performed_Procedures, 1:N)

a catalog procedure can be performed many times.

performed_on (Patients ↔ Performed_Procedures, 1:N)

a patient can have many procedures performed, each performed procedure belongs to one patient.

performed_by (Doctors ↔ Performed_Procedures, 1:N)

a doctor can perform many procedures, each performed procedure has one doctor.

has (Patients ↔ Medical_Records, 1:N as constrained)

each medical record belongs to one patient.

updates (Doctors ↔ Medical_Records, M:N via Doctor_Medical_Records)

doctors can update multiple records; a record can be updated by multiple doctors.

relates_to (Diagnosis ↔ Medical_Records, M:N via Diagnosis_Medical_Records)

diagnoses can relate to multiple records, a record can include multiple diagnoses.

linked_to (Prescriptions ↔ Medical_Records, M:N via Prescription_Medical_Records)

prescriptions can be linked to multiple records, a record can include multiple prescriptions, each with its own dosage/frequency/duration/notes for that record.

contains_symptoms (Medical_Records ↔ Symptoms, M:N via Medical_Record_Symptoms)

a record can list multiple symptoms, each with its own severity.

contains_allergies (Medical_Records ↔ Allergies, M:N via Medical_Record_Allergies)

a record can list multiple allergies, each with its own reaction and severity.

includes_procedures (Medical_Records ↔ Procedures, M:N via Medical_Record_Procedures)

a record can reference multiple procedures.

includes_lab_results (Medical_Records ↔ Lab_Results, M:N via Medical_Record_Lab_Results)

a record can reference multiple lab results.

generates_referral (Medical_Records ↔ Referrals, 1:N)

a record can generate multiple referrals, each referral is based on one record.

issues (Doctors ↔ Referrals as from_doctor_id, 1:N)

a doctor can issue many referrals.

receives_referral (Doctors ↔ Referrals as to_doctor_id, 1:N)

a doctor can receive many referrals.

creates (Doctors ↔ Medical_Report, 1:N)

a doctor can create multiple reports, each report is created by one doctor.

generates_report (Medical_Records ↔ Medical_Report, 1:N)

a record can generate multiple reports, each report is based on one record.

includes_lab_results_report (Medical_Report ↔ Lab_Results, M:N via Medical_Report_Lab_Results)

a report can include multiple lab results, a result can appear in multiple reports.

processes (Admin ↔ Billing, 1:N)

an admin can process multiple bills, each bill is processed by one admin.

generates_bill (Medical_Records ↔ Billing, 1:N)

a record can generate multiple bills, each bill is tied to one record.