Database schema design is the practice of deciding which business facts a system can store, how those facts relate, and which invalid states the database should refuse to represent. It is not a naming exercise or a translation of classes into tables. A good schema makes authority, identity, lifecycle, and invariants visible to every writer, including a repair script or background job that arrives months later. The PostgreSQL data-definition documentation is a useful primary reference for the mechanics. This guide focuses on the reasoning around those mechanics: model meaning first, encode durable rules, design for measured access, and migrate in a way operators can verify.
Name the business facts
Write sentences the business would recognize before drawing tables. A subscription belongs to an account; an invoice records a chargeable period; a shipment has a destination and a lifecycle; a user may hold a role in a workspace. Identify which nouns are durable entities, which are events, and which are derived views. Give each fact an owner and a correction path. Avoid storing a mutable display label as the only identity of a record. If the same fact appears in two systems, decide which one is authoritative and whether the second copy is a cache, projection, or independently owned record.
| Schema question | Example decision | Why it matters |
|---|---|---|
| Identity | Invoice has an immutable internal key and unique account-period number | References survive display changes |
| Lifecycle | Draft, issued, paid, void are explicit states | Queries and transitions do not infer meaning |
| Relationship | Invoice belongs to one account and may reference an order | Ownership and deletion behavior are clear |
| Authority | Billing service owns amount and settlement state | Other systems do not silently overwrite truth |
| Correction | Adjustment records explain a financial change | Repair preserves auditability instead of rewriting history |
Make invariants hard to violate
An invariant is a condition that should remain true regardless of which code path writes the data. The PostgreSQL constraints documentation covers primary keys, unique constraints, foreign keys, check constraints, and not-null requirements that can express many such rules. Use a primary key for identity, a unique constraint for business uniqueness where its scope is known, a foreign key when a relationship must point to an existing row, and a check constraint for a row-local condition. Use a transaction for a rule that spans multiple writes. If a rule is only in a comment, every future writer must remember it perfectly.
Use nullability to state meaning
A nullable column should have a deliberate interpretation. Does null mean unknown, not applicable, not yet calculated, or intentionally removed? If those states lead to different decisions, model them explicitly instead of asking every query to guess. Avoid default values that make an absent fact look like a real one, such as a zero amount or a fabricated date. When a value becomes required, migrate existing records and writers in stages. The schema should make a bad state visible at the point it is created, not only after a report or workflow produces a surprising result.
Model relationships and deletion deliberately
Choose whether a relationship is required, optional, one-to-one, one-to-many, or many-to-many, then decide what happens when the parent changes or is removed. Cascading deletion may be correct for private child records and dangerous for financial or audit history. Soft deletion can preserve history but creates filters, uniqueness, and restore rules that must be explicit. A join table can express membership and its attributes more clearly than a list stored in one column. Test concurrent creation, deletion, and reassignment so the relationship remains valid when two writers act close together.
Choose indexes from real access patterns
An index is a performance decision with storage and write costs, not a reward for adding a column to a table. List the predicates, joins, ordering, and pagination that users actually trigger, then inspect query plans and representative data. The PostgreSQL indexes guide explains the database's index role; the local decision must account for selectivity, update frequency, size, and maintenance. An index that helps a rare report may slow every write. Measure before and after, and remove unused structures only after confirming that a hidden job or support query does not depend on them.
| Access pattern | Possible design | Check before release |
|---|---|---|
| Find account's recent invoices | Account key plus issued-time ordering | Plan, selectivity, and pagination stability |
| Enforce active membership | Scoped unique rule with lifecycle state | Concurrent insert and restore behavior |
| Search by external reference | Unique or selective index if governed | Normalization and collision handling |
| Process pending work | State plus claim-time index | Worker contention and stale claims |
| Archive by period | Partition or time-oriented strategy when justified | Retention, query plan, and operational complexity |
Use transactions for a complete decision
A transaction should cover the writes that must appear as one valid state transition. The PostgreSQL transaction tutorial shows the all-or-nothing property that protects multi-step changes. Keep the transaction boundary neither so small that half a business decision becomes visible nor so broad that locks and retries become unmanageable. Define isolation, conflict handling, and retry behavior for concurrent work. A transaction cannot make an external email, payment, or queue publish atomic with a database row; use an outbox, reconciliation, or an explicit pending state for that cross-system boundary.
Migrate in expand, backfill, contract stages
A safe schema migration lets old and new application versions coexist while the data moves. Add a compatible column or table, deploy writers that can handle both forms, backfill in bounded batches, validate counts and meaning, switch reads, then remove the old structure after rollback is no longer needed. A backfill needs a stable selection rule, progress marker, rate limit, resume behavior, and verification query. Do not add a non-null constraint to dirty data simply because the desired end state is correct. State the lock, storage, replication, and customer-impact assumptions before running the migration.
Rehearse a status change
Suppose an order gains a cancelled state while reports and workers still recognize only open and closed. Add the state and transition rule, make readers tolerate it, update workers to stop processing cancelled orders, backfill historical cases only when the business meaning is certain, and compare report totals before and after. Test a concurrent cancellation and fulfillment attempt, a retry after a timeout, and a rollback where the new state is already present. The related database schema design checklist can extend this exercise with operational verification.
Operate data quality as well as database health
Monitor constraint violations, long transactions, deadlocks, replication or replica lag, storage growth, failed migrations, backfill age, query latency, and index use. Pair infrastructure signals with data checks: orphan count, duplicate business identifiers, invalid state transitions, reconciliation mismatch, and records stuck in a pending state. Give each table or aggregate an owner and a correction runbook. A manual repair should preserve authority and audit history rather than writing a second truth into a support spreadsheet. The related background jobs guide is useful when backfills, projections, or cleanup work run asynchronously.
Review schema changes in business language
Ask what new fact or invariant the change introduces, which writers can create it, which readers need it, what happens to old rows, how queries will find it, and how the team will correct a mistake. Include product or operations when a field changes a visible status, financial result, permission, or retention rule. Keep the design record next to the migration and verification queries. Review a change with someone who did not write it; if they cannot explain the source of truth or the safe rollback, the schema may be technically valid but operationally incomplete.
Rehearse restore and correction, not only migration
A schema is part of the recovery surface. For a high-impact change, rehearse restoring a representative backup or snapshot, applying the migration, replaying allowed writes, and comparing critical counts and invariants with the authoritative source. Include access to the restored data, application compatibility, indexes, constraints, and the steps for returning service to normal. A restore that starts successfully but cannot reconcile a payment, membership, or job state is not enough evidence. Keep the correction owner and communication path visible so data repair does not become an improvised second system.
Separate correction from rewriting history
When data is wrong, decide whether to correct the record, append an adjustment, rebuild a derived view, or restore from an authoritative source. Financial, permission, and audit facts often need an explanatory adjustment rather than a silent overwrite. A repair script should be idempotent, bounded, logged, reviewed, and safe to stop and resume. Test it against a copy with duplicate, missing, and partially migrated rows. The caching strategy guide is relevant when a stale derived representation must be invalidated after the source is repaired.
| Recovery question | Pass condition | Evidence |
|---|---|---|
| Can the database restore? | Representative data opens with required access | Restore log and permission test |
| Are invariants intact? | Constraints and business checks pass | Violation and reconciliation report |
| Can the application resume? | Old and new readers handle restored state | Smoke workflow and migration marker |
| Can correction stop safely? | Script resumes without duplicate effects | Checkpoint, audit, and rollback note |
| Who communicates? | Customer and internal owners are named | Incident and support update path |
Run this rehearsal after the design is approved and before the migration is considered routine. It gives the team a practical answer to the question customers care about: if the schema change creates a bad state, how will the organization find the authoritative fact, limit further harm, and restore a trustworthy result?
Database schema design takeaways
- Model business facts, identity, lifecycle, authority, and correction before choosing columns.
- Use constraints and transactions to make durable invariants hard to violate.
- Give nullability and deletion a business meaning instead of leaving them to query conventions.
- Select indexes from measured access patterns and account for their write and maintenance cost.
- Migrate additively, backfill visibly, verify meaning, and remove old structure only when recovery is safe.
Database schema design FAQ
Should every schema be fully normalized?
Normalize enough to keep authority and updates clear, then denormalize only for a measured access pattern or reporting need. A derived copy brings synchronization, invalidation, and repair responsibilities that should be named rather than hidden.
Should validation live in the application or database?
Rules that must hold for every writer belong as close to the database as practical, often as constraints or transactions. Contextual workflow checks may live in application code, but document and test them, and do not assume one code path is the only writer.
When should a new index be added?
Add one for an important or growing access pattern after reviewing the predicate, ordering, data distribution, write cost, and query plan. Verify it with representative data and remove it only after checking less-visible jobs and support queries.
Conclusion: make data meaning durable
Database schema design is successful when the stored facts remain understandable and valid as the application changes. Name authority and invariants, encode durable rules, measure access patterns, migrate in compatible stages, and keep correction evidence. That approach gives engineers a dependable foundation for features while giving operators a way to explain and repair the data when real systems behave imperfectly.