db.sql

   1 CREATE TABLE IF NOT EXISTS products (
   2         product  serial PRIMARY KEY,
   3         title    TEXT   NOT NULL,
   4         subtitle TEXT   NOT NULL,
   5         summary  TEXT   NOT NULL,
   6         price    INT    NOT NULL,
   7         stock    INT    NOT NULL,
   8         CONSTRAINT positive_stock CHECK (stock >= 0)
   9 );
  10
  11 CREATE TABLE IF NOT EXISTS discounts (
  12         discount VARCHAR(20) PRIMARY KEY,
  13         fraction DECIMAL,
  14         flat     INT,
  15         CONSTRAINT fraction_xor_flat CHECK ((fraction IS NULL AND flat IS NOT NULL) OR (fraction IS NOT NULL AND flat IS NULL))
  16 );
  17
  18 CREATE TABLE IF NOT EXISTS orders (
  19         id       TEXT PRIMARY KEY,
  20         products JSON NOT NULL,
  21         total    INT NOT NULL,
  22         discount VARCHAR(20) REFERENCES discounts UNIQUE,
  23
  24         stripe_token TEXT,
  25
  26         -- DELIVERY
  27         first_name   VARCHAR(20) NOT NULL,
  28         last_name    VARCHAR(20) NOT NULL,
  29         email        VARCHAR(80) NOT NULL,
  30         phone        VARCHAR(20),
  31         postcode     VARCHAR(10) NOT NULL,
  32         address1     VARCHAR(32) NOT NULL,
  33         address2     VARCHAR(32),
  34         address3     VARCHAR(32),
  35         address4     VARCHAR(32),
  36         safe_place   VARCHAR(32),
  37         instructions VARCHAR(32)
  38 );