Database Design Document Template (ERD and Physical Model)

Database design: DBMS choice, conceptual, logical and physical models, indexes, partitioning, integrity, security, backup, capacity

Architektur & SystemdesignVollständigdatabase design mathAktualisiert 20.10.2026

Im Editor öffnen.md herunterladen

Anleitung: Use for a new database or a major schema change. Start from the access patterns in section 5 and justify every index against them; record each deliberate denormalisation with its consistency mechanism. Main pitfall: a correct schema that cannot serve the top queries fast, or a document that drifts from the migration files.

Vorschau

Database Design Document: [Database or application name]

Complete design for one database: purpose, DBMS choice, conceptual, logical and physical models, keys and indexes, normalisation decisions, partitioning, transactions, integrity, security, backup and restore, retention, migrations, capacity estimates and naming conventions. Based on common industry practice for relational database design. Replace every [bracketed] value and every row marked Example. Worked examples use an order-management schema on PostgreSQL.

Document control

Field	Value
Document ID	[DBD-ORDERS-001]
Database / schema	[orders_db / public]
Application(s)	[orders-service, reporting-etl]
Version	[0.1]
Status	[Draft / In review / Approved / Superseded]
Owner (data owner)	[@data-owner]
Author (designer)	[@dba-or-engineer]
Classification	[Internal / Confidential]
Last updated	YYYY-MM-DD
Related	[System design], [Data dictionary], [Security architecture]

Approvals

Role	Name	Decision	Date
Application tech lead	[@name]	[Approved / Changes requested]	YYYY-MM-DD
Database administrator	[@name]	[Decision]	YYYY-MM-DD
Data owner	[@name]	[Decision]	YYYY-MM-DD
Security / privacy	[@name]	[Decision]	YYYY-MM-DD

Revision history

Version	Date	Author	Change summary	Migration
0.1	YYYY-MM-DD	[@name]	First draft	n/a
0.2	YYYY-MM-DD	[@name]	Example: Partitioned `order_event` by month	`V012__partition_order_event.sql`
1.0	YYYY-MM-DD	[@name]	Example: Approved for production	`V015`

Introduction
DBMS selection
Conceptual model
Logical model
Workload and access patterns
Physical design
Keys and indexes
Normalisation and denormalisation
Partitioning and sharding
Transactions, isolation and concurrency
Data integrity rules
Security
Backup, restore and retention
Migration strategy
Capacity and performance estimates
Naming conventions
Appendices

1. Introduction

1.1 Purpose

[What the database is for. Example: "The orders database is the system of record for customer orders, order lines, payments references and shipment status for the EU storefront."]

1.2 Scope

In scope	Out of scope
Example: Orders, order lines, order status history, shipments	Product catalogue (owned by Catalogue DB)
Example: Customer reference (ID and display name only)	Customer personal data (owned by Customer DB)
[Item]	[Item]

1.3 Assumptions and constraints

[Example: Single region primary in eu-west-1 with a synchronous standby in another availability zone.]
[Example: Personal data must stay in the EU.]
[Example: Reporting reads go to a read replica, never the primary.]

2. DBMS selection

Criterion	Weight	[PostgreSQL 17]	[MySQL 8.4]	[Document store]
Relational integrity and transactions	High	Strong	Strong	Limited across documents
JSON support for flexible attributes	Medium	Strong (`jsonb`, GIN indexes)	Good	Native
Native partitioning	Medium	Declarative	Yes	Sharding
Team skills and operations tooling	High	Strong	Medium	Weak
Managed service available	High	Yes	Yes	Yes
Decision		Chosen	Rejected	Rejected

Rationale: [Example: "Orders need multi-row transactions across order, lines and payments; PostgreSQL meets this, supports partitioning for the event table and matches the team's operational experience."]

3. Conceptual model

The main business entities and how they relate. Cardinalities reflect the business rules in section 11.

Entity	Business meaning	Owner
Customer	A person or organisation that places orders	Customer domain
Order	A customer's request to buy products, with a delivery address	Orders domain
Order line	One product and quantity within an order	Orders domain
Payment	A payment attempt or capture against an order	Payments domain (reference only)
Shipment	A physical delivery of some or all lines	Fulfilment domain (reference only)
Order event	A recorded change of order status	Orders domain

4. Logical model

The logical model adds attributes and keys. Types here are logical; the physical types are in section 6.

