coinvictor.sql

CREATE TABLE users (
  user_id           SERIAL,
  user_name         VARCHAR(80) NOT NULL,
  user_email        VARCHAR(80) UNIQUE,
  password          VARCHAR(80) NOT NULL,
  registration_date TIMESTAMP,
  PRIMARY KEY (user_id)
);

CREATE TYPE CATEGORY_TYPE AS ENUM ('income', 'spending');
CREATE TYPE CURRENCY AS ENUM ('rub', 'usd');

CREATE TABLE category (
  category_id   SERIAL,
  category_name VARCHAR(80) NOT NULL,
  category_type CATEGORY_TYPE,
  balance       INTEGER DEFAULT 0,
  planned       INTEGER,
  user_id       SERIAL UNIQUE,
  PRIMARY KEY (category_id),
  FOREIGN KEY (user_id) REFERENCES users (user_id),
  CHECK (balance >= 0)
);

CREATE TABLE transactions (
  transaction_id SERIAL,
  sum            FLOAT,
  date           TIMESTAMP,
  currency       CURRENCY,
  user_id        SERIAL,
  from_category  SERIAL,
  to_category    SERIAL,
  PRIMARY KEY (transaction_id),
  FOREIGN KEY (user_id) REFERENCES users (user_id),
  FOREIGN KEY (from_category) REFERENCES category (category_id),
  FOREIGN KEY (to_category) REFERENCES category (category_id)
)