# 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.

<!--
Guidance: How to use this template
- Start from the access patterns (section 5.3), not from the entities. A
  schema that is correct but cannot answer the top ten queries fast is not
  done.
- Record every deliberate deviation from third normal form in section 8 with
  its reason and the mechanism that keeps the copies consistent.
- Keep the physical model (section 6) in sync with the migration files. When
  they differ, the migrations win and this document is updated.
- For a document, key-value or graph database, keep the section structure
  and replace tables and columns with collections, documents and keys.
-->

## Document control

<!-- Guidance: Identify the database, its environments and the owner accountable for its data. -->

| 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

<!-- Guidance: Record schema-level changes and the migration that implements them. -->

| 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` |

## Contents

<!-- Guidance: Keep this list aligned with the headings. -->

1. [Introduction](#1-introduction)
2. [DBMS selection](#2-dbms-selection)
3. [Conceptual model](#3-conceptual-model)
4. [Logical model](#4-logical-model)
5. [Workload and access patterns](#5-workload-and-access-patterns)
6. [Physical design](#6-physical-design)
7. [Keys and indexes](#7-keys-and-indexes)
8. [Normalisation and denormalisation](#8-normalisation-and-denormalisation)
9. [Partitioning and sharding](#9-partitioning-and-sharding)
10. [Transactions, isolation and concurrency](#10-transactions-isolation-and-concurrency)
11. [Data integrity rules](#11-data-integrity-rules)
12. [Security](#12-security)
13. [Backup, restore and retention](#13-backup-restore-and-retention)
14. [Migration strategy](#14-migration-strategy)
15. [Capacity and performance estimates](#15-capacity-and-performance-estimates)
16. [Naming conventions](#16-naming-conventions)
17. [Appendices](#17-appendices)

## 1. Introduction

<!-- Guidance: What data this database holds, for which application, and what it explicitly does not hold. -->

### 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

<!-- Guidance: Justify the engine against requirements, not familiarity. List the options you considered. -->

| 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

<!-- Guidance: Business entities and relationships only, in language the business recognises. No types, no keys beyond identity. -->

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

```mermaid
erDiagram
  CUSTOMER ||--o{ ORDER : places
  ORDER ||--|{ ORDER_LINE : contains
  PRODUCT ||--o{ ORDER_LINE : "is ordered as"
  ORDER ||--o{ PAYMENT : "is paid by"
  ORDER ||--o{ SHIPMENT : "is fulfilled by"
  ORDER ||--o{ ORDER_EVENT : "has history"
```

| 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

<!-- Guidance: Attributes, primary and foreign keys and relationships, independent of the DBMS. Resolve many-to-many relationships here. -->

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

```mermaid
erDiagram
  CUSTOMER_REF {
    uuid customer_id PK
    string display_name
  }
  ORDERS {
    uuid order_id PK
    uuid customer_id FK
    string status
    decimal total_amount
    string currency
    datetime created_at
  }
  ORDER_LINE {
    uuid order_line_id PK
    uuid order_id FK
    string sku
    int quantity
    decimal unit_price
  }
  ORDER_EVENT {
    bigint order_event_id PK
    uuid order_id FK
    string from_status
    string to_status
    datetime occurred_at
  }
  CUSTOMER_REF ||--o{ ORDERS : places
  ORDERS ||--|{ ORDER_LINE : contains
  ORDERS ||--o{ ORDER_EVENT : records
```

## 5. Workload and access patterns

<!-- Guidance: List the queries and writes the database must serve, with frequency and latency targets. Indexes in section 7 must trace back to these rows. -->

### 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

<!-- Guidance: One table per database table: column, type, nullability, default, constraint and meaning. Types must match the DBMS. -->

### 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

<!-- Guidance: Every index costs write time and storage. Justify each one by the access pattern it serves and review unused indexes quarterly. -->

### 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

<!-- Guidance: State the target normal form and list every deliberate exception with the consistency mechanism. -->

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

<!-- Guidance: Partition when a table's size harms maintenance or queries, not by default. State the key, the scheme and how partitions are created and dropped. -->

| 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

<!-- Guidance: State the default isolation level, which operations need stronger guarantees and how conflicting updates are detected. -->

| 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

<!-- Guidance: Business rules the database itself enforces, and those enforced only by the application. Prefer database constraints for rules that must never be broken. -->

| 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:

```mermaid
stateDiagram-v2
  [*] --> PENDING_PAYMENT
  PENDING_PAYMENT --> PAID
  PENDING_PAYMENT --> CANCELLED
  PAID --> SHIPPED
  PAID --> CANCELLED
  SHIPPED --> DELIVERED
  DELIVERED --> [*]
  CANCELLED --> [*]
```

## 12. Security

<!-- Guidance: Least privilege for every role, encryption, and how personal data is protected. Link the security architecture for shared controls. -->

### 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

<!-- Guidance: A backup that has never been restored is a hope. State targets, the method, and the drill schedule. -->

### 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

<!-- Guidance: How schema changes reach production without downtime or data loss. -->

| 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

<!-- Guidance: Show inputs and formulas so reviewers can change an assumption. Round up and say so. -->

| 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

<!-- Guidance: Agree once, enforce in review or with a linter. Consistency matters more than the specific choice. -->

| 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

<!-- Guidance: Open questions, glossary and references. -->

### 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.