Database schema example · PostgreSQL
Order database schema example
This small ERD uses customers and orders to show primary keys, UNIQUE, a foreign key, a CHECK, and a default. Open the downloadable ERD file in Relio ERD to inspect the relationship and generated SQL.
Relationship at a glance
customers
Customers- idINTEGER
- emailTEXT
orders
Orders- idINTEGER
- customer_idINTEGER
- totalNUMERIC
- created_atTIMESTAMPTZ
Fields and constraints
| Table | Column | Type | Nullable | Key, rule, or default |
|---|---|---|---|---|
| customers | id | INTEGER | No | Primary key |
| customers | TEXT | No | UNIQUE · prevents duplicate email | |
| orders | id | INTEGER | No | Primary key |
| orders | customer_id | INTEGER | No | Foreign key → customers.id |
| orders | total | NUMERIC | No | CHECK · at least 0 |
| orders | created_at | TIMESTAMPTZ | No | Default · CURRENT_TIMESTAMP |
Why the schema is shaped this way
Customer email is unique
NOT NULL disallows NULL values, and UNIQUE prevents duplicate email values. Email format and empty strings require separate validation.
Each order references a customer
orders.customer_id references customers.id so an order cannot point to a missing customer. This models one customer to many orders.
The total cannot be negative
A CHECK(total >= 0) constrains the order total. created_at uses PostgreSQL CURRENT_TIMESTAMP as its default.
Generated PostgreSQL SQL
The SQL below matches the downloadable example.sql file.
BEGIN;
CREATE TABLE "public"."customers" (
"id" INTEGER NOT NULL,
"email" TEXT NOT NULL,
CONSTRAINT "customers_pkey" PRIMARY KEY ("id"),
CONSTRAINT "customers_email_key" UNIQUE ("email")
);
CREATE TABLE "public"."orders" (
"id" INTEGER NOT NULL,
"customer_id" INTEGER NOT NULL,
"total" NUMERIC NOT NULL,
"created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "orders_pkey" PRIMARY KEY ("id"),
CONSTRAINT "orders_total_check" CHECK ("total" >= E'0'::NUMERIC),
CONSTRAINT "fk_orders_customer_id_f0efafd5"
FOREIGN KEY ("customer_id") REFERENCES "public"."customers" ("id")
ON DELETE NO ACTION ON UPDATE NO ACTION
);
COMMIT;
Download the example files
Open and edit the ERD JSON in the editor, or inspect the SQL as a PostgreSQL DDL starting point.
Scope of this example
This example does not define an ID generation strategy or include order items, shipping, inventory, currency, payment authorization or refunds, or a payment ledger. Before using it in a real service, define deletion behavior and amount precision, then review indexes for your query patterns.