Custom database application development

Records and how they relate

Start by naming the main things your business tracks, such as customers, sites, contracts, products, jobs or assets, and how they connect. A customer may have several sites; each site may have many jobs; each job may use several products. Writing these relationships down, in plain language or a simple diagram, is the foundation of the database. Getting them wrong forces awkward workarounds, such as repeating a customer's address on every job.

Store each fact once and link to it, rather than copying it into many places.

Design choices that matter

Validation that keeps data clean

Many people, one database

• Simultaneous editing

  Prevent two people overwriting each other's changes.

• Permissions

  Control who can see and change each kind of record.

• Change history

  Record who changed what, and when.

• Backups

  Regular, tested backups with a clear restore process.

Finding information quickly

People judge a database application largely by how quickly they can find things. Design search around the ways staff actually look up records: by customer name, reference number, postcode, date or status. Saved filters for common views, such as overdue jobs or contracts renewing this month, save repeated effort. As data grows, the database needs indexes on frequently searched fields to keep searches fast.

Designing the database

1. 01

   Gather

   Collect existing spreadsheets, forms and reports.

   Output: Inventory of data

2. 02

   Model

   Define records, fields and relationships.

   Output: Data model

3. 03

   Check

   Walk through real examples with staff.

   Output: Validated model

4. 04

   Build screens

   Forms, lists and searches on top of the model.

   Output: Working application

Keeping history without clutter

Some information changes over time and its history matters: prices, contract terms, addresses, statuses. Keeping previous values, with dates, lets reports show what was true at a given point and lets mistakes be corrected. Records that are no longer active can be archived, hidden from everyday lists but still available. Personal data should be kept only as long as needed; the ICO states that you must not keep personal data for longer than you need it.

Reference lists and categories

Categories such as product types, job reasons, regions or contract levels belong in lists managed by administrators, not typed freely each time. Free text produces many spellings of the same thing, which breaks filters and reports. Managed lists keep data consistent and let administrators add new options without a developer. Where a value is retired, keep it for historical records but hide it from new entries.

Reviewing these lists once a year keeps them tidy and relevant.

Planning for growth in data

A database that holds a few thousand records today may hold far more in a few years. Choosing an established database engine, indexing frequently searched fields and archiving old records keeps performance steady. Hosting with automatic backups and the option to increase capacity avoids disruptive moves later. These choices cost little at the start but are expensive to retrofit.

Files attached to records

Photos, certificates and signed documents can be attached to records and stored securely, with access controlled by the same permissions as the data itself.