db2026/4/schema.sql

DROP TABLE IF EXISTS "Person", "Client", "Master", "Studio", "Course", "Class", "Class_Master", "Registration", "FoodProductEnum", "FoodProduct", "Equipment", "Class_Equipment", "DrugEnum", "Drug", "DrugIntolerance", "ActivityLog";

DROP TYPE IF EXISTS "ClassType", "RegistrationStatus", "UsageType";


CREATE TYPE "UsageType" AS ENUM (
  'PayOnce',
  'PayMonthly'
);


CREATE TABLE "Person" (
  person_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  full_name text NOT NULL,
  passwd_hash bytea NOT NULL
);


CREATE TABLE "Client" (
  person_id int PRIMARY KEY,

  phone text NOT NULL,
  email text NOT NULL,
  billing_account text NOT NULL,
  
  FOREIGN KEY (person_id) REFERENCES "Person" (person_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);


CREATE TABLE "Master" (
  person_id int PRIMARY KEY,

  passport text UNIQUE NOT NULL,
  readme text NOT NULL,

  FOREIGN KEY (person_id) REFERENCES "Person" (person_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);


CREATE TABLE "Studio" (
  studio_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  address text UNIQUE NOT NULL,
  capacity int NOT NULL CHECK (capacity > 0),
  begin_date timestamptz NOT NULL,
  duration interval NOT NULL,
  usage_price real NOT NULL,
  usage_type "UsageType" NOT NULL
);


CREATE TABLE "Course" (
  course_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  master_id int,
  name text NOT NULL,
  price real NOT NULL CHECK (price > 0),
  deadline_date timestamptz NOT NULL,

  FOREIGN KEY (master_id) REFERENCES "Master" (person_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);


CREATE TYPE "ClassType" AS ENUM (
  'Theory',
  'Practice'
);


CREATE TABLE "Class" (
  begin_date timestamptz,
  course_id int,
  PRIMARY KEY (begin_date, course_id),

  studio_id int,
  name text NOT NULL,
  type "ClassType" NOT NULL,
  duration interval NOT NULL,

  FOREIGN KEY (course_id) REFERENCES "Course" (course_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  
  FOREIGN KEY (studio_id) REFERENCES "Studio" (studio_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);


CREATE TABLE "Class_Master" (
  class_begin_date timestamptz,
  class_course_id int,
  master_id int,
  PRIMARY KEY (class_begin_date, class_course_id, master_id),

  FOREIGN KEY (master_id) REFERENCES "Master" (person_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  
  FOREIGN KEY (class_begin_date, class_course_id) REFERENCES "Class" (begin_date, course_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);


CREATE TYPE "RegistrationStatus" AS ENUM (
  'NotPaid',
  'Paid'
);


CREATE TABLE "Registration" (
  client_id int,
  course_id int,
  PRIMARY KEY (client_id, course_id),

  date timestamptz NOT NULL,
  status "RegistrationStatus" NOT NULL,

  FOREIGN KEY (client_id) REFERENCES "Client" (person_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  
  FOREIGN KEY (course_id) REFERENCES "Course" (course_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);


CREATE TABLE "FoodProductEnum" (
  food_product_enum_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  name text NOT NULL,
  avg_price real NOT NULL
);


CREATE TABLE "FoodProduct" (
  food_product_id int GENERATED ALWAYS AS IDENTITY,
  food_product_enum_id int,
  PRIMARY KEY (food_product_id, food_product_enum_id),

  class_begin_date timestamptz,
  class_course_id int,
  buy_price real NOT NULL,
  buy_date timestamptz NOT NULL,
  delivery_price real NOT NULL,
  delivery_date timestamptz NOT NULL,
  expires_date timestamptz NOT NULL,

  FOREIGN KEY (food_product_enum_id) REFERENCES "FoodProductEnum" (food_product_enum_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  
  FOREIGN KEY (class_begin_date, class_course_id) REFERENCES "Class" (begin_date, course_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);


CREATE TABLE "Equipment" (
  equipment_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  studio_id int,
  name text NOT NULL,
  usage_price real NOT NULL,
  usage_type "UsageType" NOT NULL,
  delivery_price real NOT NULL,
  delivery_date timestamptz NOT NULL,

  FOREIGN KEY (studio_id) REFERENCES "Studio" (studio_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);


CREATE TABLE "Class_Equipment" (
  class_begin_date timestamptz,
  class_course_id int,
  equipment_id int,
  PRIMARY KEY (class_begin_date, class_course_id, equipment_id),

  FOREIGN KEY (class_begin_date, class_course_id) REFERENCES "Class" (begin_date, course_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  
  FOREIGN KEY (equipment_id) REFERENCES "Equipment" (equipment_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);


CREATE TABLE "DrugEnum" (
  drug_enum_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  name text NOT NULL,
  avg_price real NOT NULL
);


CREATE TABLE "Drug" (
  drug_id int GENERATED ALWAYS AS IDENTITY,
  drug_enum_id int,
  PRIMARY KEY (drug_id, drug_enum_id),

  course_id int,
  buy_price real NOT NULL,
  buy_date timestamptz NOT NULL,
  delivery_price real NOT NULL,
  delivery_date timestamptz NOT NULL,
  expires_date timestamptz NOT NULL,

  FOREIGN KEY (drug_enum_id) REFERENCES "DrugEnum" (drug_enum_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  
  FOREIGN KEY (course_id) REFERENCES "Course" (course_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);


CREATE TABLE "DrugIntolerance" (
  person_id int,
  drug_enum_id int,

  PRIMARY KEY (person_id, drug_enum_id),

  FOREIGN KEY (person_id) REFERENCES "Person" (person_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  
  FOREIGN KEY (drug_enum_id) REFERENCES "DrugEnum" (drug_enum_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);


CREATE TABLE "ActivityLog" (
  activity_log_id int GENERATED ALWAYS AS IDENTITY,
  person_id int,
  PRIMARY KEY (activity_log_id, person_id),
  
  action text NOT NULL,

  FOREIGN KEY (person_id) REFERENCES "Person" (person_id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
);