Appendix A. Frogge Emporium Database Schema

-- Customers go in this table.
CREATE TABLE customers (
    id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    first_name text NOT NULL,
    middle_name text,
    last_name text,
    marketing_consent boolean DEFAULT false NOT NULL
);

-- This is where we hold contact details for customers.
CREATE TABLE customer_contact_details (
    id bigint PRIMARY KEY REFERENCES customers(id),
    email text DEFAULT '' NOT NULL,
    street_address text,
    city text,
    state text,
    country text,
    phone_no text
);
CREATE INDEX on customer_contact_details (email);

-- Each invoice for an order group goes in here.
CREATE TABLE invoices (
    id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    amount numeric NOT NULL,
    customer bigint REFERENCES customers(id),
    paid boolean DEFAULT false NOT NULL,
    order_group bigint REFERENCES order_groups(id),
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);

-- We represent order status by an enumeration.
CREATE TYPE order_status AS ENUM (
    'Placed',
    'Fulfilled',
    'Cancelled'
);

-- Order groups aggregate large orders for multiple items.
CREATE TABLE order_groups (
    id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    status order_status,
    placed_at timestamp with time zone,
    updated_at timestamp with time zone,
    customer bigint REFERENCES customers(id)
);