5. Workload and access patterns

5.1 Workload profile

Characteristic	Value
Workload type	Example: OLTP, read-heavy (reads : writes about 8 : 1)
Peak transactions per second	Example: 600
Concurrent connections (pooled)	Example: 200
Largest table at 3 years	Example: `order_event`, about 900 million rows

5.2 Writes

ID	Operation	Tables	Frequency (peak)	Latency target
W-01	Create order with lines	`orders`, `order_line`, `order_event`	80/s	p99 50 ms
W-02	Change order status	`orders`, `order_event`	150/s	p99 20 ms
[W-nn]	[Operation]	[Tables]	[Rate]	[Target]

5.3 Reads

ID	Query	Frequency (peak)	Latency target	Served by index
R-01	Get order with lines by `order_id`	400/s	p99 10 ms	PK, IX-02
R-02	List a customer's orders, newest first, 25 per page	120/s	p99 30 ms	IX-01
R-03	Orders in status `PAID` older than 2 hours (stuck orders job)	1/min	2 s	IX-03 (partial)
R-04	Status history of an order	50/s	p99 20 ms	IX-04
[R-nn]	[Query]	[Rate]	[Target]	[Index]

6. Physical design

6.1 Table `orders`

Holds one row per customer order. Rows are never deleted by the application; cancellation is a status.

Column	Type	Null	Default	Constraints	Description
`order_id`	`uuid`	No	`gen_random_uuid()`	PK	Surrogate key
`order_number`	`varchar(20)`	No		UNIQUE	Human-readable number, `ORD-2026-000123`
`customer_id`	`uuid`	No		FK `customer_ref`	Customer who placed the order
`status`	`varchar(30)`	No	`'PENDING_PAYMENT'`	CHECK in status list	Lifecycle state
`currency`	`char(3)`	No		CHECK `~ '^[A-Z]{3}$'`	ISO 4217 code
`total_amount`	`numeric(12,2)`	No	`0`	CHECK `>= 0`	Sum of line totals
`shipping_address`	`jsonb`	No		CHECK has `countryCode`	Snapshot at order time
`created_at`	`timestamptz`	No	`now()`		Creation time (UTC)
`updated_at`	`timestamptz`	No	`now()`		Maintained by trigger
`version`	`integer`	No	`1`		Optimistic lock counter

6.2 Table `order_line`

Column	Type	Null	Default	Constraints	Description
`order_line_id`	`uuid`	No	`gen_random_uuid()`	PK	Surrogate key
`order_id`	`uuid`	No		FK `orders` ON DELETE CASCADE	Parent order
`line_no`	`smallint`	No		UNIQUE (`order_id`, `line_no`)	Position in order
`sku`	`varchar(40)`	No			Product SKU at order time
`quantity`	`integer`	No		CHECK `between 1 and 999`	Units ordered
`unit_price`	`numeric(12,2)`	No		CHECK `>= 0`	Price per unit at order time
`line_total`	`numeric(12,2)`	No		Generated: `quantity * unit_price`	Stored generated column

6.3 Table `order_event`

Column	Type	Null	Default	Constraints	Description
`order_event_id`	`bigint`	No	identity	PK (with `occurred_at`)	Sequence
`order_id`	`uuid`	No		FK `orders`	Order changed
`from_status`	`varchar(30)`	Yes			Null for creation
`to_status`	`varchar(30)`	No			New status
`actor`	`varchar(100)`	No			User or service that made the change
`occurred_at`	`timestamptz`	No	`now()`	Partition key	Time of change

6.4 Table `[table_name]`

[Repeat the column table for every remaining table, including lookup and join tables.]

7. Keys and indexes

7.1 Key strategy

Decision	Choice	Rationale
Primary keys	Example: UUID v7 surrogate keys	Globally unique, time-ordered so inserts stay near the right edge of the B-tree
Natural keys	Enforced as UNIQUE constraints (`order_number`)	Business lookups without coupling joins to business values
Foreign keys	Declared and indexed on the child side	Integrity and fast parent deletes and joins

7.2 Index register

