§ CAPABILITY

Database Architecture and Data Modeling

The schema the rest of the system is built on. Ownership, constraints and a migration path, decided before the model has a hundred callers and becomes expensive to change.

Who
Delivered by a senior team assembled for the engagement, against a defined scope.
Request a call How the work runs

What you're seeing

The same rule is enforced in three places and one of them is wrong.
Usually means Integrity lives in application code that not everything goes through. Migrations, admin tools and console sessions all write directly, and one of them has been writing rows the rules would have rejected.
A schema change is discussed for weeks and never made.
Usually means There is no reversible migration path, so every structural change is a one-way decision made under fear. The model then freezes and the workarounds accumulate above it.
The tables mirror the current screens.
Usually means The model was derived from the interface rather than from the domain. Screens change more often than domains do, and each redesign now requires a migration.
Query performance degraded and nobody knows which query.
Usually means There is no measurement of the query patterns the application actually produces, so indexing is being done defensively. Defensive indexes cost write throughput and frequently buy nothing.

Model the domain, not the screens

The tables should describe how the business works, not how the current interface is arranged.

This is the single decision with the longest half-life in a codebase. Interfaces are redesigned every year or two; the underlying domain — what a customer is, what an order is, what a subscription is — changes very rarely. A model derived from screens requires a migration with each redesign, and each migration is harder than the last because there is more data to move.

The sessions that produce a good model are with people who understand the business rather than with engineers, which is the main practical obstacle. They are also short: two or three conversations is usually enough to find the entities and the relationships that were being approximated.

Constraints belong in the database

Application code is not the only thing that writes to your tables.

Migrations write. Admin tooling writes. The console session somebody opened at eleven at night to unblock a support ticket writes. A rule enforced only in a service layer applies to exactly the paths that go through that layer, which is fewer paths than anyone believes.

Adding a constraint to an existing table frequently fails on the first attempt because there is already data violating it. That is not an argument against the constraint. It is the count of how long the rule has not really been a rule, and it is usually a number that surprises the team.

Indexes from measurement

Every index costs write throughput and storage, and defensive indexes added at design time frequently serve no query the application ever issues.

Measuring the query patterns the application actually produces gives you fewer indexes doing more work. It also surfaces the more useful finding: the query that cannot be fixed with an index because the model is wrong for it. That is a different conversation, and it is worth having before someone adds a fourth composite index instead.

Migrations that go both ways

A migration coupled to a deploy makes rollback impossible at exactly the moment it is needed.

Expand-contract is the standard answer and it works: add the new structure, backfill, switch the application to read from it, verify under real traffic, and remove the old structure in a later release. Each step deploys independently and reverts independently.

The cost is more releases for one logical change. The return is that structural change stops being something the team avoids, which is what prevents the workaround layer from forming above a model everyone knows is wrong.

Where it sits

This capability sits under Software Development, where the model determines most of what the build will cost, and under API Integration, where the identity and ownership questions between two systems are data model questions before they are integration ones.

Its two neighbours are Backend & API — the interface is a view onto this, and the two are designed together or neither is coherent — and Architecture Review, where a surprising share of architectural constraints turn out to be data model constraints under a different name.

How the work runs

  1. Model the domain as it is

    Entities and relationships from how the business actually works, not from the shape of the current screens. Screens change more often than domains.

  2. Put integrity in the database

    Foreign keys, uniqueness, check constraints and not-null where they belong. The database is the last line that everything writes through.

  3. Index against real queries

    From the query patterns the application produces, measured. Indexes added defensively cost write throughput and buy nothing.

  4. Make migrations reversible

    Every migration with a tested rollback, and expand-contract for changes that cannot be applied in one step without downtime.

What arrives

  • A documented schema with the reasoning for each boundary
  • Integrity constraints enforced in the database
  • An indexing strategy derived from measured query patterns
  • Reversible migrations with a zero-downtime path for breaking changes

What it costs your team

Two or three sessions with whoever knows the domain best, usually not an engineer.

Next step

Tell us where this is breaking and we'll say what it takes to fix.

Request a call

How we decide

  • The domain is modelled, not the screens

    Costs It requires sessions with people who understand the business rather than with engineers, and their time is harder to get.

    Interfaces change every year or two; the underlying domain rarely does. A model derived from the current screens requires a migration with every redesign, and the migrations get progressively harder as data accumulates. Modelling the domain is more work once and considerably less work repeatedly.

  • Integrity constraints go in the database

    Costs Adding one to an existing table often fails first time, forcing a data cleanup before the constraint can exist at all.

    The database is the last thing everything writes through, and it is the only layer that cannot be bypassed by a migration, an admin tool or a console session during an incident. The cleanup a new constraint forces is not an obstacle — it is the count of rows that were already violating a rule everyone believed was enforced.

  • Indexes come from measured query patterns

    Costs It means waiting for real query data rather than adding indexes at design time.

    Every index costs write throughput and storage, and indexes added defensively at design time frequently serve no query the application actually issues. Measuring first produces fewer indexes that do more, and it identifies the queries that need a model change rather than an index.

  • Every migration has a tested rollback, and breaking changes use expand-contract

    Costs It roughly doubles the number of deployments a structural change requires.

    A migration coupled to a deploy makes rollback impossible exactly when it is needed. Expand-contract — add, backfill, switch, remove — makes each step individually reversible. The cost is more releases; the return is that structural change stops being a decision people are afraid to make.

Frequently Asked Questions

Deciding what things exist in your domain, how they relate, which facts are true about each, and where those facts live. It is the foundation the rest of the system is built on, which is why it is expensive to change later — by then there are a hundred callers holding assumptions about it.
Postgres unless there is a specific reason not to. It handles relational data, JSON documents, full-text search and geospatial work adequately, which means one system to operate, back up and hire for instead of three. Reaching for a specialised store is a decision that should be forced by a requirement, not by preference.
The database, for anything that must be true. Application code is not the only thing that writes to your tables — migrations, admin tooling and console sessions all bypass it. A rule enforced only above the database is a convention, and conventions are what a one-off script ignores.
When analytical queries start affecting transactional performance, or when the questions being asked require a shape the operational model cannot serve without contortion. Before that, a read replica is usually sufficient and far cheaper to operate.
Expand-contract. Add the new structure alongside the old, backfill it, switch the application to read from it, verify, and remove the old structure in a later release. Each step is individually deployable and individually reversible, which is what makes the change safe rather than merely careful.

Sources

Page reviewed