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         freepost BOOLEAN NOT NULL DEFAULT FALSE,
   9         model    TEXT,
  10         brand    TEXT,
  11         CONSTRAINT positive_stock CHECK (stock >= 0)
  12 );
  13
  14 CREATE TABLE IF NOT EXISTS discounts (
  15         discount VARCHAR(20) PRIMARY KEY,
  16         fraction DECIMAL,
  17         flat     INT,
  18         CONSTRAINT fraction_xor_flat CHECK ((fraction IS NULL AND flat IS NOT NULL) OR (fraction IS NOT NULL AND flat IS NULL))
  19 );
  20
  21 CREATE TABLE IF NOT EXISTS orders (
  22         id       TEXT PRIMARY KEY,
  23         date     BIGINT NOT NULL,
  24         products JSON NOT NULL,
  25         total    INT NOT NULL,
  26         discount VARCHAR(20) REFERENCES discounts UNIQUE,
  27
  28         stripe_token TEXT,
  29
  30         -- DELIVERY
  31         first_name   VARCHAR(20) NOT NULL,
  32         last_name    VARCHAR(20) NOT NULL,
  33         email        VARCHAR(80) NOT NULL,
  34         phone        VARCHAR(20),
  35         postcode     VARCHAR(10) NOT NULL,
  36         address1     VARCHAR(32) NOT NULL,
  37         address2     VARCHAR(32),
  38         address3     VARCHAR(32),
  39         address4     VARCHAR(32),
  40         safe_place   VARCHAR(32),
  41         instructions VARCHAR(32)
  42 );