Skip to content

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
One customer → Many orders

orders

Orders
  • idINTEGER
  • customer_idINTEGER
  • totalNUMERIC
  • created_atTIMESTAMPTZ

Fields and constraints

Tables, columns, types, nullability, and rules in the order example
TableColumnTypeNullableKey, rule, or default
customersidINTEGERNoPrimary key
customersemailTEXTNoUNIQUE · prevents duplicate email
ordersidINTEGERNoPrimary key
orderscustomer_idINTEGERNoForeign key → customers.id
orderstotalNUMERICNoCHECK · at least 0
orderscreated_atTIMESTAMPTZNoDefault · 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.