ID	Table	Columns	Type	Unique	Serves	Notes
IX-01	`orders`	(`customer_id`, `created_at` DESC)	B-tree	No	R-02	Covers pagination by keyset
IX-02	`order_line`	(`order_id`)	B-tree	No	R-01, FK
IX-03	`orders`	(`created_at`) WHERE `status = 'PAID'`	Partial B-tree	No	R-03	Small; only paid orders
IX-04	`order_event`	(`order_id`, `occurred_at`)	B-tree, per partition	No	R-04
IX-05	`orders`	(`shipping_address`)	GIN `jsonb_path_ops`	No	[Support search]	Review after 3 months
[IX-nn]	[Table]	[Columns]	[Type]	[Y/N]	[Pattern]	[Notes]

8. Normalisation and denormalisation

The schema targets third normal form. Deliberate exceptions:

ID	Denormalised data	Where	Reason	Consistency mechanism
DN-01	`total_amount`	`orders`	Avoid summing lines on every list query (R-02)	Recomputed in the same transaction as any line change; nightly reconciliation job
DN-02	`shipping_address` snapshot	`orders`	Legal record of the address at order time	Immutable after `PAID`
DN-03	`unit_price`	`order_line`	Price at order time must not change with the catalogue	Immutable after creation
[DN-nn]	[Data]	[Table]	[Reason]	[Mechanism]

9. Partitioning and sharding

Table	Strategy	Key	Granularity	Lifecycle
`order_event`	Range partitioning	`occurred_at`	Monthly	Created 3 months ahead by scheduled job; detached and archived after 24 months
`orders`	None			Revisit above [200 million] rows
[Table]	[Strategy]	[Key]	[Granularity]	[Lifecycle]

Sharding: [Example: "Not required. A single primary handles the projected peak with 3x headroom (section 15). If needed later, shard by customer_id hash so a customer's orders stay together."]

10. Transactions, isolation and concurrency

Topic	Decision
Default isolation	Example: READ COMMITTED
Stronger isolation	Example: SERIALIZABLE for refund creation, with retry on serialization failure (up to 3 attempts)
Optimistic locking	`version` column; `UPDATE ... WHERE order_id = $1 AND version = $2`; zero rows updated means conflict
Pessimistic locking	`SELECT ... FOR UPDATE SKIP LOCKED` for the stuck-order job queue
Transaction length	No user think time inside a transaction; statement timeout [5 s], idle-in-transaction timeout [10 s]
Deadlock prevention	Lock rows in a fixed order: `orders` before `order_line`
Connection pooling	Example: Transaction-mode pooler, 200 client connections onto 40 server connections

11. Data integrity rules

ID	Rule	Enforced by	Verification
DI-01	An order has at least one line	Deferred constraint trigger	Integration test
DI-02	`total_amount` equals the sum of `line_total`	Application transaction plus nightly check	Reconciliation report
DI-03	Status transitions follow the state machine	Trigger rejects invalid `from_status` to `to_status` pairs	Unit test of trigger
DI-04	Currency is identical for all lines in an order	Application	Contract test
DI-05	An order is never physically deleted	No DELETE grant to application role	Privilege audit
[DI-nn]	[Rule]	[Mechanism]	[Check]

The order status state machine enforced by DI-03:

12. Security

12.1 Roles and privileges

Role	Used by	Privileges
`orders_owner`	Migrations only	Owns schema objects; no login outside the pipeline
`orders_app`	`orders-service`	SELECT, INSERT, UPDATE on application tables; no DELETE on `orders`
`orders_readonly`	Reporting ETL, read replica	SELECT on views in schema `reporting`
`dba_breakglass`	DBAs, on approved incident ticket	Full access, session logged, time-limited
[Role]	[User]	[Privileges]

12.2 Data protection

Control	Implementation
Encryption in transit	TLS required (`sslmode=verify-full`) for all clients
Encryption at rest	Storage encryption with customer-managed key `[key alias]`
Column protection	Example: Delivery phone number encrypted in the application with envelope encryption
Row-level security	Example: RLS policy on `tenant_id` for the multi-tenant B2B schema
Masking	Non-production copies use masked data generated by `[tool]`
Auditing	DDL and privilege changes logged; access to `dba_breakglass` alerted
Secrets	Credentials issued dynamically by `[secrets manager]`, lifetime 1 hour

13. Backup, restore and retention

13.1 Backup and restore

Item	Target or method
Recovery point objective (RPO)	Example: 5 minutes
Recovery time objective (RTO)	Example: 1 hour
Method	Continuous WAL archiving plus daily snapshot
Backup retention	35 days point-in-time; monthly snapshot kept 12 months
Backup location	Separate account and region, immutable storage
Restore drill	Quarterly full restore to an isolated environment; time recorded
Last successful drill	YYYY-MM-DD, restore time [n] minutes

