Changes between Version 8 and Version 9 of ERModel

Timestamp:

09/14/26 21:45:47 (22 hours ago)

Author:

236021

Comment:

--

ERModel

@@ -1 @@
-= Conceptual Model =
-
-== ER Diagram ==
+
+= ER Diagram =
+
+== Diagram ==
 
 
@@ -7 +8 @@
 
 ----
-= Data Requirements  =
-
-
-== Entities ==
+== Data Requirements  ==
+
+=== Entities ===
 
 === Doctor_Level ===
-* level_id (PK, bigint)
-* level (text)
+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 ===
-* specialization_id (PK, bigint)
-* specialization_name (text)
+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 ===
-* department_id (PK, bigint)
-* department_name (text)
+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 ===
-* doctor_id (PK, bigint)
-* first_name (text)
-* last_name (text)
-* email_address (text)
-* level_id (FK, bigint)
-* specialization_id (FK, bigint)
-* department_id (FK, bigint)
+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 ===
-* patient_id (PK, bigint)
-* first_name (text)
-* last_name (text)
-* email_address (text)
-* date_of_birth (date)
-* blood_type (text)
-* gender (text)
-* phone_number (text)
-* embg (text)
+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 ===
-* admin_id (PK, bigint)
-* username (text)
-* name (text)
-* lastname (text)
-* email (text)
+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 ===
-* technician_id (PK, bigint)
-* username (text)
-* name (text)
-* lastname (text)
-* email (text)
+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 ===
-* user_id (PK, serial)
-* username (text)
-* password (text)
-* role (text)
-* first_name (text)
-* last_name (text)
-* patient_id (FK, bigint, nullable)
-* doctor_id (FK, bigint, nullable)
-* admin_id (FK, bigint, nullable)
-* technician_id (FK, bigint, nullable)
-* is_active (boolean)
+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 ===
-* appointment_id (PK, bigint)
-* appointment_date (date)
-* appointment_time (time)
-* status (text)
-* patient_id (FK, bigint)
-* doctor_id (FK, bigint)
+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_id (PK, bigint)
-* name (text)
-* description (text)
-* patient_id (FK, bigint)
-* doctor_id (FK, bigint)
+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 ===
-* procedure_id (PK, bigint)
-* procedure_type (text)
-* procedure_date (date)
-* description (text)
-* cost (decimal)
-* doctor_id (FK, bigint)
-* diagnosis_id (FK, bigint)
+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 ===
-* result_id (PK, bigint)
-* result_description (text)
-* result_date (date)
-* procedure_id (FK, bigint)
+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 ===
-* prescription_id (PK, bigint)
-* medication_name (text)
+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 ===
-* restriction_id (PK, bigint)
-* description (text)
-* prescription_id (FK, bigint)
+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 ===
-* test_id (PK, bigint)
-* test_name (text)
-* description (text)
-* cost (decimal)
+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 ===
-* result_id (PK, bigint)
-* results (text)
-* result_date (date)
-* test_id (FK, bigint)
+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 ===
-''(simplified)''
-* record_id (PK, bigint)
-* patient_id (FK, bigint)
+
+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 ===
-* allergy_id (PK, bigint)
-* name (text)
-* allergy_severity (text)
+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 ===
-* symptom_id (PK, bigint)
-* name (text)
-* description (text)
+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 ===
-* referral_id (PK, bigint)
-* reason (text)
-* referral_date (date)
-* record_id (FK, bigint)
-* from_doctor_id (FK, bigint)
-* to_doctor_id (FK, bigint)
+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 ===
-* report_id (PK, bigint)
-* description (text)
-* report_date (date)
-* record_id (FK, bigint)
-* doctor_id (FK, bigint)
+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 ===
-* bill_id (PK, bigint)
-* total_cost (decimal)
-* payment_status (text)
-* payment_date (date)
-* record_id (FK, bigint)
-* admin_id (FK, bigint)
+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 ===
-* performed_id (PK, bigint)
-* procedure_id (FK, bigint)
-* doctor_id (FK, bigint)
-* patient_id (FK, bigint)
-* diagnosis_id (FK, bigint, nullable)
-* procedure_date (date)
-* notes (text)
+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 ===
-
-* performed_test_id (PK, bigint)
-* test_id (FK, bigint)
-* patient_id (FK, bigint)
-* doctor_id (FK, bigint)
-* technician_id (FK, bigint)
-* test_date (date)
-* notes (text)
+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 ====
-* prescription_id (FK, bigint)
-* record_id (FK, bigint)
-* dosage (text)
-* frequency (text)
-* duration (text)
-* notes (text)
+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 ====
-* record_id (FK, bigint)
-* symptom_id (FK, bigint)
-* severity (text)
+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 ====
-* record_id (FK, bigint)
-* allergy_id (FK, bigint)
-* reaction (text)
-* severity (text)
+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 ==
+=== Relations ===
 
 === is (Doctor_Level ↔ Doctors, 1:N) ===
@@ -332 +700 @@
 === generates_bill (Medical_Records ↔ Billing, 1:N) ===
 a record can generate multiple bills, each bill is tied to one record.
+