Changes between Version 6 and Version 7 of RelationalModel

Timestamp:

09/14/26 12:54:39 (11 days ago)

Author:

231039

Comment:

--

RelationalModel

@@ -4 +4 @@
 
 [[Image(RelationalModel-PawCare.svg)]]
+
+== 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 ==