Changes between Version 5 and Version 6 of LogicalAndPhysicalDesign

Timestamp:

12/19/25 10:41:06 (10 days ago)

Author:

231035

Comment:

--

LogicalAndPhysicalDesign

@@ -55 +55 @@
 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