Database design and optimisation.

Database design is based on the application's data model, its relationships, consistency requirements, access patterns, expected workload, and reporting needs. Those inputs determine the structure; the structure then determines how much every later feature costs to build.

Normalisation is applied where consistency matters and relaxed where read performance requires it. That trade-off is a decision recorded during design rather than a by-product of the first implementation. PostgreSQL is our default for business applications. MongoDB suits document-shaped or flexible-schema data, and MySQL or SQLite fit smaller or simpler cases. The engine is selected against the workload and the hosting environment.

Performance work begins with measurement. We review query execution plans, indexes, connection usage, data access patterns, and workload characteristics before making changes.

The usual remedies are indexing, query rewriting, connection pooling, caching, and targeted schema adjustments. Which of those apply, and how much difference they make, depends on the workload. The measurement before and after is the record of what the work achieved, and it is part of the deliverable.

Backup requirements are defined around the acceptable recovery point and recovery time for the system: how much data the business can afford to lose, and how long it can afford to be without the system. Depending on the project, this can include automated backups, point-in-time recovery, off-site copies, and periodic restore testing.

Restore testing is the step most often skipped, and it is the only thing that establishes whether a backup is usable. Where it is in scope, restores are run into a separate environment on a schedule and the result recorded.

Migrations are planned around data mapping, validation, cutover requirements, acceptable downtime, rollback strategy, and reconciliation after the move. Acceptable downtime is agreed in advance, because it determines the migration approach.

Higher-risk migrations are rehearsed against a non-production copy before the production change, with record counts and key values compared on both sides. The rollback is prepared and tested alongside the migration.

Access control covers roles and permissions, encryption in transit and at rest where the data requires it, and audit logging of who changed what. Which of those apply depends on the sensitivity of the data and any obligations attached to it.

Reporting views can be separated from the operational database so analysis does not run against production, with a refresh frequency agreed against how current the reporting has to be.