Relational Model

Relational Diagram

Entities

1. Owner – entity that stores information about pet owners.
   • owner_id (serial, primary key)
   • last_name (varchar, not null)
   • first_name (varchar, not null)
   • phone (varchar)
   • email (varchar, not null, unique)
   • address (varchar)
   • gender (varchar, check: 'M' or 'F')

2. Pet – entity that represents a pet registered at the clinic. The age_display field is a generated column that automatically computes a human-readable age based on age.
   • pet_id (serial, primary key)
   • name (varchar, not null)
   • is_active (boolean, not null, default: true)
   • type (varchar, check: 'mammal', 'bird', 'fish', 'amphibian', 'reptile')
   • breed (varchar)
   • age (integer, check: >= 0)
   • medical_history (text)
   • owner_id (foreign key -> owner.owner_id, not null)
   • age_display (text, generated always as ... stored)

3. Role – entity that defines employee roles at the clinic (e.g. veterinarian, assistant, receptionist).
   • role_id (serial, primary key)
   • name (varchar, not null)
   • description (varchar)

4. Employee – entity that represents a clinic employee. Employee has a self-reference (supervised_by) used to model a hierarchy between employees (e.g. an assistant supervised by a veterinarian).
   • employee_id (serial, primary key)
   • last_name (varchar, not null)
   • first_name (varchar, not null)
   • phone (varchar)
   • email (varchar, not null, unique)
   • address (varchar)
   • date_employment (date, default: current_date)
   • experience (integer, default: 0, check: >= 0)
   • role_id (foreign key -> role.role_id)
   • supervised_by (foreign key -> employee.employee_id, self-reference)
   • gender (varchar, check: 'M' or 'F')

5. Appointment – entity that represents the initial request/booking made by the owner (when and why the visit was requested). The actual scheduled slot, with an assigned employee and room, is stored separately in Examination.
   • appointment_id (serial, primary key)
   • date_appointment (date, not null)
   • reason (varchar, not null)
   • phone (varchar)
   • owner_id (foreign key -> owner.owner_id)
   • pet_id (foreign key -> pet.pet_id)

6. Specialization – entity that stores employees' specializations (one employee can have multiple specializations).
   • specialization_id (serial, primary key)
   • specialization (varchar, not null)
   • license_number (varchar, not null)
   • employee_id (foreign key -> employee.employee_id, not null)

7. Certificate – entity that stores employees' certificates.
   • certificate_id (serial, primary key)
   • certificate_name (varchar, not null)
   • employee_id (foreign key -> employee.employee_id, not null)
   • category (varchar)

8. Examination_room – entity that represents a room at the clinic (examination room, surgery room, laboratory, etc.).
   • examination_room_id (serial, primary key)
   • room_number (varchar, not null)
   • type (varchar, not null, check: 'examination', 'surgery', 'radiology', 'laboratory', 'isolation ward', 'patient ward')
   • capacity (integer, check: > 0)
   • status (varchar, default: 'available', check: 'available' or 'unavailable')

9. Examination – entity that represents the concrete scheduled slot: when it takes place, which employee handles it, in which room, and its current status. It is linked to Appointment (the initial request) and is the entity that all treatments (Treatment) attach to.
   • examination_id (serial, primary key)
   • date_examination (date, not null, default: current_date)
   • status (varchar, not null, default: 'scheduled', check: 'scheduled', 'completed', 'cancelled')
   • description (text)
   • appointment_id (foreign key -> appointment.appointment_id)
   • employee_id (foreign key -> employee.employee_id)
   • examination_room_id (foreign key -> examination_room.examination_room_id)

10. Coupon – entity that represents a discount coupon that can be applied to an entire invoice.
    • coupon_id (serial, primary key)
    • code (varchar, not null, unique)
    • type (varchar, not null, check: 'fixed' or 'percentage')
    • value (numeric, not null, check: >= 0)
    • valid_from (date, not null, default: current_date)
    • valid_to (date, not null)
    • usage_limit (integer, not null, default: 1)
    • usage_count (integer, not null, default: 0)
    • is_active (boolean, not null, default: true)
    • min_total (numeric, default: 0, check: >= 0)

11. Shop_item_category – entity that categorizes products in the shop. It has a self-reference (parent_id) to allow a hierarchy of categories and subcategories.
    • shop_item_category_id (serial, primary key)
    • name (varchar, not null)
    • parent_id (foreign key -> shop_item_category.shop_item_category_id, self-reference)

12. Shop_item – entity that represents a product sold in the clinic's shop (e.g. medicine, food, equipment).
    • shop_item_id (serial, primary key)
    • name (varchar, not null)
    • price (numeric, not null, check: >= 0)
    • stock (integer, default: 0, check: >= 0)
    • shop_item_category_id (foreign key -> shop_item_category.shop_item_category_id)

