Changes between Version 4 and Version 5 of LogicalAndPhysicalDesign


Ignore:
Timestamp:
12/18/25 00:13:07 (11 days ago)
Author:
231035
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • LogicalAndPhysicalDesign

    v4 v5  
    11= Logical and Physical Design, DB Creation (DDL)
     2== Notation
     3* PK marked with [PK]
     4* FK marked with [FK→Table(column)]
     5* UQ = unique constraint
     6== Relation schema
     7{{{USERS + SUBTYPES}}}
     8* Users(user_id [PK], username [UQ], email [UQ], name, surname, created_at)
     9* Admin(user_id [PK, FK→Users(user_id)])
     10* Client(user_id [PK, FK→Users(user_id)])
     11* Owner(user_id [PK, FK→Client(user_id)]) (Owner is a subtype of Client, as in the diagram)
     12
     13{{{NOTIFICATIONS}}}
     14* Notification(notification_id [PK], user_id [FK→Users(user_id)], type, message, is_read, created_at)
     15 
     16{{{VET CLINICS}}}
     17* VetClinic(clinic_id [PK], name, email, phone, city, address, approved_by [FK→Admin(user_id)])
     18
     19{{{ANIMALS}}}
     20* Animal(animal_id [PK], owner_id [FK→Owner(user_id)], name, sex, date_of_birth, photo_url, type, species, breed, located_name)
     21
     22{{{LISTINGS}}}
     23* Listing(listing_id [PK], owner_id [FK→Owner(user_id)], animal_id [FK→Animal(animal_id)], status, price, description, created_at)
     24{{{APPOINTMENTS}}}
     25* 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)
     26{{{REVIEWS (SUPERTYPE + SUBTYPES)}}}
     27* Review(review_id [PK], reviewer_id [FK→Client(user_id)], rating, comment, created_at)
     28* UserReview(review_id [PK, FK→Review(review_id)], target_user_id [FK→Users(user_id)])
     29* ClinicReview(review_id [PK, FK→Review(review_id)], target_clinic_id [FK→VetClinic(clinic_id)])
     30{{{HEALTH RECORDS}}}
     31* HealthRecord(healthrecord_id [PK], animal_id [FK→Animal(animal_id)], appointment_id [FK→Appointment(appointment_id)], type, description, date)
     32----
     33Business rules enforced in DDL:
     34[[BR]]
     35Review rating range (1–5)
     36Disjoint review specialization (a Review can be either UserReview or ClinicReview, not both) via trigger
     37[[BR]]
     38Clinic review constraint: reviewer must have at least one DONE appointment at that clinic (via trigger)
     39[[BR]]
     40HealthRecord consistency: its animal_id must match appointment.animal_id (via trigger)
    241== DDL
    342{{{
    4 
    5 CREATE TYPE user_role AS ENUM ('ADMIN', 'USER', 'GUEST');
    6 
    7 CREATE TABLE User (
    8     user_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    9     username     VARCHAR(50)  NOT NULL UNIQUE,
    10     email        VARCHAR(255) NOT NULL UNIQUE,
    11     role         user_role  NOT NULL,
    12     fullname     VARCHAR(100),
    13     name         VARCHAR(50),
    14     surname      VARCHAR(50),
    15     created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
    16 );
    17 
    18 
    19 CREATE TABLE VetClinic (
    20     clinic_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    21     name      VARCHAR(100) NOT NULL,
    22     address   VARCHAR(200),
    23     city      VARCHAR(80),
    24     location  VARCHAR(100),
    25     email     VARCHAR(255),
    26     phone     VARCHAR(30)
    27 );
    28 
    29 CREATE TABLE Animal (
    30     animal_id     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    31     owner_id      BIGINT     NOT NULL,
    32     name          VARCHAR(80) NOT NULL,
    33     species       VARCHAR(50),
    34     type          VARCHAR(50),
    35     breed         VARCHAR(80),
    36     sex           VARCHAR(10),
    37     date_of_birth DATE,
    38     located       VARCHAR(100),
    39     photo_url     TEXT,
    40     CONSTRAINT fk_animal_owner
    41         FOREIGN KEY (owner_id)
    42         REFERENCES User(user_id)
    43         ON DELETE CASCADE
    44 );
    45 
    46 
    47 CREATE TABLE Listing (
    48     listing_id  BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    49     user_id     BIGINT     NOT NULL, 
    50     animal_id   BIGINT     NOT NULL, 
    51     status      VARCHAR(30) NOT NULL,
    52     price       NUMERIC(10,2) NOT NULL,
    53     description TEXT,
    54     created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    55     CONSTRAINT fk_listing_user
    56         FOREIGN KEY (user_id)
    57         REFERENCES User(user_id)
    58         ON DELETE CASCADE,
    59     CONSTRAINT fk_listing_animal
    60         FOREIGN KEY (animal_id)
    61         REFERENCES Animal(animal_id)
    62         ON DELETE CASCADE
    63 );
    64 
    65 CREATE TABLE Appointment (
    66     appointment_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    67     animal_id      BIGINT     NOT NULL,
    68     clinic_id      BIGINT     NOT NULL,
    69     date_time      TIMESTAMPTZ NOT NULL,
    70     status         VARCHAR(30) NOT NULL,
    71     notes          TEXT,
    72     CONSTRAINT fk_appt_animal
    73         FOREIGN KEY (animal_id)
    74         REFERENCES Animal(animal_id)
    75         ON DELETE CASCADE,
    76     CONSTRAINT fk_appt_clinic
    77         FOREIGN KEY (clinic_id)
    78         REFERENCES VetClinic(clinic_id)
    79         ON DELETE CASCADE
    80 );
    81 
    82 
    83 CREATE TABLE HealthRecord (
    84     healthrecord_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    85     animal_id       BIGINT     NOT NULL,
    86     appointment_id  BIGINT,
    87     clinic_id       BIGINT,
    88     type            VARCHAR(50),
    89     description     TEXT,
    90     date            DATE NOT NULL,
    91     CONSTRAINT fk_hr_animal
    92         FOREIGN KEY (animal_id)
    93         REFERENCES Animal(animal_id)
    94         ON DELETE CASCADE,
    95     CONSTRAINT fk_hr_appt
    96         FOREIGN KEY (appointment_id)
    97         REFERENCES Appointment(appointment_id)
    98         ON DELETE SET NULL,
    99     CONSTRAINT fk_hr_clinic
    100         FOREIGN KEY (clinic_id)
    101         REFERENCES VetClinic(clinic_id)
    102         ON DELETE SET NULL
    103 );
    104 
    105 CREATE TABLE Notification (
    106     notification_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    107     user_id         BIGINT     NOT NULL, 
    108     message         TEXT       NOT NULL,
    109     type            VARCHAR(50),
    110     is_read         BOOLEAN    NOT NULL DEFAULT FALSE,
    111     created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    112     CONSTRAINT fk_notification_user
    113         FOREIGN KEY (user_id)
    114         REFERENCES User(user_id)
    115         ON DELETE CASCADE
    116 );
    117 
    118 
    119 CREATE TABLE Review (
    120     review_id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    121     reviewer_id      BIGINT NOT NULL,       
    122     target_user_id   BIGINT,                 
    123     target_clinic_id BIGINT,                 
    124     rating           SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    125     comment          TEXT,
    126     created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    127     CONSTRAINT fk_review_reviewer
    128         FOREIGN KEY (reviewer_id)
    129         REFERENCES User(user_id)
    130         ON DELETE CASCADE,
    131     CONSTRAINT fk_review_target_user
    132         FOREIGN KEY (target_user_id)
    133         REFERENCES User(user_id)
    134         ON DELETE CASCADE,
    135     CONSTRAINT fk_review_target_clinic
    136         FOREIGN KEY (target_clinic_id)
    137         REFERENCES VetClinic(clinic_id)
    138         ON DELETE CASCADE,
    139     CONSTRAINT review_must_have_target
    140         CHECK (target_user_id IS NOT NULL OR target_clinic_id IS NOT NULL)
    141 );
     43DROP TABLE IF EXISTS health_records CASCADE;
     44DROP TABLE IF EXISTS clinic_reviews CASCADE;
     45DROP TABLE IF EXISTS user_reviews CASCADE;
     46DROP TABLE IF EXISTS reviews CASCADE;
     47DROP TABLE IF EXISTS appointments CASCADE;
     48DROP TABLE IF EXISTS listings CASCADE;
     49DROP TABLE IF EXISTS animals CASCADE;
     50DROP TABLE IF EXISTS vet_clinics CASCADE;
     51DROP TABLE IF EXISTS notifications CASCADE;
     52DROP TABLE IF EXISTS owners CASCADE;
     53DROP TABLE IF EXISTS clients CASCADE;
     54DROP TABLE IF EXISTS admins CASCADE;
     55DROP TABLE IF EXISTS users CASCADE;
    14256
    14357}}}