Database schema design is the discipline of deciding which facts a system preserves, how those facts relate, and which invalid states the data store must refuse. In custom software, the schema becomes part of the product's memory: invoices, permissions, workflow states, customer identity, and audit history outlive individual screens and services. A design that looks elegant in an ORM can still lose meaning when two writers race, a migration is interrupted, or a report reads a field whose timestamp semantics were never agreed.
A durable schema starts with business facts and operational consequences. Identify the actor, the fact being recorded, the authority for that fact, the lifecycle, and the evidence needed to correct it. Then choose keys, relationships, types, constraints, indexes, transaction boundaries, retention, and migration steps. This sequence lets a team move quickly without hiding important rules in a collection of controllers and background jobs.
For connected implementation concerns, read Edilec's Node.js APIs for custom software, background jobs field guide, and technical debt checklist. A schema is reliable only when its interfaces, asynchronous writers, and maintenance work agree on the same facts.
Define what each row means
Write a one-sentence grain for every important table: one row per invoice, one row per invoice line, one row per entitlement decision, or one row per state transition. If a table mixes events and current state, make that choice visible or split the representations. Use stable identifiers at the system boundary and preserve external references separately. A customer ID issued by a partner is not automatically a good primary key for your system because the partner may merge, reuse, or redact it.
Separate facts that change from facts that explain history. An invoice line should retain the price, tax treatment, and description that applied when it was issued rather than re-reading the current product catalog. Use effective time, recorded time, and processing time for different questions. A status changed yesterday but loaded today should not be reported as a business event occurring today merely because the pipeline was late.
| Design question | Good schema evidence | Risk when omitted |
|---|---|---|
| Row grain | A plain-language statement and representative row | Aggregates count mixed facts |
| Identity | Stable local key plus external reference | Imports duplicate or overwrite records |
| Relationship | Foreign key and cardinality decision | Orphaned or ambiguous records |
| Time | Named business and system timestamps | Reports shift when ingestion is late |
| Lifecycle | Allowed states and transitions | Support guesses what a status means |
Put invariants close to the data
Use the database to enforce rules that must hold for every writer. PostgreSQL constraints cover primary keys, unique values, foreign keys, checks, and not-null requirements. The exact features differ by engine, but the design principle is portable: a rule that protects identity, referential integrity, or an impossible state should not depend on every code path remembering a helper function. Application validation remains useful for friendly messages; it should not be the only guard against contradictory rows.
Be precise about nullability and uniqueness. Unknown, not applicable, not yet calculated, and intentionally blank are different states. A partial unique rule may be right for an active record but wrong for retained history. Foreign-key actions need review because cascade deletion can remove evidence that finance or support still needs. For SQLite-backed tools, the foreign-key documentation is a useful reminder that enforcement can depend on connection settings and must be tested in the runtime configuration, not assumed from the migration file.
Design for concurrency and recovery
Most schema failures are not visible in a single-user demo. Two requests can both observe an available seat, reserve the same coupon, or insert the same external payment reference unless the transaction and constraint boundary prevents it. PostgreSQL transaction guidance explains the commit and rollback model; your design must add the business expectation: which conflicts should retry, which should return a clear rejection, and which need a human review queue.
Make repair a first-class capability. Keep an immutable event or audit record where the business requires history, store the source reference needed to reprocess a failed import, and mark correction authority rather than editing rows invisibly. A repair operation should be idempotent, scoped, logged, and safe to pause. If a data quality rule cannot be enforced immediately, record the exception and its owner instead of weakening every constraint for the sake of one problematic record.
| Failure case | Schema or transaction control | Operational follow-up |
|---|---|---|
| Duplicate external event | Unique source reference | Dead-letter and safe replay path |
| Two competing updates | Version or conditional write | Conflict message and owner |
| Missing parent record | Foreign key or staged intake | Quarantine until authority arrives |
| Partial workflow | Explicit pending state and transaction | Resume or compensate by operation ID |
| Corrupted import | Validation boundary and batch marker | Rollback batch without hiding history |
Choose indexes for real access paths
An index is a promise about a query pattern and a write cost. Start from the reads people actually perform: tenant plus status, account plus effective date, or external ID lookup. PostgreSQL indexes can accelerate retrieval, but each index consumes storage and adds work to inserts and updates. Do not add indexes because a column looks important. Use query plans, cardinality, selectivity, and production-like volume to decide, then monitor whether the index remains useful as data distribution changes.
Keep derived summaries rebuildable. A materialized count or search projection can make a user-facing page fast, but it should have an authoritative source, refresh behavior, lag signal, and reconciliation check. Do not let a cached projection silently become the only place that records a business fact. If a report needs a denormalized shape, document its grain and refresh timestamp so readers know whether they are looking at current state, an event view, or an approximation.
Evolve the schema with expand and contract
A safe migration separates structural change from behavioral cutover. Add the new column or table, deploy a writer that can populate both representations, backfill in bounded batches, compare old and new results, switch readers, observe, and remove the old path only after the compatibility window. PostgreSQL table modification guidance describes engine behavior; you still need to estimate lock time, index build cost, replication impact, and recovery time for your data volume.
Every migration needs a pause condition. Stop when error rate, lock wait, replication lag, row counts, checksum comparisons, or business reconciliation crosses the agreed boundary. A rollback may mean restoring the old reader, stopping the backfill, or restoring from a backup; it may not be possible to unwrite every transformed row. State that boundary before deployment and rehearse it against a realistic copy. Treat irreversible data deletion as a separate approval from additive schema work.
Protect the schema through its interfaces
Secure data modeling includes how callers reach the data. Apply least privilege to application roles, migrations, reporting users, and support tools. Use parameterized queries and safe query builders; the OWASP SQL Injection Prevention Cheat Sheet explains why binding values is stronger than escaping strings. Separate sensitive fields where access, retention, or encryption requirements differ, and avoid copying protected data into logs, analytics exports, or test fixtures without a purpose and control.
Record schema and permission changes as deployable artifacts. A database administrator's manual edit may fix a live symptom while leaving the next environment or recovery image inconsistent. Review who can run migrations, who can approve emergency changes, and how the system proves which version is active. Security review should include retention, deletion, backups, replicas, indexes, and derived stores because a column removed from the primary table can remain in exports and snapshots.
Review the model with product and operations
A schema review should walk one normal journey and several uncomfortable ones: duplicate submission, delayed dependency, correction after close, permission change, retention expiry, and restore after failure. Ask which table is authoritative, which rule rejects the invalid state, how a support engineer finds the record, and how the team repairs it without losing history. Include reporting and integration owners because they often expose timestamp, deletion, and cardinality assumptions that application code can hide.
Keep the decision record with migrations, test data, query plans, and reconciliation evidence. When the model changes, update diagrams and examples that explain grain rather than maintaining a picture that no longer matches the database. Review high-value constraints and indexes after real volume arrives. The goal is not to prevent all evolution; it is to make the cost, behavior, and recovery path of evolution legible.
Key takeaways for database schema design
- State the grain, authority, lifecycle, retention, and timestamp meaning for each important record.
- Use stable local identity and preserve external references without confusing the two.
- Enforce cross-writer invariants with constraints, transactions, or explicit conflict handling.
- Choose indexes from measured access paths and monitor their write and storage cost.
- Use observable expand-and-contract migrations with bounded backfills and a pause condition.
- Protect sensitive data across primary, replica, backup, export, test, and logging paths.
- Keep repair, reconciliation, restore, and deletion as designed capabilities rather than emergency scripts.
Database schema design questions
Should every business rule be enforced in the database?
Rules that must hold for every writer belong as close to the data as the engine can safely enforce them. External policy, workflow orchestration, and user guidance may stay in services, but their assumptions should be explicit and their failure or repair path recorded. Do not duplicate a critical invariant only in UI validation.
When is denormalization reasonable?
After the normalized facts and measured query need are understood. A summary or projection is reasonable when its source, grain, freshness, rebuild process, reconciliation check, and owner are clear. It becomes risky when teams write to it directly and stop knowing which representation is authoritative.
How should a team make a risky schema change?
Separate the structural change, compatibility period, data movement, reader switch, verification, and cleanup. Test against realistic data, measure locks and lag, define the stop condition, and rehearse the recovery boundary. A migration plan is incomplete when it only says how to finish.
Conclusion: a schema that can evolve
Good database schema design preserves business meaning while making invalid states difficult to create and safe change possible. Model the facts before the screens, enforce the rules that every writer must honor, design for concurrency and repair, and evolve through observable compatibility steps. When constraints, migrations, indexes, permissions, and recovery all reflect the same grain, custom software can grow without turning its database into an undocumented second application.