Name the nouns and the rules between them.
Decide ownership, cardinality, optionality, and what deletion should do before drawing tables.
Customer1placesNOrder
Databases / Design
Design a database around its rules.
A schema is more than a collection of fields. Make relationships, valid changes, common reads, and the path to the next version explicit.
02
Design
Model the rules. Then measure the queries.
A good schema makes valid changes straightforward, invalid states difficult, and common questions inexpensive.
Give every index a job.
Write the actual query, establish a baseline, add an index, and compare the execution plan and write cost.
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC, id DESC LIMIT 20;Run the PostgreSQL experiment →Assume the model will evolve.
Prefer reversible migrations, observable rollouts, and explicit compatibility windows. Test old and new application versions against the changed schema before rollout.
v1→v2→v3
Keep the source of truth clear.
If you add a search index or cache, document which database owns the record, how updates arrive, and how to rebuild the derived copy.
Primary recordsDerived index
If updates are asynchronous, define acceptable lag and a repair path.
A small example / an orders service
Separate identity from changing facts.
A customer has an identity that multiple orders reference. An order has line items, each with the quantity and price agreed at checkout. Keeping that agreed price on the line item preserves the transaction’s history when the current catalog price changes.
Describe the constraints in plain language first: an order must reference a customer; a line item needs a positive quantity; an external payment event must not be applied twice. Then decide which rules can be enforced with foreign keys, checks, unique constraints, and transactions, and which require coordination with an external service.
The database transaction does not automatically undo a payment request sent to another system. Define intermediate states, idempotent operations, and a reconciliation path for uncertain outcomes.
Explore the orders-service boundaries →Plan the next change
Give migrations a compatibility window.
For a change that affects running application versions, separate introducing the new structure from removing the old one. Decide how existing rows are populated, which version reads or writes each field, and how progress will be observed.
Before production, rehearse with representative data and identify operations that may lock tables, build large indexes, or create extra write load. A rollback plan must say what happens to data written after the change; reversing a migration file alone may not recover it.
Keep evidence from the rehearsal and connect the release plan to a tested backup and restore procedure.
Check the operational prerequisites →