Logical and Physical Design, DB Creation (DDL)

DDL

CREATE TABLE "User" (
    user_id      BIGSERIAL PRIMARY KEY,
    username     VARCHAR(50)  NOT NULL UNIQUE,
    email        VARCHAR(255) NOT NULL UNIQUE,
    role         VARCHAR(30)  NOT NULL,
    fullname     VARCHAR(100),
    name         VARCHAR(50),
    surname      VARCHAR(50),
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);


CREATE TABLE "VetClinic" (
    clinic_id BIGSERIAL PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    address   VARCHAR(200),
    city      VARCHAR(80),
    location  VARCHAR(100),
    email     VARCHAR(255),
    phone     VARCHAR(30)
);

CREATE TABLE "Animal" (
    animal_id     BIGSERIAL PRIMARY KEY,
    owner_id      BIGINT     NOT NULL,
    name          VARCHAR(80) NOT NULL,
    species       VARCHAR(50),
    type          VARCHAR(50),
    breed         VARCHAR(80),
    sex           VARCHAR(10),
    date_of_birth DATE,
    located       VARCHAR(100),
    photo_url     TEXT,
    CONSTRAINT fk_animal_owner
        FOREIGN KEY (owner_id)
        REFERENCES "User"(user_id)
        ON DELETE CASCADE
);


CREATE TABLE "Listing" (
    listing_id  BIGSERIAL PRIMARY KEY,
    user_id     BIGINT     NOT NULL,  
    animal_id   BIGINT     NOT NULL,  
    status      VARCHAR(30) NOT NULL,
    price       NUMERIC(10,2) NOT NULL,
    description TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_listing_user
        FOREIGN KEY (user_id)
        REFERENCES "User"(user_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_listing_animal
        FOREIGN KEY (animal_id)
        REFERENCES "Animal"(animal_id)
        ON DELETE CASCADE
);

CREATE TABLE "Appointment" (
    appointment_id BIGSERIAL PRIMARY KEY,
    animal_id      BIGINT     NOT NULL,
    clinic_id      BIGINT     NOT NULL,
    date_time      TIMESTAMPTZ NOT NULL,
    status         VARCHAR(30) NOT NULL,
    notes          TEXT,
    CONSTRAINT fk_appt_animal
        FOREIGN KEY (animal_id)
        REFERENCES "Animal"(animal_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_appt_clinic
        FOREIGN KEY (clinic_id)
        REFERENCES "VetClinic"(clinic_id)
        ON DELETE CASCADE
);


CREATE TABLE "HealthRecord" (
    healthrecord_id BIGSERIAL PRIMARY KEY,
    animal_id       BIGINT     NOT NULL,
    appointment_id  BIGINT,
    clinic_id       BIGINT,
    type            VARCHAR(50),
    description     TEXT,
    date            DATE NOT NULL,
    CONSTRAINT fk_hr_animal
        FOREIGN KEY (animal_id)
        REFERENCES "Animal"(animal_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_hr_appt
        FOREIGN KEY (appointment_id)
        REFERENCES "Appointment"(appointment_id)
        ON DELETE SET NULL,
    CONSTRAINT fk_hr_clinic
        FOREIGN KEY (clinic_id)
        REFERENCES "VetClinic"(clinic_id)
        ON DELETE SET NULL
);

CREATE TABLE "Notification" (
    notification_id BIGSERIAL PRIMARY KEY,
    user_id         BIGINT     NOT NULL,  
    message         TEXT       NOT NULL,
    type            VARCHAR(50),
    is_read         BOOLEAN    NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_notification_user
        FOREIGN KEY (user_id)
        REFERENCES "User"(user_id)
        ON DELETE CASCADE
);


CREATE TABLE "Review" (
    review_id        BIGSERIAL PRIMARY KEY,
    reviewer_id      BIGINT NOT NULL,        
    target_user_id   BIGINT,                 
    target_clinic_id BIGINT,                 
    rating           SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment          TEXT,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_review_reviewer
        FOREIGN KEY (reviewer_id)
        REFERENCES "User"(user_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_review_target_user
        FOREIGN KEY (target_user_id)
        REFERENCES "User"(user_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_review_target_clinic
        FOREIGN KEY (target_clinic_id)
        REFERENCES "VetClinic"(clinic_id)
        ON DELETE CASCADE,
    CONSTRAINT review_must_have_target
        CHECK (target_user_id IS NOT NULL OR target_clinic_id IS NOT NULL)
);

DML