Denormalisation and calculated fields

Denormalisation means storing a copied value on a record even though the source value lives somewhere else or can be calculated from related records.

In Bubble, this can be the right decision. A copied field can make a search possible, make a repeating group faster, or let a workflow check one stored value. Once you copy the value, you are responsible for keeping the copy correct.

Default to storing the relationship clearly. A Job points to aClient. An Invoice points to aClient. An Invoice Line Item points to anInvoice.

Do not copy client name, client email, or invoice total only because the value appears on a page. First check whether Bubble can read the value through the relationship or calculate it cheaply enough for the place where it is used.

Copy data when the copied field has a specific job. The common reasons are search, display, filtering, privacy, and repeated calculations. Each copied field should have a named source and a named reason.

A Job's client name lowercase field can make job search match client names without searching clients first. AnInvoice's total field can let lists, reports, and payment workflows read one stored number instead of summing line items every time. These fields are useful because Bubble reads them directly from the record being searched or displayed.

The copied field should not become the place where the app edits the value. If the source is Client's display name, users edit the client. Job's client display name exists for search or display only.

Denormalised fields should almost always be maintained by database trigger. Page workflows should not each remember to update copied values. If a copied field can be changed from several screens, imports, API calls, or scheduled workflows, scattered maintenance actions will miss a case.

If Job stores client display name, a database trigger on Client should update related jobs when the client display name changes. If Invoice stores total, a database trigger on Invoice Line Item should recalculate the invoice total when a line item is created, changed, or deleted.

The trigger is part of the database structure decision. When you add the copied field, also decide which source change runs the trigger, which records it updates, and how old records will be backfilled.

Many calculated values should stay as dynamic Bubble expressions. Use a dynamic expression when the list is small, the value appears in one place, the calculation is cheap, and the result is not needed for searching, sorting, filtering, privacy, or reporting.

Remember, the total cost of an expression is the cost per invocation multiplied by the number of times it is invoked. It is only worth optimising if that result becomes large enough that optimisation is justified.

An invoice can begin with line items and a dynamic expression:Search for Invoice Line Items:sum of amount. If the invoice page only shows one invoice at a time, that may be enough.

The tradeoff changes when invoice totals appear in account lists, reports, overdue checks, payment workflows, and exported data. At that point,Invoice's total can be a stored calculated field maintained by database trigger.

The trigger has to cover every source change: line item created, line item amount changed, line item deleted, tax changed, and any backfill that creates historical line items. If one of those cases is missing, the stored total can be wrong while the line items are correct.

Every copied field adds a source field, a copied field, a trigger, a backfill path for old records, and a stale-data risk. That extra work is worth it when the copied field solves a real search, sort, filter, display, privacy, reporting, or repeated-calculation problem.

Build the direct relationship first. Add the copied field when the read-side benefit is clear enough to justify the trigger and backfill work.