Design Template Collection — Introduction to Database Design

This page collects checklists and templates for putting the knowledge from this guide to use in practice. Use them when designing or reviewing a new project.

Naming Convention Checklist

Table Names

Write in snake_case (user_profiles; NG: UserProfiles)
Plural (users; NG: user)
Use English business terms (orders; NG: tbl_注文)
Keep it concise, within 2-3 words (order_items is fine; customer_order_line_item_details is verbose)
Don't use prefixes like tbl_ or m_ (in the RDB world, it's obvious that users is a table)

Column Names

Write in snake_case
The primary key is id (self-contained within the table); a foreign key is <referenced_table_singular>_id (user_id, etc.)
Booleans start with is_ / has_ / can_ (is_active, has_subscription, can_edit)
Date-times end in _at (created_at, updated_at, deleted_at)
Dates end in _date (birth_date, hire_date)
Counts and amounts get a name that reflects the type (item_count, total_amount)
Avoid unclear abbreviations (flg → is_published, cnt → view_count)
Avoid Japanese (DBMS / tool compatibility issues)

Index Names

Standardize on the convention idx_<table>_<column> (idx_users_email, idx_orders_user_created)
A UNIQUE constraint is uq_<table>_<column> (uq_users_email)
List a composite index's columns in order (idx_orders_user_created = (user_id, created_at))

Standard Column Naming

Purpose	Recommended name	Description
Primary key	`id`	Unique within the table
Foreign key	`<table_singular>_id`	`user_id`, `category_id`
Creation timestamp	`created_at`	When the record was created
Update timestamp	`updated_at`	When the record was updated
Deletion timestamp (logical delete)	`deleted_at`	NULL = not deleted
Active flag	`is_active`	Boolean
Display order	`display_order` or `sort_order`	INTEGER
Creator	`created_by`	The creating user's ID

Key-Design Checklist

