In short
Model the product by identifying its durable entities, relationships, states, invariants, and lifecycle rules before choosing tables or writing endpoints. Use the database to enforce truths the product cannot afford to violate, and test the model against change—not only the first release.
Begin with the nouns people use
Listen to how the product is described. Projects contain tasks. Organisations have members. Orders contain line items. Documents have versions and approvals.
Those nouns are candidates for entities, but not every noun deserves a table. An entity earns its place when it has an identity, a lifecycle, or relationships of its own. A shipping address copied onto an order may be a value. A reusable address book entry that can be edited and selected by several users is an entity.
The distinction matters because entities change independently. Values belong to something else.
Give every entity an identity
If the product needs to refer to a thing over time, give it a stable identifier. Names, email addresses, and titles are poor identifiers because they change and may not be unique.
A primary key says which row represents the thing. A unique constraint says which other values may not repeat. These are different jobs. A user can have an internal stable ID while their email remains unique and editable.
Stable identity also keeps URLs, audit logs, integrations, and references intact when human-readable attributes change.
Draw relationships in both directions
"A project has tasks" is only half a relationship. Ask whether a task can exist without a project, whether it can move between projects, and what should happen when its project is archived or deleted.
For every relationship, decide:
- Is it one-to-one, one-to-many, or many-to-many?
- Is the relationship required or optional?
- Who owns the relationship?
- Can it change after creation?
- What happens when either side is deleted?
Many-to-many relationships usually deserve an entity of their own once the relationship carries information. A membership is not merely a connection between a user and an organisation if it also contains a role, invitation state, join date, or notification preference.
Model states explicitly
Products often hide a state machine inside a single status field. Draft, submitted, approved, rejected, cancelled, fulfilled: the labels are easy. The allowed transitions are the real model.
Write down which state can move to which other state, who can cause the transition, and what else must happen atomically. Can an approved invoice return to draft? Can a cancelled order be fulfilled? Does deleting a user cancel their pending approvals?
If any code path can assign any status string, the product has no dependable state model. It has a convention.
Put invariants close to the data
An invariant is something that must remain true regardless of which interface, import, background job, or integration changes the data.
Examples include:
- An email address is unique within an organisation.
- A price cannot be negative.
- Every task belongs to an existing project.
- A membership refers to an existing user and organisation.
- There can be only one active subscription per account.
Use not-null, unique, check, primary-key, and foreign-key constraints for truths the database can enforce. Application validation is still useful for friendly messages, but it should not be the only defence against impossible data.
Decide what deletion means
"Delete" can mean hard deletion, soft deletion, anonymisation, archival, or revocation. Treating these as interchangeable causes some of the most expensive data problems.
For each entity, ask what must remain for financial records, auditability, analytics, collaboration, or legal obligations. Then decide how references behave. Cascading deletion is correct when dependent data has no meaning without its parent. It is dangerous when the dependent record is itself evidence that must survive.
Do not add a universal deleted_at column and call the lifecycle solved. Define the product behaviour that follows deletion: visibility, restoration, uniqueness, search, exports, and integrations.
Test the model against future questions
Before implementation, ask questions the first version does not yet answer:
- Can one person belong to several organisations?
- Can a record move between owners?
- Do we need historical values or only the latest value?
- Can two actions happen concurrently?
- Will customers need imports and exports?
- Which events must be auditable?
This is not speculative architecture. It is a way to find assumptions while changing them still costs a diagram instead of a migration.
Primary references
Common questions
Should the database schema mirror the interface?
No. Screens change with workflows; the data model should represent the durable concepts beneath them. One screen may combine several entities, and one entity may appear across many screens.
Should every rule become a database constraint?
Only rules the database can evaluate reliably and that must hold across every write path. Contextual permissions and complex workflows often belong in application logic, backed by simpler structural constraints.
Is a diagram enough?
No. A useful model also records lifecycle rules, valid state transitions, uniqueness scope, deletion behaviour, and invariants. Boxes and arrows alone leave the consequential decisions unstated.
