Changes between Version 7 and Version 8 of LogicalAndPhysicalDesign


Ignore:
Timestamp:
12/19/25 12:59:01 (9 days ago)
Author:
231035
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • LogicalAndPhysicalDesign

    v7 v8  
    4141HealthRecord consistency: its animal_id must match appointment.animal_id (via trigger)
    4242== DDL
    43 {{{
    44 DROP TABLE IF EXISTS health_records CASCADE;
    45 DROP TABLE IF EXISTS clinic_reviews CASCADE;
    46 DROP TABLE IF EXISTS user_reviews CASCADE;
    47 DROP TABLE IF EXISTS reviews CASCADE;
    48 DROP TABLE IF EXISTS appointments CASCADE;
    49 DROP TABLE IF EXISTS listings CASCADE;
    50 DROP TABLE IF EXISTS animals CASCADE;
    51 DROP TABLE IF EXISTS vet_clinics CASCADE;
    52 DROP TABLE IF EXISTS notifications CASCADE;
    53 DROP TABLE IF EXISTS owners CASCADE;
    54 DROP TABLE IF EXISTS clients CASCADE;
    55 DROP TABLE IF EXISTS admins CASCADE;
    56 DROP TABLE IF EXISTS users CASCADE;
    5743
    58 CREATE TABLE users (
    59   user_id     BIGSERIAL PRIMARY KEY,
    60   username    VARCHAR(30)  NOT NULL,
    61   email       VARCHAR(254) NOT NULL,
    62   name        VARCHAR(60)  NOT NULL,
    63   surname     VARCHAR(60)  NOT NULL,
    64   created_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
    65   CONSTRAINT uq_users_username UNIQUE (username),
    66   CONSTRAINT uq_users_email    UNIQUE (email)
    67 );
    68 
    69 CREATE TABLE admins (
    70   user_id BIGINT PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE
    71 );
    72 
    73 CREATE TABLE clients (
    74   user_id BIGINT PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE
    75 );
    76 
    77 CREATE TABLE owners (
    78   user_id BIGINT PRIMARY KEY REFERENCES clients(user_id) ON DELETE CASCADE
    79 );
    80 
    81 CREATE TABLE notifications (
    82   notification_id BIGSERIAL PRIMARY KEY,
    83   user_id         BIGINT       NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    84   type            VARCHAR(40)  NOT NULL,
    85   message         TEXT         NOT NULL,
    86   is_read         BOOLEAN      NOT NULL DEFAULT FALSE,
    87   created_at      TIMESTAMP    NOT NULL DEFAULT NOW()
    88 );
    89 
    90 CREATE TABLE vet_clinics (
    91   clinic_id   BIGSERIAL PRIMARY KEY,
    92   name        VARCHAR(120) NOT NULL,
    93   email       VARCHAR(254),
    94   phone       VARCHAR(40),
    95   city        VARCHAR(80)  NOT NULL,
    96   address     VARCHAR(200) NOT NULL,
    97   approved_by BIGINT REFERENCES admins(user_id) ON DELETE SET NULL
    98 );
    99 
    100 CREATE TABLE animals (
    101   animal_id     BIGSERIAL PRIMARY KEY,
    102   owner_id      BIGINT      NOT NULL REFERENCES owners(user_id) ON DELETE CASCADE,
    103   name          VARCHAR(80) NOT NULL,
    104   sex           VARCHAR(10) NOT NULL CHECK (sex IN ('MALE','FEMALE','UNKNOWN')),
    105   date_of_birth DATE,
    106   photo_url     TEXT,
    107   type          VARCHAR(40),
    108   species       VARCHAR(60),
    109   breed         VARCHAR(60),
    110   located_name  VARCHAR(120)
    111 );
    112 
    113 CREATE TABLE listings (
    114   listing_id   BIGSERIAL PRIMARY KEY,
    115   owner_id     BIGINT      NOT NULL REFERENCES owners(user_id) ON DELETE CASCADE,
    116   animal_id    BIGINT      NOT NULL REFERENCES animals(animal_id) ON DELETE CASCADE,
    117   status       VARCHAR(20) NOT NULL CHECK (status IN ('DRAFT','ACTIVE','PAUSED','SOLD','ARCHIVED')),
    118   price        NUMERIC(12,2) NOT NULL CHECK (price >= 0),
    119   description  TEXT,
    120   created_at   TIMESTAMP   NOT NULL DEFAULT NOW(),
    121   CONSTRAINT fk_listing_owner_matches_animal_owner
    122     FOREIGN KEY (owner_id) REFERENCES owners(user_id) ON DELETE CASCADE
    123 );
    124 
    125 CREATE TABLE appointments (
    126   appointment_id       BIGSERIAL PRIMARY KEY,
    127   clinic_id            BIGINT      NOT NULL REFERENCES vet_clinics(clinic_id) ON DELETE CASCADE,
    128   animal_id            BIGINT      NOT NULL REFERENCES animals(animal_id) ON DELETE CASCADE,
    129   responsible_owner_id BIGINT      NOT NULL REFERENCES owners(user_id) ON DELETE CASCADE,
    130   status               VARCHAR(20) NOT NULL CHECK (status IN ('REQUESTED','CONFIRMED','CANCELLED','DONE','NO_SHOW')),
    131   date_time            TIMESTAMP   NOT NULL,
    132   notes                TEXT
    133 );
    134 
    135 CREATE TABLE reviews (
    136   review_id    BIGSERIAL PRIMARY KEY,
    137   reviewer_id  BIGINT NOT NULL REFERENCES clients(user_id) ON DELETE CASCADE,
    138   rating       INT    NOT NULL CHECK (rating BETWEEN 1 AND 5),
    139   comment      TEXT,
    140   created_at   TIMESTAMP NOT NULL DEFAULT NOW()
    141 );
    142 
    143 CREATE TABLE user_reviews (
    144   review_id      BIGINT PRIMARY KEY REFERENCES reviews(review_id) ON DELETE CASCADE,
    145   target_user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE
    146 );
    147 
    148 CREATE TABLE clinic_reviews (
    149   review_id        BIGINT PRIMARY KEY REFERENCES reviews(review_id) ON DELETE CASCADE,
    150   target_clinic_id BIGINT NOT NULL REFERENCES vet_clinics(clinic_id) ON DELETE CASCADE
    151 );
    152 
    153 CREATE TABLE health_records (
    154   healthrecord_id BIGSERIAL PRIMARY KEY,
    155   animal_id       BIGINT NOT NULL REFERENCES animals(animal_id) ON DELETE CASCADE,
    156   appointment_id  BIGINT NOT NULL REFERENCES appointments(appointment_id) ON DELETE CASCADE,
    157   type            VARCHAR(40) NOT NULL,
    158   description     TEXT,
    159   date            DATE NOT NULL
    160 );
    161 }}}
    16244== DML