Skip to content

Data & Analytics

Database design: put the rules where the data lives

A job belongs to a customer. An invoice number is used once. A visit ends after it starts. Rules like these can live in application code, in a spreadsheet check, or in the database itself, and only the last one can't be skipped by a new screen or an import script. Good design also decides which questions must be fast, what history is kept and who can see what.
Discuss your systemSee one table, designed ↓
Incoming writesconstraintscustomerspropertiesjobsJ-513visitsinvoicesimportjob J-512 for customer C-0099fk_job_customer · no such customerAPIinvoice INV-2290uq_invoice_number · already usedappvisit 11:00–09:00ck_visit_times · ends before it startsappjob J-513 for C-1042✓ stored

Where a rule should live

Not every rule belongs in the database. The ones that describe what the data is do.
Every job has a customerThe database: a foreign keyNo screen or import can skip it
Invoice numbers are used onceThe database: a unique constraintTwo systems writing at once can't both win
A visit ends after it startsThe database: a checkIt's true of every visit, everywhere
A discount over 15% needs approvalThe applicationIt involves people and context
How revenue is counted for reportsThe data modelIt's a reporting definition, not a storage rule

One table, designed

jobs

key
job_id, generated by the database
belongs to
a customer and a property, both required
status
one of: requested, scheduled, in progress, completed, cancelled
times
scheduled start and end; end must be after start
fast questions
“history at this address”, “today's board by status”
history
every status change kept in job_events, never overwritten
access
technicians see their assigned jobs; the office sees all

Each line is a decision someone would otherwise make by accident. The allowed statuses are the same list the screens show, so a typo can't create a sixth one.

Indexes follow the questions people actually ask. A technician opening an address sees its whole history straight away, because that lookup was designed for.

Five decisions in every design

  1. Keys

    How each row is known

    Generated ids for rows, natural ones (like invoice numbers) kept unique.

  2. Constraints

    What can't be true

    Required links, allowed values and checks that reject bad rows with a reason.

  3. Indexes

    What must be fast

    Built for the questions people ask every day, and no more.

  4. History

    What's kept when it changes

    Status changes and edits recorded, so “who changed this?” has an answer.

  5. Access

    Who sees which rows

    Rules in the database, so every screen and report applies them the same way.

What a database design hands off

In

Domain model
entities and rules
Access patterns
questions that must be fast
Volumes
rows now and in three years
Schema
tables · keys · constraints · indexes

Out

Enforced rules
bad writes refused, with a reason
Fast paths
indexes for common questions
History and access
kept changes, row-level rules

Worth designing properly when the data has to stay right

Good fit

  • You're building a system that becomes the record for jobs, customers or money.
  • More than one screen, import or integration writes the same data.
  • Bad rows are found in reports, weeks after they were written.

Not the right fit

  • A spreadsheet or an off-the-shelf tool already holds the data well.
  • A short-lived prototype. Design the schema when the shape has settled.

Where it's the foundation

Next to database design

Questions about database design

Which database do you use?

Usually PostgreSQL. It handles constraints, history and access rules well, and runs almost anywhere. Starting candidates, not requirements.

Can you fix our existing database?

Often. We find the rules it should enforce, clean the rows that break them, and add the constraints so new bad rows can't arrive.

Doesn't putting rules in the database make changes harder?

Changes become deliberate: a migration that's reviewed and can be rolled back. That's slower than editing a screen, and that's the point.

What about NoSQL?

It fits some jobs, like large volumes of events. Business records that relate to each other usually belong in a relational database.