13. Shop_item_attribute – entity that defines which attributes are relevant for products in a given category (e.g. "weight" for food, "expiration date" for medicine). It is split into its own entity to enable an EAV (Entity-Attribute-Value) pattern, since different product categories have different sets of attributes and it isn't practical for every attribute to be its own column on Shop_item.
    • shop_item_attribute_id (serial, primary key)
    • name (varchar, not null)
    • data_type (varchar, not null, check: 'text', 'integer', 'decimal', 'boolean', 'date')
    • shop_item_category_id (foreign key -> shop_item_category.shop_item_category_id, not null)

14. Shop_item_attribute_value – entity that stores the actual value of one attribute for one specific product (EAV pattern).
    • shop_item_attribute_value_id (serial, primary key)
    • value (varchar, not null)
    • notes (varchar)
    • shop_item_attribute_id (foreign key -> shop_item_attribute.shop_item_attribute_id, not null)
    • shop_item_id (foreign key -> shop_item.shop_item_id, not null)

15. Medicine – entity that represents a medicine. It is linked to Shop_item because medicines are also physical products sold/dispensed through the shop.
    • medicine_id (serial, primary key)
    • name (varchar, not null)
    • manufacturer (varchar)
    • description (varchar)
    • shop_item_id (foreign key -> shop_item.shop_item_id)

16. Prescription – entity that represents a prescription issued during a given examination. examination_id is UNIQUE, since an examination can have at most one prescription.
    • prescription_id (serial, primary key)
    • examination_id (foreign key -> examination.examination_id, not null, unique)
    • date_start (date, not null)
    • date_end (date, not null)
    • description (text)

17. Prescription_medicine – entity that links prescriptions and medicines (N:M), with additional attributes specific to that combination (dosage, number of days).
    • prescription_id (foreign key -> prescription.prescription_id, primary key)
    • medicine_id (foreign key -> medicine.medicine_id, primary key)
    • dosage (integer, not null, check: > 0)
    • num_days (integer, not null, check: > 0)

18. Treatment_type – entity that defines the types of treatments the clinic offers (consultation, prescription, vaccination, operation).
    • treatment_type_id (serial, primary key)
    • name (varchar, not null, check: 'prescription', 'vaccination', 'consultation', 'operation')

19. Treatment – entity that represents a specific treatment carried out as part of one examination. One Examination can include multiple treatments (e.g. a consultation followed by a vaccination in the same visit).
    • treatment_id (serial, primary key)
    • date_treatment (date, not null, default: current_date)
    • notes (text)
    • treatment_type_id (foreign key -> treatment_type.treatment_type_id)
    • examination_id (foreign key -> examination.examination_id)

20. Treatment_attribute – entity that defines which attributes are relevant for a given treatment type (e.g. "date_next" and "adverse_reaction" for vaccination, "duration_minutes" for operation). It is split into its own entity to enable an EAV pattern, since each treatment type has a different set of attributes.
    • treatment_attribute_id (serial, primary key)
    • name (varchar, not null)
    • data_type (varchar, not null, check: 'text', 'integer', 'decimal', 'boolean', 'date')
    • treatment_type_id (foreign key -> treatment_type.treatment_type_id, not null)

21. Treatment_attribute_value – entity that stores the actual value of one attribute for one specific treatment (EAV pattern).
    • treatment_attribute_value_id (serial, primary key)
    • value (varchar, not null)
    • notes (varchar)
    • treatment_attribute_id (foreign key -> treatment_attribute.treatment_attribute_id, not null)
    • treatment_id (foreign key -> treatment.treatment_id, not null)

22. Discount – entity that represents a discount on a specific shop product.
    • discount_id (serial, primary key)
    • type (varchar, not null, check: 'fixed' or 'percentage')
    • value (numeric, not null, check: >= 0)
    • description (varchar)
    • date_from (date, default: current_date)
    • date_to (date)
    • shop_item_id (foreign key -> shop_item.shop_item_id)

23. Invoice – entity that represents an invoice issued to an owner, with an optional coupon applied to it.
    • invoice_id (serial, primary key)
    • date_invoice (date, not null, default: current_date)
    • total (numeric, not null, check: >= 0)
    • coupon_id (foreign key -> coupon.coupon_id)
    • owner_id (foreign key -> owner.owner_id)

24. Invoice_item – entity that represents a line item on an invoice. The type field determines whether the line item is a shop product or a treatment, and the check constraint ensures that exactly one of shop_item_id/treatment_id is populated, depending on the type.
    • num_item (integer, not null, check: > 0, part of composite primary key)
    • invoice_id (foreign key -> invoice.invoice_id, not null, part of composite primary key)
    • price (numeric, not null, check: >= 0)
    • quantity (integer, not null, default: 1, check: >= 0)
    • type (varchar, check: 'shop_item' or 'treatment')
    • shop_item_id (foreign key -> shop_item.shop_item_id)
    • treatment_id (foreign key -> treatment.treatment_id)

