TL;DR
- Most tools build an invoice as two tables joined by a link. The parent and its lines are then separate records that get saved separately.
- A line item has no independent life. Delete the invoice and the line should go with it. Reject the invoice and none of its lines should survive.
- In InfoLobby the invoice and its lines are one atomic write unit. Either the whole unit is valid and commits, or none of it does.
- That is what makes cross-row rules possible at all. "Debits must equal credits" cannot be checked one row at a time, because a single row is never valid on its own.
- Use a sub-table when the child belongs to the parent. Use an ordinary relationship when the child has its own lifecycle.
Most database tools can build an invoice.
Create an Invoices table. Create another table for Line Items. Link the two together.
It looks like an invoice.
But there is a fundamental problem.
The database still considers the invoice and its line items separate records.
That means the parent can be saved separately from its children. A line item can potentially be written separately from its invoice. And rules involving several lines become something you have to enforce around the relationship.
For an invoice, I don't think that is the right data model.
A line item isn't really an independent record.
It is part of the invoice.
So I built an invoice in InfoLobby and then deliberately tried to break it.
First, the invoice
The basic invoice structure is straightforward.
The parent record contains fields such as:
- Invoice Number
- Client
- Issue Date
- Due Date
- Status
- Total
Inside that invoice is a sub-table called Line Items.
Each line contains:
- Description
- Quantity
- Rate
- Total
Total is calculated from Quantity × Rate.
The invoice total can then be calculated from all of the line totals.
I previously built this complete invoicing workflow in InfoLobby, including automatic invoice numbering, due dates and PDF generation. The original build shows how the sub-table is configured and used inside an invoice.
Watch the original invoice build
That video covers how to build it. The sub-tables help guide covers the configuration in writing.
This article is about what happens underneath it.
A line item should not exist without its invoice
Take this invoice:
INV-0042
| Description | Quantity | Rate | Total |
|---|---|---|---|
| Consulting | 4 | £150 | £600 |
| Implementation | 2 | £250 | £500 |
There are technically three pieces of data here.
One invoice and two lines.
But there aren't really three independent business entities.
Nobody cares about:
Implementation | 2 | £250
without knowing which invoice it belongs to.
Delete the invoice and that line should disappear with it.
Create the invoice and its lines should be created with it.
Reject the invoice and none of its lines should survive the failed operation.
This is master-detail.
The invoice is the master.
Its lines are weak entities whose existence depends on that master.
That distinction becomes important as soon as we introduce a rule involving more than one line.
Let's try to save an invalid accounts journal entry
For the test, I'll make the rule more interesting than simply checking whether Quantity contains a number.
Imagine each line contains:
- Debit
- Credit
The journal entry is only valid when:
Total Debit = Total Credit
A valid set of lines might look like this:
| Account | Debit | Credit |
|---|---|---|
| Revenue | £1,100 | £0 |
| Accounts Receivable | £0 | £1,100 |
Now I'll deliberately break it.
| Account | Debit | Credit |
|---|---|---|
| Revenue | £1,100 | £0 |
| Accounts Receivable | £0 | £1,000 |
There is now a £100 difference.
I press Save.
The journal entry is rejected.
Nothing gets partially written.
We don't save the journal entry and then discover that one of its lines failed.
We don't save some lines and roll back others manually.
The journal entry and its lines form one atomic write unit.
Either the complete unit is valid and commits, or none of it does.
Why this cannot be ordinary row validation
This is where the implementation gets interesting.
Look at the first line:
| Account | Debit | Credit |
|---|---|---|
| Accounts Receivable | £0 | £1,100 |
Is it valid?
You cannot answer yet.
It only becomes valid when another line supplies the corresponding £1,100 credit.
So running validation independently against every line cannot enforce this rule correctly.
The validator needs to see:
the proposed state of the entire journal entry.
Only then can it calculate:
SUM(Debit) == SUM(Credit)
and decide whether the write should be allowed.
The validation rule therefore fires once for the whole unit, rather than independently for each line. It is a PHPScript check that runs before the write, sees the proposed master record and all of its proposed lines, and blocks the save by returning a message.
This opens up much more useful validation than:
Quantity must be greater than zero.
You can enforce rules such as:
A journal entry total must equal the sum of its lines.
Debits must equal credits.
At least one line must exist.
The combined allocation across lines must equal 100%.
These are rules about the record as a whole, not an individual field or row.
InfoLobby already supports table-level validation against record state. In my original invoice build, I demonstrated a simpler version by preventing the Issue Date from being changed after the invoice had been created.
Sub-table validation extends that principle across the complete master-detail structure.
This is different from displaying related records
There is nothing wrong with linked records.
I use them constantly.
But linked records and master-detail solve different problems. It is the distinction most no-code databases skip, and the reason switching from Airtable changes what you can guarantee rather than just what you can see.
Consider:
Client → Cases
A case can meaningfully exist as its own record.
Company → Contacts
A contact can have its own information, relationships and lifecycle.
Project → Tasks
Depending on the system, a task may need its own permissions, automations and relationships.
Those are genuine entities connected by relationships.
Now consider:
Invoice → Invoice Lines
An invoice line has no useful independent lifecycle.
The same is often true for:
- Quotation items
- Purchase order lines
- Sales order lines
- Journal entries
- Expense breakdowns
- Timesheet entries
- Inspection rows
- Allocation lines
The question isn't whether two records are related.
The question is:
Should the child be allowed to exist independently of its parent?
If yes, model a relationship.
If no, you probably have a master-detail problem.
Why this distinction matters
At first, both approaches can look almost identical in the interface.
You open an invoice.
You see its lines underneath.
Done.
The difference appears when something goes wrong.
Can the parent exist without its required children?
Can a child survive after its parent disappears?
Can an API modify the child without going through the parent?
Can a validation rule inspect all proposed lines before any of them are committed?
Can the entire operation fail without leaving half-written data behind?
Those aren't interface questions.
They are data integrity questions.
And once a no-code system starts running invoicing, accounting, approvals, orders or other operational processes, those questions matter considerably more than whether related records can be displayed nicely inside a tab.
The limitation
Not every child record belongs in a sub-table.
If the child needs:
- its own independent lifecycle
- separate permissions
- independent workflows
- extensive relationships elsewhere in the system
- direct creation and editing outside the parent
then it may deserve to be a normal table.
Sub-tables are specifically useful when the child belongs to the parent.
That is the trade-off.
The test I use
When deciding between a relationship and a sub-table, I ask one question:
If I delete the parent, would keeping this child record make any business sense?
If the answer is yes, it is probably an independent entity.
If the answer is no, treating it as an ordinary related record may be modelling the wrong thing.
An invoice line isn't a record that happens to be related to an invoice.
It is part of the invoice.
So when I save an invoice, I want the database to treat the invoice exactly the same way the business does.
As one thing.
Related reading: why true atomic master-detail matters goes through the transaction boundary itself and how the other platforms document their behaviour. If the rule you need to enforce is a sign-off rather than a sum, approval workflow software covers that shape.