Database and user interface: how a web application works together
A web application does not consist of screens, but of data, rules and views onto them. Anyone who knows these layers understands why some requests are small and others only look small.
What you will learn
Five layers every entry passes through
- 1FormThe field in which someone enters something.
- 2RulesWhat is valid, who may change it, what follows from it.
- 3TableThe column in which the value lives, with its type and whether it is required.
- 4RelationshipsWhat the record is attached to — customer, order, line item.
- 5ReportingLists, filters and exports that use the value.
The user interface is the last of these layers and the only one you can see. That is why the effort of a project is looked for there — and is rarely found there.
Relationships: what a record is attached to
Data is not held in one big table, but in several that refer to each other. An order refers to a customer, a line item to an order and an article.
These references are the reason why some questions are easy to answer and others are not. "All orders of this customer" follows an existing reference. "All customers who bought a particular article in the last quarter" requires several steps — doable, but not free.
They are also the reason why deleting a record is rarely simple: a customer with orders cannot disappear without someone deciding what happens to the orders.
The data structure is the most important early decision
Interfaces can be rebuilt. Processes can be changed. A data structure that already holds years of data can only be changed with effort and risk — every change has to carry the existing data along, and a wrongly migrated row often goes unnoticed for months.
That is why we ask questions at the start that seem disproportionate: can an order have several contact persons? Can a line item have its quantity changed afterwards, and should the old value remain traceable? Can a customer also be a supplier? The answers cost ten minutes now and weeks later.
An example from practice
"Just one more field"
A cost centre is to be recorded on the order. On the form that is one line. Then the questions begin: what happens to the three thousand existing orders without a cost centre — does the field stay empty there, or is it mandatory? May every role change it, including afterwards? Does it appear in the handover to accounting? Do old reports remain comparable?
None of these questions is technical. All of them must be answered before the field can be built. That is why a developer asks about existing data, the handover and permissions — not about the field.
Common misconceptions
About the author
Fabian Lücker is managing partner of PHP-Schmiede KG in Rheine and has worked in software development since 2007. He is responsible for development and design, and writes here about decisions that come up in real projects.