Learning library / Application architecture

How databases work

A database keeps information organised so an application can find, change and relate it. The hard part is deciding what the information means and who can use it.

Explore this guide
01 / Structure

Records represent things the business needs to remember.

In a relational database, a table groups records of one kind. Each row is one record and each column describes a named field, such as a product name, provider ID or date. Other database models organise information differently; the right choice depends on the queries and relationships the product needs.

A grocery product might have separate records for users, lists, staples and providers. Separating these concepts helps prevent one changed provider address from having to be fixed in every deal record. It also makes the ownership of each fact clearer.

Read the source: PostgreSQL: Tables, rows and columns.

02 / Retrieval

A query asks for a useful view of those facts.

The app may ask for all active deals for a user's staples, the next published roster for a venue, or a count of bookings this week. A query selects matching records and can connect related tables. Indexes can help common lookups, but they also use storage and add maintenance work; choose them around real queries.

The interface should not receive more personal information than it needs. The server should decide which records the requester may access before it returns them. Hiding a field on a screen does not protect data that was already sent to the browser.

Read the source: PostgreSQL: Joining related tables, OWASP: Authorisation.

03 / Changes

Changes need rules, validation and consistency.

Creating an account, publishing a shift and changing a provider deal all modify stored facts. The app should validate the input, check permission, apply business rules and tell the user whether the change succeeded. A transaction can group related database changes so they succeed or fail together, avoiding a half-finished update.

Plan what happens when two people edit the same record. Some products can show a simple conflict and ask one person to retry; others need locking or a more specialised collaboration model. The choice depends on the consequence of a conflicting change.

Read the source: PostgreSQL: Transactions.

04 / Stewardship

Data needs a lifecycle beyond its first write.

Decide what should be collected, how long it should be kept, who can export or remove it, and how it can be restored. Backups are useful only when the team has tested that they can recover the right information and continue operating after a failure.

Changes to the structure of a live database need care. Test migrations against representative, appropriately protected data before production, and plan a rollback or recovery path. The more important the records, the more deliberate this step should be.

Use this in a project

Questions worth answering together.

These make the idea concrete before the team commits to a design or release plan.

  1. 01

    What facts must be remembered, and for how long?

  2. 02

    Which relationships will users search or report on?

  3. 03

    Who may read, create, change or delete each kind of record?

  4. 04

    How will we restore the system after a failed change or outage?

Further reading

Primary references.

Technical references for the concepts in this guide. The project advice and examples are our interpretation.

From concept to delivery

Bring us the question you are working through.

From Melbourne, Paradox Design develops bespoke web applications and operational software. We can help turn a product idea, existing workflow or technical concern into a clear next step.

External project website

Open this project site?

is hosted at . It will open in a new tab.