Data Modeling and Database Selection
Choose storage technologies from access patterns, data shape, transaction needs, and consistency requirements.
Relational vs. Document Data Modeling
Relational databases model data through tables, keys, constraints, and relationships. They are strong when transactions, joins, integrity constraints, and expressive queries matter. Document databases store nested objects and can fit aggregate-shaped records with evolving fields and access patterns centered on the document.
Do not choose a document database merely because the application uses JSON. Model the lifecycle and relationships. Embedding a user's settings with the user may be convenient when read and updated together; embedding millions of independently growing events in one document is usually a poor fit.
Normalize to reduce duplication and preserve integrity, or denormalize deliberately to optimize reads. Denormalization creates update obligations: if a product name is copied into orders, decide whether historical orders should preserve the purchased name or reflect current catalog data.
- Relational strengths:
Constraints, joins, multi-row transactions - Document strengths:
Aggregate-shaped data, flexible schema, nested reads - Decision input:
Access patterns and correctness requirements
- Choose based on access patterns and integrity needs.
- Embedding and normalization are trade-offs, not moral rules.
- Document the source of truth when data is duplicated.
Why is a database unique constraint important when creating usernames?
Indexes, Query Plans, and Data Access Patterns
An index is an additional data structure that helps the database locate records without scanning every row or document. It speeds selected reads but consumes storage and adds work to writes and updates. The correct index depends on filter predicates, sort order, cardinality, and query shape.
For a query filtering by status and sorting by createdAt, a compound index such as { status: 1, createdAt: -1 } may help. Field order matters: an index is not a universal accelerator for every query. Inspect execution plans and examine examined-versus-returned records, sort stages, and index usage.
Avoid creating indexes based on guesses alone. Use representative production-like data, observe slow queries, and test changes against realistic distributions. Indexes can become ineffective when predicates are not selective or when the query requests a large fraction of the collection.
db.orders.createIndex({ status: 1, createdAt: -1 });
db.orders.find({ status: "PAID" })
.sort({ createdAt: -1 })
.limit(50)
.explain("executionStats");- Indexes trade write cost and storage for faster access paths.
- Compound index field order is important.
- Use execution plans rather than assuming an index is being used.
Transactions, Constraints, and Data Integrity
A transaction groups changes into a unit with defined atomicity and isolation behavior. Relational systems commonly provide multi-row transactions; document databases vary in transaction scope and cost. A transaction is useful when related state must change together, such as debiting one account and crediting another.
Constraints such as unique keys, foreign keys, and check constraints protect invariants at the data boundary. Application-level checks alone can race: two requests may both observe that a username is free and then attempt to insert it. A unique constraint is the final authority.
Keep transactions short and avoid slow network calls inside them. For workflows spanning independent services, a single database transaction may not be available; use patterns such as sagas, reservations, compensating actions, and reconciliation instead of pretending the whole workflow is atomic.
Write down what must never become false—for example, available inventory cannot be negative. Then choose constraints, transactions, and concurrency controls that enforce it.
- Put critical invariants close to the data they protect.
- Unique constraints prevent check-then-insert races.
- Cross-service workflows need explicit recovery behavior.