= 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
[[BR]] Review.rating must be in range 1–5.
[[BR]] Review specialization is disjoint:one Review can be either UserReview or ClinicReview, not both.
[[BR]] A Client can review a clinic only if they have at least one Appointment at that clinic with status = 'DONE'.
[[BR]] HealthRecord consistency:HealthRecord.animal_id must match Appointment.animal_id.
[[BR]] FavoriteListing has composite primary key:(user_id, listing_id).
== DDL
 * [attachment:schema_creation.sql]
== DML
* [attachment:data_load.sql]
== Relational diagram
[[Image(relational_schema.jpg)]]