Database Design in New Zealand

Design follows the questions you will ask

A database is not a filing cabinet. It is shaped by what you will ask of it, and the most common design failure is a model that stores everything beautifully and answers nothing quickly.

Before anyone draws a diagram, write down the questions the system has to answer:

• "Show me every open job for this customer, newest first."
• "What did we invoice last month by product line?"
• "Which of these two thousand records changed since Tuesday?"
• "Who touched this record, and what did it say before?"

Those questions decide the keys, the indexes and how history is kept. A designer who asks for them is doing the job. One who asks only what you want to store is drawing a picture.

Constraints are the only thing that holds

The application will be rewritten. An integration will arrive that nobody planned for. Someone will run a script at 11pm to fix something urgent. Through all of that, the only rules that survive are the ones the database itself enforces.

What belongs in the schema:

• Foreign keys — so an order cannot reference a customer that does not exist.
• Unique constraints — so you do not end up with the same invoice number twice.
• Not null on the fields that genuinely must have a value.
• Check constraints for the rules that are always true — a quantity is not negative, an end date is not before a start date.

A schema with none of these is a warning sign. It means the data is only ever as good as every piece of code that has ever written to it, and you will find out which one was wrong when the reports stop reconciling.

Business rules that change with policy belong in the application. The distinction is whether the rule could ever legitimately be false.

If it is slow, measure before you spend

Databases that have got slow have almost always got slow for one of a few reasons:

• A missing index that was unnecessary at ten thousand rows and is essential at two million.
• A query loading far more data than it needs and discarding most of it.
• An application making hundreds of small queries in a loop where one would have done.
• Growth in a table nobody is archiving — audit logs and event tables are the usual culprits.
• Locking, where one long operation blocks everyone else.

Every one is diagnosable. Insist on measurement before a fix is quoted: which queries are slow, how often they run, and what the database says it is doing. A proposal to add hardware without that is a proposal to spend money on a guess — and hardware usually buys a few months at best.

Migrations decide whether you can change anything

The schema will need to change after go-live. How that is handled is a decision worth making before the first release.

What good looks like:

• Every change is a versioned migration file, in source control, applied the same way in every environment.
• Nobody changes production by hand.
• Migrations run while the system is up.
• Difficult changes are staged — add the new column, backfill it, switch the code, remove the old one — across several releases.

Teams without this discipline become afraid of their own database, and that fear is what turns a two-day change into a three-month project. Ask how migrations work before you sign anything.

Spreadsheet to database is mostly modelling

If the brief is a spreadsheet that has outgrown itself, the technical work is small and the modelling work is not. A spreadsheet lets you put anything anywhere, and that freedom has always been used.

Expect the project to surface:

• Columns holding two things at once, separated by a slash.
• Rows that are really notes, or subtotals, or a heading someone added.
• The same entity written five ways.
• A rule that lives only in one person's head and one formula.

That is the project. Budget your own people's time for the decisions, because only they can make them, and expect the result to be better than the spreadsheet in ways that take some getting used to.

Design for deletion

Under the Privacy Act you have to be able to remove a person's information when there is no longer a reason to hold it. That is a schema decision made at the start or an expensive retrofit later.

Practically: know which tables hold personal information, avoid copying it into six places, and decide early whether deletion means removing rows or anonymising them so history and totals still reconcile. Ask each designer how they would handle a deletion request. It takes ten seconds and separates experience from enthusiasm.

Comparing five quotes

Read for:

• Who asked what questions the data has to answer?
• Who talked about constraints in the database rather than validation in the app?
• Who insisted on measuring before quoting a performance fix?
• Who described how schema changes happen after go-live?
• What documentation is included, and could your next developer read it?

Describe what the system does, roughly how much data you expect in three years, and the reports that must be fast. Designers who have done this will start by asking what you need to ask the data.