13.2 Retention and archival

Data	Retain in primary	Then	Delete after	Basis
`orders`, `order_line`	7 years	Remain (small)	10 years	[Tax record-keeping requirement]
`order_event`	24 months	Partition detached and exported to object storage as Parquet	7 years	[Audit policy]
Application logs in DB	30 days	Delete	30 days	[Policy]
[Data]	[Period]	[Action]	[Period]	[Basis]

14. Migration strategy

Topic	Rule
Tool	Example: Versioned SQL migrations (`V###__description.sql`) applied by the deployment pipeline
Review	Every migration reviewed by a DBA or a second engineer; lock impact stated in the PR
Expand and contract	Add new structures, dual-write, backfill, switch reads, then drop old structures in a later release
Large backfills	Batched (for example 10 000 rows per batch) with throttling, outside the migration transaction
Locking	`CREATE INDEX CONCURRENTLY`; `lock_timeout` set to [3 s] on DDL
Rollback	Every migration has a tested forward fix or down script; destructive steps only after one release in production
Initial data load	[Source, method, validation counts]

15. Capacity and performance estimates

Input	Symbol	Value
Orders per day	$n_o$	Example: 200 000
Lines per order (average)	$l$	2.5
Status events per order	$e$	4
Average row size, `orders` (with index share)	$s_o$	1.2 KB
Average row size, `order_line`	$s_l$	0.4 KB
Average row size, `order_event`	$s_e$	0.25 KB
Horizon	$D$	1 095 days (3 years)

Storage growth per day:

G = n_o \left(s_o + l\,s_l + e\,s_e\right) = 200\,000 \times (1.2 + 1.0 + 1.0)\ \text{KB} \approx 640\ \text{MB/day}

Size after three years, before compression and excluding archived partitions:

S = G \times D \approx 640\ \text{MB} \times 1\,095 \approx 700\ \text{GB}

Peak write rate with a peak factor $k = 5$ :

W_{peak} = \frac{k \cdot n_o (1 + l + e)}{86\,400} = \frac{5 \times 200\,000 \times 7.5}{86\,400} \approx 87\ \text{rows/s}

Resource	Estimate	Provisioned	Headroom
Storage (3 years)	~700 GB	[1.5 TB with autoscaling]	~2x
Peak writes	~90 rows/s	[Instance class]	[x]
Working set in memory	[Hot 30 days of orders and indexes, ~25 GB]	[64 GB RAM]	~2.5x

16. Naming conventions

Object	Convention	Example
Tables	snake_case, singular, except reserved words	`order_line`, `orders`
Columns	snake_case; booleans start with `is_` or `has_`	`is_gift`
Primary key	`<table>_id`	`order_line_id`
Foreign key column	Same name as the referenced key	`order_id`
Timestamps	`<event>_at`, always `timestamptz`	`created_at`
Indexes	`ix_<table>__<columns>`	`ix_orders__customer_id_created_at`
Unique constraints	`uq_<table>__<columns>`	`uq_orders__order_number`
Foreign key constraints	`fk_<table>__<referenced>`	`fk_order_line__orders`
Check constraints	`ck_<table>__<rule>`	`ck_orders__total_non_negative`
Views	`v_<name>`	`v_daily_sales`

17. Appendices

17.1 Open questions

ID	Question	Owner	Due
OQ-01	Example: Do we need `tenant_id` now for the B2B launch?	[@name]	YYYY-MM-DD
[OQ-nn]	[Question]	[@name]	YYYY-MM-DD

17.2 Glossary

Term	Meaning
OLTP	Online transaction processing: many short reads and writes
RPO / RTO	Maximum tolerable data loss / time to restore service
Keyset pagination	Paging by the last seen key instead of an offset
Expand and contract	Zero-downtime migration pattern in additive and subtractive phases
[Term]	[Meaning]

17.3 References

Reference	Use
[DBMS documentation, version]	Types, partitioning, isolation behaviour
[Data dictionary]	Business definitions of each column
[Internal database standards]	Naming and review rules

Standard and licence note

Based on common industry practice for database design documentation: conceptual, logical and physical modelling, normalisation, indexing and operational design. Original text written for this collection; example values are illustrative and not tuning advice for any specific product. Template licence: MIT.