= ER Diagram = == Diagram == [[Image("ERModel_v03.png", width=1400)]] ---- == 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.