Data Modeling Basics

8 examples to get you started with Data Modeling - 5 basic and 3 intermediate.

Prerequisites

• PostgreSQL 18.4 (or compatible 17.x cluster) with psql access.
• A scratch database for DDL experiments: CREATE DATABASE modeling_lab;

Basic Examples

1. Conceptual Entity Sketch

Name the nouns in your domain before writing DDL.

-- Conceptual model (documentation only - not executable DDL)
-- Customer --places--> Order --contains--> LineItem
-- Product --referenced-by--> LineItem

• Start with entities (Customer, Order) and relationships (places, contains).
• Cardinality comes later; capture business language first.
• This sketch drives table names, FK direction, and API boundaries.
• Share it with product and backend before migration files exist.

Related: Entity-Relationship Modeling - cardinality and optionality

2. Logical Table with Surrogate Key

Map one entity to one table with a stable primary key.

CREATE TABLE customers (
  customer_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email         text NOT NULL UNIQUE,
  display_name  text NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now()
);

• GENERATED ALWAYS AS IDENTITY is the PostgreSQL-native surrogate key (preferred over serial).
• UNIQUE on email encodes a business rule at the logical layer.
• timestamptz stores absolute instants; avoid timestamp without time zone for user-facing times.

Related: Entity-Relationship Modeling - naming and keys

3. Foreign Key for a One-to-Many

Child rows reference parent rows; the FK column lives on the "many" side.

CREATE TABLE orders (
  order_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (customer_id),
  status       text NOT NULL DEFAULT 'draft',
  placed_at    timestamptz
);
 
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

• REFERENCES customers (customer_id) enforces referential integrity in the database.
• Index the FK column (customer_id) for join and cascade performance.
• Default status documents the lifecycle entry point in the physical model.

Related: Entity-Relationship Modeling - one-to-many patterns

4. Junction Table for Many-to-Many

Resolve M:N relationships with a dedicated link table and composite uniqueness.

CREATE TABLE products (
  product_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text NOT NULL UNIQUE,
  name        text NOT NULL
);
 
CREATE TABLE order_items (
  order_id    bigint NOT NULL REFERENCES orders (order_id) ON DELETE CASCADE,
  product_id  bigint NOT NULL REFERENCES products (product_id),
  quantity    integer NOT NULL CHECK (quantity > 0),
  unit_price  numeric(12, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

• Composite PRIMARY KEY (order_id, product_id) prevents duplicate lines per product per order.
• ON DELETE CASCADE on order_id removes line items when an order is deleted.
• Store unit_price at order time (a snapshot), not only the current catalog price.

Related: Entity-Relationship Modeling - M:N resolution

5. Schema as a Bounded Context Boundary

Group related tables under a PostgreSQL schema to mirror domain boundaries.

CREATE SCHEMA billing;
CREATE SCHEMA catalog;
 
CREATE TABLE billing.invoices (
  invoice_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL,
  issued_at    timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE catalog.products (
  product_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text NOT NULL UNIQUE
);

• Schemas are cheap namespaces - use them before splitting databases.
• Cross-schema FKs are allowed but signal coupling; document the contract.
• search_path and grants per schema control which roles see which context.

Related: Domain-Driven Schema Boundaries - bounded contexts

Intermediate Examples

6. Physical Model with Check Constraints

Encode invariants the application should not be sole guardian of.

CREATE TABLE orders (
  order_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (customer_id),
  status       text NOT NULL,
  placed_at    timestamptz,
  CONSTRAINT orders_status_check
    CHECK (status IN ('draft', 'placed', 'shipped', 'cancelled')),
  CONSTRAINT orders_placed_at_required
    CHECK (status = 'draft' OR placed_at IS NOT NULL)
);

• CHECK constraints survive application bugs and ad-hoc SQL.
• Pair status enums with temporal rules (placed_at required once placed).
• Prefer CHECK + text over PostgreSQL ENUM when values change often.

Related: Schema Evolution Strategy - evolving constraints safely

7. Compatibility View During Schema Evolution

Expose a stable interface while physical columns move behind migrations.

ALTER TABLE customers ADD COLUMN legal_name text;
 
UPDATE customers SET legal_name = display_name WHERE legal_name IS NULL;
 
CREATE OR REPLACE VIEW customers_v1 AS
SELECT
  customer_id,
  email,
  display_name,
  COALESCE(legal_name, display_name) AS name_for_invoices,
  created_at
FROM customers;

• Views let services read a stable contract while columns are renamed or split.
• CREATE OR REPLACE VIEW is a low-risk expand step in expand-contract migrations.
• Deprecate the view only after all consumers move to the new column names.

Related: Schema Evolution Strategy - backward-compatible views

8. ADR-Style Ownership Comment

Document who owns a table and whether other services may write to it.

COMMENT ON TABLE billing.invoices IS
  'Owner: billing-service. Writes: billing-service only. '
  'Reads: reporting-replica, finance-batch. '
  'ADR: shared-db-with-schema-isolation (2024-06).';

• COMMENT ON metadata survives in dumps and \d+ output.
• Tie comments to an ADR when debating single-DB vs per-service databases.
• Ownership comments prevent "helpful" cross-service writes that corrupt invariants.

Related: ADR: Single Database vs Per-Service DB - data ownership trade-offs

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.