Appendix A. Frogge Emporium database schema
This appendix provides a concise, runnable setup for the Frogge Emporium sample database. It begins by creating a dedicated role and database, then loading a single schema file via psql. The model is organized into four logical schemas—erp, audit, support, and test—so readers can reproduce scenarios cleanly and examine different concerns in isolation while working through the book’s examples.
The erp schema contains the core business entities and relationships. It defines customers and a one-to-one customer_contact_details table (with an email index), plus controlled vocabularies via enums for order_status and email_type. Orders are grouped in order_groups and broken down into individual orders, with timestamps for lifecycle tracking. Financials are modeled with invoices (amounts, paid flag, ties to customers and order groups) and payments (timestamped, indexed), and suppliers hold vendor details. Communication and operations history appears in sent_emails (timestamp as primary key, links to customers and invoices), and operational metrics are represented by energy_usage, which records branch readings with defaults and units. Throughout, identity-generated primary keys, NOT NULL/defaults, and foreign keys enforce structure and integrity.
The support schema captures customer service workflow via tickets, recording content, status, and open/close times. The audit schema adds a simple audit_log for who/what/when activity tracing. Together with the empty test schema, this organization illustrates clear separation of concerns: business data (erp), operational support (support), compliance/forensics (audit), and experimentation (test). The result is a minimal but complete DDL baseline that demonstrates referential integrity, status modeling with enums, judicious indexing, and timestamped event trails, while remaining easy to load and extend for hands-on exploration.
Frogge Emporium database data
You can populate the database with the data for customers and their contact details by loading the file customer_dump.sql as follows from the command line:
psql -U frogge -f customer_dump.sql
customer_dump.sql can be found at: https://github.com/vyruss/postgresql-mistakes/
Finally, you can create the rest of the data by running create_data.sql as follows from the command line:
psql -U frogge -f create_data.sql
create_data.sql can be found at: https://github.com/vyruss/postgresql-mistakes/
The content of create_data.sql is below:
-- Data for suppliers table
INSERT INTO erp.suppliers (company_name, state, country, email) VALUES
('Omni Consumer Products', 'MI', 'United States of America',
'ocp@example.com'),
('Yoyodyne',null,'Japan','yoyodyne@example.com');
-- Data for orders, order_groups, invoices, payments, sent_emails tables
DO $$
DECLARE _id bigint;
DECLARE _t1 timestamptz;
DECLARE _t2 timestamptz;
BEGIN
SELECT CURRENT_DATE - INTERVAL '1y' INTO _t1;
SELECT CURRENT_DATE INTO _t2;
FOR i IN 1 .. 50000 LOOP
INSERT INTO erp.order_groups (status, placed_at, updated_at, customer)
VALUES ('Fulfilled',
(_t1 + (i * INTERVAL '1 s')),
(_t1 + (i * INTERVAL '1 s')),
TRUNC(RANDOM() * 14000 + 1)) RETURNING id INTO _id;
INSERT INTO erp.orders (order_group, status, placed_at, updated_at,
item) VALUES
(_id, 'Fulfilled',
(_t1 + (i * INTERVAL '1 s')),
(_t1 + (i * INTERVAL '1 s')),
TRUNC(RANDOM() * 1000 + 1));
INSERT INTO erp.invoices (amount, customer, paid, order_group,
created_at, updated_at) VALUES
(59.95, (SELECT customer FROM erp.order_groups WHERE id=_id), 't',
_id,
(_t1 + (i * INTERVAL '1 s')),
(_t1 + (i * INTERVAL '1 s')
+ INTERVAL '30 s')) RETURNING id INTO _id;
INSERT INTO erp.payments (tstamp, amount, invoice)
VALUES ((_t1 + (i * INTERVAL '1 s')
+ INTERVAL '30 s'), 59.95, _id);
END LOOP;
FOR i IN 1 .. 200000 LOOP
INSERT INTO erp.order_groups (status, placed_at, updated_at, customer)
VALUES ('Placed', _t2 - INTERVAL '2 d' + (i * INTERVAL '1 s'),
_t2 - INTERVAL '2 d' + (i * INTERVAL '1 s'),
TRUNC(RANDOM() * 14000 + 1)) RETURNING id INTO _id;
INSERT INTO erp.orders (order_group, status, placed_at, updated_at,
item) VALUES
(_id, 'Placed', _t2 - INTERVAL '2 d' + (i * INTERVAL '1 s'),
_t2 - INTERVAL '2 d' + (i * INTERVAL '1 s'),
TRUNC(RANDOM() * 1000 + 1));
INSERT INTO erp.invoices (amount, customer, paid, order_group,
created_at, updated_at) VALUES
(59.95, (SELECT customer FROM erp.order_groups WHERE id=_id), 't',
_id,
_t2 - INTERVAL '2 d' + (i * INTERVAL '1 s'),
_t2 - INTERVAL '2 d' + (i * INTERVAL '1 s') + INTERVAL '30 s')
RETURNING id INTO _id;
INSERT INTO erp.payments (tstamp, amount, invoice)
VALUES (_t2 - INTERVAL '2 d' + (i * INTERVAL '1 s') + INTERVAL '30 s',
59.95, _id);
END LOOP;
WITH o AS (SELECT id FROM erp.orders ORDER BY RANDOM() LIMIT 1350
FOR UPDATE)
UPDATE erp.orders SET item = NULL, service = 21
FROM o WHERE orders.id=o.id;
WITH i AS (SELECT id FROM erp.invoices ORDER BY RANDOM() LIMIT 1350
FOR UPDATE)
UPDATE erp.invoices SET paid='f' FROM i WHERE invoices.id=i.id;
WITH i AS (SELECT id, created_at, customer FROM erp.invoices
WHERE paid='f')
INSERT INTO erp.sent_emails (tstamp, customer, email_type, invoice)
SELECT i.created_at + INTERVAL '1 d', i.customer,
'Invoice reminder', i.id FROM i;
END $$ LANGUAGE plpgsql;
-- Data for tickets table
INSERT INTO support.tickets (status, content, opened_at, closed_at)
SELECT 20, 'issue text',
CURRENT_DATE - INTERVAL '2y' + n * (INTERVAL '1 m'),
CURRENT_DATE - INTERVAL '2y' + n * (INTERVAL '1 m') + INTERVAL '1 d'
FROM generate_series(1,1000000) n;
INSERT INTO support.tickets (status, content, opened_at)
SELECT 10, 'issue text',
CURRENT_DATE - INTERVAL '1y' + n * (INTERVAL '1 m')
FROM generate_series(1,500) n;
FAQ
How do I create the frogge database user and database?
Run these commands in psql as the postgres superuser:CREATE USER frogge PASSWORD '<password>'; then CREATE DATABASE frogge OWNER frogge;How do I load the Frogge Emporium schema into the database?
From your shell, run:psql -U frogge -f schema.sql (ensure your PGHOST and PGPORT are correct or add -h/-p options).Where can I find the schema.sql file?
The schema file is available at: https://github.com/vyruss/postgresql-mistakes/Which schemas does the script create and what are they for?
It creates four schemas:erp (core business data), audit (audit log), support (customer support), and test (for testing or experiments).How are customers and their contact details modeled?
erp.customers stores customer identities with an identity primary key. erp.customer_contact_details uses the same id as a foreign key to customers, acting as a 1:1 extension table; it has an index on email and defaults email to an empty string.How are order status and orders represented?
erp.order_status is an enum (Placed, Fulfilled, Cancelled). erp.order_groups aggregates orders for a customer and stores status and timestamps. erp.orders links to an order group, has its own status and timestamps, and optional item/service references (as integers).How are invoices and payments stored?
erp.invoices has an identity PK, amount (numeric), paid (default false), FKs to customers and order_groups, and timestamp defaults. erp.payments records tstamp, amount, and the target invoice, with an index on tstamp.How are suppliers and outbound emails tracked?
erp.suppliers stores supplier identity and contact fields. erp.email_type is an enum of outbound email categories, and erp.sent_emails records each send with a primary key tstamp, the customer, email_type, and optional linked invoice.How is branch energy usage recorded?
erp.energy_usage stores branch_id, a reading_time (default current timestamp), reading (numeric), and unit (default kWh). No primary key is declared in the script.What PostgreSQL features does this schema demonstrate?
Use of multiple schemas, identity columns (GENERATED ALWAYS AS IDENTITY), enum types scoped under a schema, foreign keys, indexes, default values (including CURRENT_TIMESTAMP), and timestamptz timestamp types.
PostgreSQL Mistakes and How to Avoid Them ebook for free
