Changes between Initial Version and Version 1 of LogicalAndPhysicalDesign


Ignore:
Timestamp:
12/07/25 23:05:08 (3 weeks ago)
Author:
231035
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • LogicalAndPhysicalDesign

    v1 v1  
     1= Logical and Physical Design, DB Creation (DDL)
     2== DDL
     3{{{
     4
     5CREATE TABLE "User" (
     6    user_id      BIGSERIAL PRIMARY KEY,
     7    username     VARCHAR(50)  NOT NULL UNIQUE,
     8    email        VARCHAR(255) NOT NULL UNIQUE,
     9    role         VARCHAR(30)  NOT NULL,
     10    fullname     VARCHAR(100),
     11    name         VARCHAR(50),
     12    surname      VARCHAR(50),
     13    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
     14);
     15
     16
     17CREATE TABLE "VetClinic" (
     18    clinic_id BIGSERIAL PRIMARY KEY,
     19    name      VARCHAR(100) NOT NULL,
     20    address   VARCHAR(200),
     21    city      VARCHAR(80),
     22    location  VARCHAR(100),
     23    email     VARCHAR(255),
     24    phone     VARCHAR(30)
     25);
     26
     27CREATE TABLE "Animal" (
     28    animal_id     BIGSERIAL PRIMARY KEY,
     29    owner_id      BIGINT     NOT NULL,
     30    name          VARCHAR(80) NOT NULL,
     31    species       VARCHAR(50),
     32    type          VARCHAR(50),
     33    breed         VARCHAR(80),
     34    sex           VARCHAR(10),
     35    date_of_birth DATE,
     36    located       VARCHAR(100),
     37    photo_url     TEXT,
     38    CONSTRAINT fk_animal_owner
     39        FOREIGN KEY (owner_id)
     40        REFERENCES "User"(user_id)
     41        ON DELETE CASCADE
     42);
     43
     44
     45CREATE TABLE "Listing" (
     46    listing_id  BIGSERIAL PRIMARY KEY,
     47    user_id     BIGINT     NOT NULL, 
     48    animal_id   BIGINT     NOT NULL, 
     49    status      VARCHAR(30) NOT NULL,
     50    price       NUMERIC(10,2) NOT NULL,
     51    description TEXT,
     52    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
     53    CONSTRAINT fk_listing_user
     54        FOREIGN KEY (user_id)
     55        REFERENCES "User"(user_id)
     56        ON DELETE CASCADE,
     57    CONSTRAINT fk_listing_animal
     58        FOREIGN KEY (animal_id)
     59        REFERENCES "Animal"(animal_id)
     60        ON DELETE CASCADE
     61);
     62
     63CREATE TABLE "Appointment" (
     64    appointment_id BIGSERIAL PRIMARY KEY,
     65    animal_id      BIGINT     NOT NULL,
     66    clinic_id      BIGINT     NOT NULL,
     67    date_time      TIMESTAMPTZ NOT NULL,
     68    status         VARCHAR(30) NOT NULL,
     69    notes          TEXT,
     70    CONSTRAINT fk_appt_animal
     71        FOREIGN KEY (animal_id)
     72        REFERENCES "Animal"(animal_id)
     73        ON DELETE CASCADE,
     74    CONSTRAINT fk_appt_clinic
     75        FOREIGN KEY (clinic_id)
     76        REFERENCES "VetClinic"(clinic_id)
     77        ON DELETE CASCADE
     78);
     79
     80
     81CREATE TABLE "HealthRecord" (
     82    healthrecord_id BIGSERIAL PRIMARY KEY,
     83    animal_id       BIGINT     NOT NULL,
     84    appointment_id  BIGINT,
     85    clinic_id       BIGINT,
     86    type            VARCHAR(50),
     87    description     TEXT,
     88    date            DATE NOT NULL,
     89    CONSTRAINT fk_hr_animal
     90        FOREIGN KEY (animal_id)
     91        REFERENCES "Animal"(animal_id)
     92        ON DELETE CASCADE,
     93    CONSTRAINT fk_hr_appt
     94        FOREIGN KEY (appointment_id)
     95        REFERENCES "Appointment"(appointment_id)
     96        ON DELETE SET NULL,
     97    CONSTRAINT fk_hr_clinic
     98        FOREIGN KEY (clinic_id)
     99        REFERENCES "VetClinic"(clinic_id)
     100        ON DELETE SET NULL
     101);
     102
     103CREATE TABLE "Notification" (
     104    notification_id BIGSERIAL PRIMARY KEY,
     105    user_id         BIGINT     NOT NULL, 
     106    message         TEXT       NOT NULL,
     107    type            VARCHAR(50),
     108    is_read         BOOLEAN    NOT NULL DEFAULT FALSE,
     109    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
     110    CONSTRAINT fk_notification_user
     111        FOREIGN KEY (user_id)
     112        REFERENCES "User"(user_id)
     113        ON DELETE CASCADE
     114);
     115
     116
     117CREATE TABLE "Review" (
     118    review_id        BIGSERIAL PRIMARY KEY,
     119    reviewer_id      BIGINT NOT NULL,       
     120    target_user_id   BIGINT,                 
     121    target_clinic_id BIGINT,                 
     122    rating           SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
     123    comment          TEXT,
     124    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
     125    CONSTRAINT fk_review_reviewer
     126        FOREIGN KEY (reviewer_id)
     127        REFERENCES "User"(user_id)
     128        ON DELETE CASCADE,
     129    CONSTRAINT fk_review_target_user
     130        FOREIGN KEY (target_user_id)
     131        REFERENCES "User"(user_id)
     132        ON DELETE CASCADE,
     133    CONSTRAINT fk_review_target_clinic
     134        FOREIGN KEY (target_clinic_id)
     135        REFERENCES "VetClinic"(clinic_id)
     136        ON DELETE CASCADE,
     137    CONSTRAINT review_must_have_target
     138        CHECK (target_user_id IS NOT NULL OR target_clinic_id IS NOT NULL)
     139);
     140
     141}}}
     142== DML