Relation Design

Notation

• PK marked with [PK]
• FK marked with [FK→Table(column)]
• UQ = unique constraint

Relation schema

USERS + SUBTYPES

• Users(user_id [PK], username [UQ], email [UQ], name, surname, created_at)
• Admin(user_id [PK, FK→Users(user_id)])
• Client(user_id [PK, FK→Users(user_id)])
• Owner(user_id [PK, FK→Client(user_id)])
• VetClinic(clinic_id[PK],user_id [FK→Users(user_id)])

---

NOTIFICATIONS

• Notification( notification_id [PK], user_id [FK→Users(user_id)], type, message, is_read, created_at)

---

VET CLINIC APPLICATIONS

• VetClinicApplication( application_id [PK], clinic_id [FK→VetClinic(clinic_id)], name, email, phone, city, address, submitted_at, status, reviewed_at, reviewed_by [FK→Admin(user_id)], denial_reason)

---

VET CLINICS

• VetClinic( clinic_id [PK], user_id [FK→User(user_id), UQ], application_id [FK→VetClinicApplication(application_id), UQ], name, email, phone, city, address, location, work_days, start_time, end_time)

---

ANIMALS

• Animal( animal_id [PK], owner_id [FK→Owner(user_id)], name, sex, date_of_birth, photo_url, type, species, breed, located_name)

---

LISTINGS

• Listing( listing_id [PK], owner_id [FK→Owner(user_id)], animal_id [FK→Animal(animal_id)], status, price, description, created_at)
• FavoriteListing( user_id [FK→Users(user_id)], listing_id [FK→Listing(listing_id)], [PK](user_id, listing_id))

---

APPOINTMENTS

• Appointment( appointment_id [PK], clinic_id [FK→VetClinic(clinic_id)], animal_id [FK→Animal(animal_id)], responsible_owner_id [FK→Owner(user_id)], status, date_time, notes)

---

REVIEWS: SUPERTYPE + SUBTYPES

• Review( review_id [PK], reviewer_id [FK→Client(user_id)], rating, comment, created_at, updated_at, is_deleted)
• UserReview( review_id [PK, FK→Review(review_id)], target_user_id [FK→Users(user_id)], interaction_type)
• ClinicReview( review_id [PK, FK -> Review(review_id)], target_clinic_id [FK -> VetClinic(clinic_id)], appointment_id [FK -> Appointment(appointment_id)])

---

HEALTH RECORDS

• HealthRecord( healthrecord_id [PK], animal_id [FK→Animal(animal_id)], appointment_id [FK→Appointment(appointment_id)], type, description, date)

---

CLINIC UNAVAILABLE SLOTS

• clinic_unavailable_slots( slot_id [PK], clinic_id [FK→VetClinic(clinic_id)], date_time, reason, created_at)

---

Business Rules Enforced in DDL / Triggers

Review.rating must be in range 1–5.
Review specialization is disjoint:one Review can be either UserReview or ClinicReview, not both.
A Client can review a clinic only if they have at least one Appointment at that clinic with status = 'DONE'.
HealthRecord consistency:HealthRecord.animal_id must match Appointment.animal_id.
FavoriteListing has composite primary key:(user_id, listing_id).

DDL

• schema_creation.sql​

DML

• data_load.sql​

Relational diagram