Changes between Version 4 and Version 5 of RelationalModel


Ignore:
Timestamp:
04/19/26 23:46:09 (13 days ago)
Author:
231039
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • RelationalModel

    v4 v5  
    88
    99=== !TreatmentType and Treatment ===
    10 '''!TreatmentType''' acts as a catalogue of the kinds of treatments the clinic offers, while **Treatment** represents an actual instance of a treatment performed during a specific **Examination**. The two are separated to avoid redundancy: the type-level information (“name”, “category”) lives in **TreatmentType**, while instance-level data (which examination, which employee, notes) lives in **Treatment**. The system supports three treatment types: ''Vaccination'', ''Operation'', and ''Consultation''. Examinations that result in neither a vaccination nor an operation, including those that result only in a prescription, are classified as ''Consultation'', since a prescription is an outcome of a consultation rather than a distinct treatment category.
     10'''!TreatmentType''' acts as a catalogue of the kinds of treatments the clinic offers, while **Treatment** represents an actual instance of a treatment performed during a specific **Examination**. The two are separated to avoid redundancy: the type-level information (“name”, “category”) lives in **!TreatmentType**, while instance-level data (which examination, which employee, notes) lives in **Treatment**. The system supports three treatment types: ''Vaccination'', ''Operation'', and ''Consultation''. Examinations that result in neither a vaccination nor an operation, including those that result only in a prescription, are classified as ''Consultation'', since a prescription is an outcome of a consultation rather than a distinct treatment category.
    1111
    1212=== !TreatmentAttribute and !TreatmentAttributeValue (Generic Modeling) ===
    13 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.
     13This 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.
    1414
    1515The “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.
    … …  
    1818**Coupon** and Discount serve distinct purposes in the billing model. A **Discount** is an optional reduction applied directly to an individual **ShopItem**. A **Coupon** is a promotional code applied at the **Invoice** level, carrying validation rules such as validity dates (“valid_from”, “valid_to”), a minimum order total (“min_total”), usage limits (“usage_limit”), and an “is_active” status flag. This separation reflects two different business scenarios: a staff member marking down a specific item with a discount versus a customer applying a promotional code to their whole invoice.
    1919
    20 === InvoiceItem ===
    21 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.
     20=== !InvoiceItem ===
     21An **!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.
    2222
    23 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.
     23The 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.
    2424
    25 **Medicine** exists independently of **ShopItem** because medicines are used in prescriptions regardless of whether the clinic sells them. The two are linked via an optional one-to-one relationship: a **Medicine** record may carry a foreign key to a **ShopItem**, indicating that this particular medicine is also available for purchase at the shop (under the category "medicine"). Both sides are optional: not every medicine is sold in the shop, and not every shop item is a medicine. When the link exists, it connects the clinical record of a medicine to its corresponding shop listing.
     25**Medicine** exists independently of **!ShopItem** because medicines are used in prescriptions regardless of whether the clinic sells them. The two are linked via an optional one-to-one relationship: a **Medicine** record may carry a foreign key to a **!ShopItem**, indicating that this particular medicine is also available for purchase at the shop (under the category "medicine"). Both sides are optional: not every medicine is sold in the shop, and not every shop item is a medicine. When the link exists, it connects the clinical record of a medicine to its corresponding shop listing.
    2626
    2727=== Employee Hierarchy and Examination Assignment ===
    28 **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.
     28**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.
    2929
    3030On 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.