Relational Model

Relational Diagram

Descriptive Documentation and Argumentation

TreatmentType and Treatment

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.

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.

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.

Coupon and Discount

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.

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.

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.

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.

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.

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.

Prescription_Medicine

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.

Invoice and Payment

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.

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.