Logical and Physical Design, DB Creation (DDL)

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)]) (Owner is a subtype of Client, as in the diagram)

NOTIFICATIONS

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

VET CLINICS

• VetClinic(clinic_id [PK], name, email, phone, city, address, approved_by [FK→Admin(user_id)])

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)

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)
• UserReview(review_id [PK, FK→Review(review_id)], target_user_id [FK→Users(user_id)])
• ClinicReview(review_id [PK, FK→Review(review_id)], target_clinic_id [FK→VetClinic(clinic_id)])

HEALTH RECORDS

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

---

Business rules enforced in DDL:
Review rating range (1–5)
Disjoint review specialization (a Review can be either UserReview or ClinicReview, not both) via trigger
Clinic review constraint: reviewer must have at least one DONE appointment at that clinic (via trigger)
HealthRecord consistency: its animal_id must match appointment.animal_id (via trigger)

DDL

DROP TABLE IF EXISTS health_records CASCADE;
DROP TABLE IF EXISTS clinic_reviews CASCADE;
DROP TABLE IF EXISTS user_reviews CASCADE;
DROP TABLE IF EXISTS reviews CASCADE;
DROP TABLE IF EXISTS appointments CASCADE;
DROP TABLE IF EXISTS listings CASCADE;
DROP TABLE IF EXISTS animals CASCADE;
DROP TABLE IF EXISTS vet_clinics CASCADE;
DROP TABLE IF EXISTS notifications CASCADE;
DROP TABLE IF EXISTS owners CASCADE;
DROP TABLE IF EXISTS clients CASCADE;
DROP TABLE IF EXISTS admins CASCADE;
DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (
  user_id     BIGSERIAL PRIMARY KEY,
  username    VARCHAR(30)  NOT NULL,
  email       VARCHAR(254) NOT NULL,
  name        VARCHAR(60)  NOT NULL,
  surname     VARCHAR(60)  NOT NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
  CONSTRAINT uq_users_username UNIQUE (username),
  CONSTRAINT uq_users_email    UNIQUE (email)
);

CREATE TABLE admins (
  user_id BIGINT PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE clients (
  user_id BIGINT PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE owners (
  user_id BIGINT PRIMARY KEY REFERENCES clients(user_id) ON DELETE CASCADE
);

CREATE TABLE notifications (
  notification_id BIGSERIAL PRIMARY KEY,
  user_id         BIGINT       NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  type            VARCHAR(40)  NOT NULL,
  message         TEXT         NOT NULL,
  is_read         BOOLEAN      NOT NULL DEFAULT FALSE,
  created_at      TIMESTAMP    NOT NULL DEFAULT NOW()
);

CREATE TABLE vet_clinics (
  clinic_id   BIGSERIAL PRIMARY KEY,
  name        VARCHAR(120) NOT NULL,
  email       VARCHAR(254),
  phone       VARCHAR(40),
  city        VARCHAR(80)  NOT NULL,
  address     VARCHAR(200) NOT NULL,
  approved_by BIGINT REFERENCES admins(user_id) ON DELETE SET NULL
);

CREATE TABLE animals (
  animal_id     BIGSERIAL PRIMARY KEY,
  owner_id      BIGINT      NOT NULL REFERENCES owners(user_id) ON DELETE CASCADE,
  name          VARCHAR(80) NOT NULL,
  sex           VARCHAR(10) NOT NULL CHECK (sex IN ('MALE','FEMALE','UNKNOWN')),
  date_of_birth DATE,
  photo_url     TEXT,
  type          VARCHAR(40),
  species       VARCHAR(60),
  breed         VARCHAR(60),
  located_name  VARCHAR(120)
);

CREATE TABLE listings (
  listing_id   BIGSERIAL PRIMARY KEY,
  owner_id     BIGINT      NOT NULL REFERENCES owners(user_id) ON DELETE CASCADE,
  animal_id    BIGINT      NOT NULL REFERENCES animals(animal_id) ON DELETE CASCADE,
  status       VARCHAR(20) NOT NULL CHECK (status IN ('DRAFT','ACTIVE','PAUSED','SOLD','ARCHIVED')),
  price        NUMERIC(12,2) NOT NULL CHECK (price >= 0),
  description  TEXT,
  created_at   TIMESTAMP   NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_listing_owner_matches_animal_owner
    FOREIGN KEY (owner_id) REFERENCES owners(user_id) ON DELETE CASCADE
);

CREATE TABLE appointments (
  appointment_id       BIGSERIAL PRIMARY KEY,
  clinic_id            BIGINT      NOT NULL REFERENCES vet_clinics(clinic_id) ON DELETE CASCADE,
  animal_id            BIGINT      NOT NULL REFERENCES animals(animal_id) ON DELETE CASCADE,
  responsible_owner_id BIGINT      NOT NULL REFERENCES owners(user_id) ON DELETE CASCADE,
  status               VARCHAR(20) NOT NULL CHECK (status IN ('REQUESTED','CONFIRMED','CANCELLED','DONE','NO_SHOW')),
  date_time            TIMESTAMP   NOT NULL,
  notes                TEXT
);

CREATE TABLE reviews (
  review_id    BIGSERIAL PRIMARY KEY,
  reviewer_id  BIGINT NOT NULL REFERENCES clients(user_id) ON DELETE CASCADE,
  rating       INT    NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment      TEXT,
  created_at   TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE user_reviews (
  review_id      BIGINT PRIMARY KEY REFERENCES reviews(review_id) ON DELETE CASCADE,
  target_user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE clinic_reviews (
  review_id        BIGINT PRIMARY KEY REFERENCES reviews(review_id) ON DELETE CASCADE,
  target_clinic_id BIGINT NOT NULL REFERENCES vet_clinics(clinic_id) ON DELETE CASCADE
);

CREATE TABLE health_records (
  healthrecord_id BIGSERIAL PRIMARY KEY,
  animal_id       BIGINT NOT NULL REFERENCES animals(animal_id) ON DELETE CASCADE,
  appointment_id  BIGINT NOT NULL REFERENCES appointments(appointment_id) ON DELETE CASCADE,
  type            VARCHAR(40) NOT NULL,
  description     TEXT,
  date            DATE NOT NULL
);

DML