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, password_hash, created_at

)

• Admin( user_id [PK, FK→Users(user_id)]

)

• Client( user_id [PK, FK→Users(user_id)], is_blocked, blocked_at, blocked_reason, blocked_by [FK→Admin(user_id)]

)

• Owner( user_id [PK, FK→Client(user_id)]

)

• VetClinic( clinic_id [PK], user_id [FK→Users(user_id), UQ]

)

VetClinic is also a specialization of Users. It keeps its own clinic_id as the primary key, while user_id connects it to the Users supertype.

---

CLIENT INTERACTIONS

• ClientInteraction( source_client_id [PK, FK→Client(user_id)], target_client_id [PK, FK→Client(user_id)],

)

This represents the recursive Client–Client relationship interaction_with.

---

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), UQ], 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→Users(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( fav_listing_id [PK], user_id [FK→Users(user_id)], listing_id [FK→Listing(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

• ClinicUnavailableSlot( 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'.

A Client can leave a UserReview only for another Client/User with whom they have an existing ClientInteraction.

ClientInteraction is recursive: both source_client_id and target_client_id reference Client(user_id).

A Client cannot have an interaction with themselves.

HealthRecord consistency: HealthRecord.animal_id must match Appointment.animal_id.

UserReview.interaction_type must be one of the allowed interaction types defined in the DDL.

DDL

• schema_creation.sql​

DML

• data_load.sql​

Relational diagram