Changes between Version 14 and Version 15 of ERModel
- Timestamp:
- 09/22/26 10:53:38 (26 hours ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
ERModel
v14 v15 63 63 64 64 === Doctors === 65 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. 65 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. A Doctors row optionally carries a `user_id` FK back to Users, linking the doctor to their login account. 66 66 67 67 * '''Candidate keys:''' … … 89 89 department_id (FK, bigint) — required, references Departments 90 90 91 user_id (FK, bigint, UNIQUE) — required, references Users 92 91 93 === Patients === 92 Patients represents the people receiving care, it is the anchor entity for almost all clinical data like appointments, diagnoses, medical records, tests, procedures, billing. 94 Patients represents the people receiving care, it is the anchor entity for almost all clinical data like appointments, diagnoses, medical records, tests, procedures, billing. A Patients row optionally carries a `user_id` FK back to Users, linking the patient to their login account. 93 95 94 96 * '''Candidate keys:''' … … 120 122 embg (text) — required, unique, 13-digit format 121 123 124 user_id (FK, bigint, UNIQUE) — required, references Users 125 122 126 === Admin === 123 Represents clinic administrative staff who manage billing and back-office operations. Kept separate from Doctors and Lab_Technician since admins have no clinical attributes. 127 Represents clinic administrative staff who manage billing and back-office operations. Kept separate from Doctors and Lab_Technician since admins have no clinical attributes. An Admin row optionally carries a `user_id` FK back to Users, linking the admin to their login account. 124 128 125 129 * '''Candidate keys:''' … … 145 149 email (text) — required, unique, must contain "@adminmedora" (`LIKE '%@adminmedora%'` - restricts admins to the internal admin email domain) 146 150 151 user_id (FK, bigint, UNIQUE) — required, references Users 152 147 153 === Lab_Technician === 148 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. 154 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. A Lab_Technician row optionally carries a `user_id` FK back to Users, linking the technician to their login account. 149 155 150 156 * '''Candidate keys:''' … … 168 174 email (text) — required, unique, must contain "@labmedora" (`LIKE '%@labmedora%'` - restricts technicians email domain) 169 175 176 user_id (FK, bigint, UNIQUE) — required, references Users 177 170 178 === Users === 171 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.179 Users is the authentication and login entity implementing role based access. Each of Patients, Doctors, Admin and Lab_Technician optionally carries a `user_id` FK back to a Users row, so a Users row is served by exactly one row among those four role tables, whichever one references it. 172 180 173 181 * '''Candidate keys:''' … … 192 200 193 201 last_name (varchar(100)) — optional 194 195 patient_id (FK, bigint, nullable) — references Patients196 197 doctor_id (FK, bigint, nullable) — references Doctors198 199 admin_id (FK, bigint, nullable) — references Admin200 201 technician_id (FK, bigint, nullable) — references Lab_Technician202 202 203 203 is_active (boolean) — optional, defaults to true … … 584 584 each doctor belongs to one department, a department employs many doctors. 585 585 586 === identifies_patient (Users ↔ Patients, 1:1) === 587 a Users row optionally links to one Patients row, when the account belongs to a patient. 588 589 === identifies_doctor (Users ↔ Doctors, 1:1) === 590 a Users row optionally links to one Doctors row, when the account belongs to a doctor. 591 592 === identifies_admin (Users ↔ Admin, 1:1) === 593 a Users row optionally links to one Admin row, when the account belongs to an administrator. 594 595 === identifies_lab_technician (Users ↔ Lab_Technician, 1:1) === 596 a Users row optionally links to one Lab_Technician row, when the account belongs to a lab technician. 597 598 Constraint for identifies relations: Exactly one of the four relationships above must be populated for a given Users row, depending on its role. 586 === identifies_patient (Patients ↔ Users, 1:1) === 587 a Patients row carries a user_id FK referencing exactly one Users row. 588 589 === identifies_doctor (Doctors ↔ Users, 1:1) === 590 a Doctors row carries a user_id FK referencing exactly one Users row. 591 592 === identifies_admin (Admin ↔ Users, 1:1) === 593 an Admin row carries a user_id FK referencing exactly one Users row. 594 595 === identifies_lab_technician (Lab_Technician ↔ Users, 1:1) === 596 a Lab_Technician row carries a user_id FK referencing exactly one Users row. 599 597 600 598 === has_allergies (Patients ↔ Allergies, M:N via Patient_Allergies) ===
