Changes between Version 8 and Version 9 of RelationalModel

Timestamp:

09/15/26 23:05:15 (10 days ago)

Author:

231039

Comment:

--

RelationalModel

@@ -233 +233 @@
 
 === !TreatmentAttribute and !TreatmentAttributeValue (Generic Modeling) ===
-This segment uses the Entity-Attribute-Value (EAV) generic modeling technique, introduced because different treatment types have structurally different properties. Rather than creating a separate table per treatment type (a **Vaccination** table, an **Operation** table, and a **Consultation** table) with hardcoded columns, EAV allows each **!TreatmentType** to define its own set of attributes dynamically. **!TreatmentAttribute** stores the attribute name (e.g. “vacc_name”, “anesthesia”, “date_checkup”) and a “data_type” field indicating what kind of value it holds (e.g. varchar, int, date). **!TreatmentAttributeValue** then stores the actual value for a given **Treatment** instance, always as a varchar regardless of the logical type.
+This segment uses the Entity-Attribute-Value (EAV) generic modeling technique, introduced because different treatment types have structurally different properties. Rather than creating a separate table per treatment type (a **Vaccination** table, an **Operation**, **Consultation** and a **Prescription** table) with hardcoded columns, EAV allows each **!TreatmentType** to define its own set of attributes dynamically. **!TreatmentAttribute** stores the attribute name (e.g. “vacc_name”, “anesthesia”, “date_checkup”) and a “data_type” field indicating what kind of value it holds (e.g. varchar, int, date). **!TreatmentAttributeValue** then stores the actual value for a given **Treatment** instance, always as a varchar regardless of the logical type.
 
 The “data_type” column is not enforced at the database level, it serves as metadata for the application layer. When the application retrieves a value, it reads the corresponding “data_type” and performs the appropriate cast before using the value (e.g. parsing "2" as an integer for “num_dose”, or "2026-01-01" as a date for “date_next”). This is an accepted tradeoff of the EAV pattern: flexibility and extensibility are gained (new treatment types with new attributes can be added without any schema changes), at the cost of delegating type enforcement and parsing to the application rather than the database.
@@ -241 +241 @@
 
 === !InvoiceItem ===
-An **!InvoiceItem** represents a single billable line on an **Invoice**, and in !PawCare two categories of things can be billed: shop items and treatments. For treatments, ''Vaccinations'' and ''Operations'' are linked via the **Treatment** table to an **!InvoiceItem**. ''Consultations'', including those that result in a prescription, are also billed as **!InvoiceItem** entries through the same **Treatment** - **!TreatmentType** path, since a prescription is considered an outcome of a consultation rather than a standalone billable category.
+An **!InvoiceItem** represents a single billable line on an **Invoice**, and in !PawCare two categories of things can be billed: shop items and treatments. For treatments, each of the four types (''Vaccinations'', ''Operations'', ''Consultations'' and ''Prescriptions'') are linked via the **Treatment** table to an **!InvoiceItem**.
 
 The same structure applies to shop items: a **!ShopItem** sold through the clinic (e.g. medicine, accessories, grooming products) is billed as an **!InvoiceItem** referencing the **!ShopItem** table, with quantity and price recorded on the line. **!ShopItem** follows an analogous generic attribute pattern to treatments: **!ShopItemAttribute** and **!ShopItemAttributeValue** allow different product categories (e.g. “food” vs. “accessories”) to carry different properties without requiring separate tables.
@@ -248 +248 @@
 
 === Employee Hierarchy and Examination Assignment ===
-**Employee** contains a nullable “upervised_by” foreign key that references another row in the same table, modeling a supervisory hierarchy. In !PawCare's business logic, vet assistants each have exactly one supervising vet (“supervised_by” holds that vet's “id”), while vets have no supervisor (“supervised_by” is NULL). This self-referencing relationship also implies that a vet can supervise multiple assistants.
+**Employee** contains a nullable “supervised_by” foreign key that references another row in the same table, modeling a supervisory hierarchy. In !PawCare's business logic, vet assistants each have exactly one supervising vet (“supervised_by” holds that vet's “id”), while vets have no supervisor (“supervised_by” is NULL). This self-referencing relationship also implies that a vet can supervise multiple assistants.
 
 On the **Examination** table, the foreign key referencing the **Employee** table records which vet is responsible for the examination. Only the vet is stored directly on the examination, assistant involvement is not redundantly recorded here, since any assistants working with that vet can be derived through the “supervised_by” relationship in **Employee**. This is a deliberate design decision to avoid storing unnecessary data about all employees present, keeping the examination record focused on the responsible clinician.
@@ -259 +259 @@
 
 === Employee and Role ===
-Each **Employee** is assigned exactly one **Role**, modeled as a many-to-one relationship from **Employee** to **Role**. This reflects the business rule that an employee holds a single, distinct role within the clinic at any given time: a person is either a vet, a vet assistant, or a receptionist, and cannot hold multiple roles simultaneously. The **Role** table is kept separate rather than using a plain string column on **Employee** to allow for consistent role definitions across the system and easier extension if new roles need to be added in the future.
+Each **Employee** is assigned exactly one **Role**, modeled as a many-to-one relationship from **Employee** to **Role**. This reflects the business rule that an employee holds a single, distinct role within the clinic at any given time: a person is either a manager, vet, vet assistant, or receptionist, and cannot hold multiple roles simultaneously. The **Role** table is kept separate rather than using a plain string column on **Employee** to allow for consistent role definitions across the system and easier extension if new roles need to be added in the future.