| 11 | | * model segment xyz - how and why you have modeled that segment of the model in that specific way |
| 12 | | * … |
| | 12 | === !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. |
| | 14 | |
| | 15 | 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. |
| | 16 | |
| | 17 | === Coupon and Discount === |
| | 18 | **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. |
| | 19 | |
| | 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. |
| | 22 | |
| | 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. |
| | 24 | |
| | 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. |
| | 26 | |
| | 27 | === 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. |
| | 29 | |
| | 30 | 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. |
| | 31 | |
| | 32 | === Prescription_Medicine === |
| | 33 | **Prescription_Medicine** is an associative table that exists as a consequence of the many-to-many relationship between **Prescription** and **Medicine**: a prescription can include multiple medicines, and the same medicine can appear across multiple prescriptions. The table carries its own attributes (“dosage”, “num_days”) because these values are specific to each medicine within a particular prescription and cannot belong to either **Prescription** or **Medicine** alone. |
| | 34 | |
| | 35 | === Invoice and Payment === |
| | 36 | The relationship between **Invoice** and **Payment** is one-to-many, meaning a single invoice can be settled through multiple payments. This reflects the business decision to allow clients to pay off their invoice in installments or partial payments rather than requiring full payment at once (e.g. for an operation). Each **Payment** record stores the “amount”, “method”, and “date”, allowing the application to track the running total paid against an invoice at any point. |
| | 37 | |
| | 38 | === Employee and Role === |
| | 39 | 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. |