25. Payment – entity that represents a payment made toward a given invoice. One invoice can be paid through multiple payments (e.g. partial payments).
    • payment_id (serial, primary key)
    • date_payment (date, not null, default: current_date)
    • amount (numeric, not null, check: >= 0)
    • method (varchar, not null, check: 'cash', 'debit card', 'credit card', 'digital wallet', 'other')
    • invoice_id (foreign key -> invoice.invoice_id, not null)

Relations

1. Owns (Owner : Pet, 1:N) - One owner can have multiple pets, while each pet belongs to exactly one owner.
2. Requests (Owner : Appointment, 1:N) - One owner can request multiple appointments, while each appointment is requested by at most one owner.
3. Concerns (Pet : Appointment, 1:N) - One pet can have multiple appointments over time, while each appointment concerns at most one pet.
4. Has_role (Role : Employee, 1:N) - One role can be held by multiple employees, while each employee has at most one role.
5. Supervises (Employee : Employee, 1:N) - One employee (supervisor) can supervise multiple other employees, while each employee is supervised by at most one other employee (self-reference).
6. Has_specialization (Employee : Specialization, 1:N) - One employee can have multiple specializations, while each specialization belongs to exactly one employee.
7. Has_certificate (Employee : Certificate, 1:N) - One employee can have multiple certificates, while each certificate belongs to exactly one employee.
8. Results_in (Appointment : Examination, 1:N) - One appointment results in one or more examinations, while each examination originates from at most one appointment.
9. Conducts (Employee : Examination, 1:N) - One employee can conduct multiple examinations, while each examination is conducted by at most one employee.
10. Held_in (Examination_room : Examination, 1:N) - Multiple examinations can be held in one room over time, while each examination takes place in at most one room.
11. Includes (Examination : Treatment, 1:N) - One examination can include multiple treatments (e.g. a consultation and a vaccination in the same visit), while each treatment belongs to at most one examination.
12. Classified_as (Treatment_type : Treatment, 1:N) - One treatment type covers multiple specific treatments, while each treatment belongs to exactly one type.
13. Defines_attribute (Treatment_type : Treatment_attribute, 1:N) - One treatment type defines multiple specific attributes, while each attribute belongs to exactly one treatment type.
14. Has_value (Treatment_attribute : Treatment_attribute_value, 1:N) - One attribute can have multiple recorded values (for different treatments), while each value refers to exactly one attribute.
15. Has_attribute_value (Treatment : Treatment_attribute_value, 1:N) - One treatment can have multiple attribute values, while each value belongs to exactly one treatment.
16. Has_prescription (Examination : Prescription, 1:1) - One examination can have at most one prescription, and each prescription belongs to exactly one examination.
17. Contains_medicine / Prescribed_in (Prescription : Medicine, N:M via Prescription_medicine) - One prescription can contain multiple medicines, and one medicine can appear in multiple prescriptions; the relationship is modeled through the associative entity Prescription_medicine, which additionally stores the dosage and number of days.
18. Sold_as (Shop_item : Medicine, 1:N) - One shop product conceptually corresponds to at most one medicine, but since shop_item_id is not UNIQUE-constrained in the model, the formal relationship is 1:N.
19. Belongs_to_category (Shop_item_category : Shop_item, 1:N) - One category contains multiple products, while each product belongs to at most one category.
20. Has_subcategory (Shop_item_category : Shop_item_category, 1:N) - One category can have multiple subcategories, while each category has at most one parent category (self-reference).
21. Defines_item_attribute (Shop_item_category : Shop_item_attribute, 1:N) - One product category defines multiple specific attributes, while each attribute belongs to exactly one category.
22. Has_item_attribute_value (Shop_item_attribute : Shop_item_attribute_value, 1:N) - One attribute can have multiple recorded values (for different products), while each value refers to exactly one attribute.
23. Has_attribute_value_for_item (Shop_item : Shop_item_attribute_value, 1:N) - One product can have multiple attribute values, while each value belongs to exactly one product.
24. Applies_to (Shop_item : Discount, 1:N) - One product can have multiple discounts over time, while each discount applies to at most one product.
25. Used_in (Coupon : Invoice, 1:N) - One coupon can be used on multiple invoices (up to its defined usage_limit), while each invoice uses at most one coupon.
26. Issued_to (Owner : Invoice, 1:N) - One owner can be issued multiple invoices, while each invoice is issued to at most one owner.
27. Contains_item (Invoice : Invoice_item, 1:N) - One invoice contains multiple line items, while each line item belongs to exactly one invoice.
28. Referenced_as_item (Shop_item : Invoice_item, 1:N) - One product can appear as a line item on multiple invoices, while each line item of type 'shop_item' refers to exactly one product.
29. Referenced_as_treatment (Treatment : Invoice_item, 1:N) - One treatment can appear as a line item on multiple invoices, while each line item of type 'treatment' refers to exactly one treatment.
30. Paid_through (Invoice : Payment, 1:N) - One invoice can be paid through multiple payments (e.g. partial payments), while each payment belongs to exactly one invoice.

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 four treatment types: Vaccination, Operation, Consultation and Prescription.

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, 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.

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, 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.

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 “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.

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 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.