Every table has a primary key (a table with no PK is a red flag as a general rule)
The primary key is a hybrid (surrogate key = PK + natural key = a UNIQUE constraint)
The surrogate key is SERIAL / BIGSERIAL / UUID (use a UUID if you don't want it guessable)
Default the primary key's type to BIGINT (BIGSERIAL) (avoids the risk of INTEGER running out at about 2.1 billion)
Set foreign-key constraints (REFERENCES)
Make ON DELETE explicit: CASCADE if it's dependent on the parent, RESTRICT otherwise (the safe default)
Consider an index on foreign keys too (PostgreSQL doesn't add one automatically; MySQL/InnoDB does)
Use a composite primary key on a many-to-many junction table (a surrogate key otherwise)

Normalization Checklist

First Normal Form

Every cell holds an atomic value (not CSV or a JSON array)
No repeating groups (column naming like child_name_1, child_name_2)
Has a primary key that uniquely identifies each record

Second Normal Form

No attribute is determined by only part of a composite primary key (eliminating partial functional dependency)

Third Normal Form

No non-key attribute is determined by another non-key attribute (eliminating transitive functional dependency)

Denormalization Decisions

Not storing a value derivable by computation (subtotal = quantity * unit_price doesn't need to be stored)
A snapshot is stored deliberately (like the unit price at order time, for chronological accuracy)
If you use an aggregation table, it can be re-synced from the source data (via a batch job or materialized view)

Constraints Checklist

NOT NULL on required fields
CHECK guarantees a value's range or enumeration (CHECK (age >= 0), CHECK (status IN ('active', 'inactive')))
UNIQUE guarantees business-level uniqueness (email, code, etc.)
FOREIGN KEY guarantees referential integrity
DEFAULT sets an initial value (is_active BOOLEAN NOT NULL DEFAULT TRUE)

Index Checklist

For the rationale and design thinking behind each item, see Chapter 19: Introduction to Index Design.

Index columns commonly used in a WHERE search
Index columns used in a JOIN condition (= foreign keys)
Index columns commonly used in ORDER BY
Be conscious of column order in a composite index (can you filter progressively from the left?)
Drop indexes you don't need (unused, or hurting write performance)
Make use of partial indexes (with a WHERE condition) (WHERE deleted_at IS NULL, etc.)

Authentication and Authorization Checklist

Hash passwords with bcrypt / Argon2 (MD5/SHA-256 are NG)
Use parameterized queries against SQL injection
Sessions use a cookie + DB / Redis (JWT alone is hard to invalidate)
MFA's secret is stored encrypted
Account lockout is OWASP/NIST compliant (progressive delay + CAPTCHA; 5 failures / 30 minutes is a DoS risk)
The audit log is append-only (REVOKE UPDATE, DELETE)
OAuth uses Authorization Code Flow + PKCE (Implicit Flow is NG)
The ID Token is verified with a JWT library (rolling your own is NG)

Deletion Strategy Checklist

Choose a deletion strategy based on the nature of the data.

Data type	Recommended strategy	Example
Personal data	Physical deletion or full anonymization	GDPR / personal-data-protection law
Business history	Logical deletion (`deleted_at`)	Orders, sales (7-year tax retention)
Short-lived data	Physical deletion	Cart, session
Audit log	Don't delete (archive)	Legal compliance

There's a procedure for handling a personal-data deletion request
For a table using logical deletion, there's a mechanism that includes WHERE deleted_at IS NULL in queries (like an ORM global scope)
The blast radius of a CASCADE delete is checked (a customer deletion wiping out their orders, etc., is NG)

Design-Review Criteria (For Team Reviews)

Key Design

Does every table have a primary key?
Is the choice between a surrogate key and a natural key appropriate?
Are foreign-key constraints set?

Normalization

Did it reach third normal form?
Is deliberate denormalization documented in a comment?
Are columns that should be snapshotted (like the unit price at order time) actually stored?

Constraints

Are NOT NULL / CHECK / UNIQUE / FOREIGN KEY set appropriately?
Is the ON DELETE policy (CASCADE / RESTRICT / SET NULL) clear?

Indexes

Are there indexes on columns used in searches?
Are there any unnecessary indexes?
Is the composite index's column order appropriate?

Security

Are passwords hashed?
Is sensitive information (personal data, tokens) encrypted?
Are operations that need an audit log actually being recorded?

Operations

Is the deletion strategy clear (physical / logical / archive)?
Is there a plan for GDPR / personal-data-protection compliance?
Are backups and disaster recovery accounted for?
Are naming conventions consistent across the project?

A Design-Document Template

This is a minimal template for documenting a new project's database design.

# Database Design Document — [System Name]

## Overview

- The system's purpose: ...
- Main use cases: ...
- Expected data scale: XXX0k users / XXX daily records

## Entity List

| Entity | Kind | Role |
|:----|:----|:----|
| users | Master | System users |
| products | Master | Product master |
| orders | Transaction | Order history |
| ... | ... | ... |

## ER Diagram

```mermaid
erDiagram
    USERS ||--o{ ORDERS : places
    ...
```

## Table Definitions

### users

| Column | Type | Constraint | Description |
|:----|:----|:----|:----|
| id | SERIAL | PRIMARY KEY | Surrogate key |
| email | VARCHAR(255) | UNIQUE NOT NULL | Email (business key) |
| ... | ... | ... | ... |

### orders

| Column | Type | Constraint | Description |
|:----|:----|:----|:----|
| id | BIGSERIAL | PRIMARY KEY | Surrogate key |
| user_id | INTEGER | NOT NULL REFERENCES users(id) | The purchaser |
| total_amount | NUMERIC(12,2) | NOT NULL CHECK > 0 | Total amount |
| ... | ... | ... | ... |

## Main Query Patterns

### A user's order history
```sql
SELECT o.* FROM orders o WHERE o.user_id = $1 ORDER BY o.order_date DESC;
```
- Index: composite `(user_id, order_date)`
- Expected frequency: on my-page display (XXX per second)

## Deletion Strategy

| Table | Strategy | Reason |
|:----|:----|:----|
| users | Logical deletion (`deleted_at`) | Referential integrity with order history |
| orders | Logical deletion | 7-year tax retention |
| sessions | Physical deletion (TTL) | Short-lived data |

## Security

- Password: hashed with bcrypt (cost 12; OWASP's recommended floor is 10)
- Session: Redis, 24-hour expiration
- Audit log: an `audit_logs` table, with `REVOKE UPDATE/DELETE` applied

## Performance and Scalability

- Expected peak: XXX per second
- Indexing strategy: ...
- Partitioning strategy: ... (state "not needed" explicitly if you're not adopting one)

## Future Extension Points

- Multi-language support: if `products.name` needs to be localized, add a `product_translations` table (not implemented currently)
- ...

Customizing this template

Add or remove sections based on your project's scale and requirements. As a "write at least this much" perspective:

The entity list (an overview of what tables exist)
The ER diagram (visualizing relationships)
Column definitions for the main tables (types, constraints, descriptions)
The deletion strategy (preventing implementation-time pitfalls)
The security policy (review criteria)

Glossary — a quick-reference dictionary of technical terms
Back to the top of the guide — an overview map of this guide

Naming Convention Checklist​

Table Names​

Column Names​

Index Names​

Standard Column Naming​

Key-Design Checklist​

Normalization Checklist​

First Normal Form​

Second Normal Form​

Third Normal Form​

Denormalization Decisions​

Constraints Checklist​

Index Checklist​

Authentication and Authorization Checklist​

Deletion Strategy Checklist​

Design-Review Criteria (For Team Reviews)​

Key Design​

Normalization​

Constraints​

Indexes​

Security​

Operations​

A Design-Document Template​

Related References​