Views: 0
Why a Single Hierarchical Table Matters to RDB Architects
2026-09-06
Relational databases are excellent for operational processing.
They are optimized for updating current data, enforcing integrity, joining normalized tables, and supporting transactions in real time.
But long-term preservation is a different problem.
A historical business record is useful only if the information needed to understand it can still be reconstructed years later.
That often requires more than the transaction rows themselves.
It may also require the exact historical master data, the correct schema version, code tables, join rules, and application-specific logic that existed when the transaction was created.
Structured Tidy Data Tables address that different requirement.
1. Start from the semantic object model
Consider a small accounting model with four classes:
-
JournalEntry -
JournalLine -
TaxDetail -
Account
A JournalEntry contains one or more JournalLine occurrences.
A JournalLine may contain repeating TaxDetail occurrences and references one Account.
Each class keeps its own unique identifier: entry_id, line_id, tax_id, and account_id.
The semantic model is the authority.
It defines the classes, attributes, cardinalities, and relationships.
The question is how to preserve and exchange data derived from that model.
2. What an RDB designer normally does
A conventional relational implementation decomposes the model into separate relations.
This is a natural design for operational processing.
2.1. JournalEntry
| entry_id | entry_date | journal_code | description |
|---|---|---|---|
|
H001 |
2026-09-01 |
SALES |
Sale of Product X |
|
H002 |
2026-09-02 |
PURCHASE |
Purchase of Material A |
2.2. JournalLine
| entry_id | line_id | debit_credit_code | account_id | amount |
|---|---|---|---|---|
|
H001 |
D001 |
debit |
1100 |
100.00 |
|
H001 |
D002 |
credit |
4000 |
100.00 |
|
H002 |
D003 |
debit |
5000 |
80.00 |
|
H002 |
D004 |
credit |
2100 |
80.00 |
2.3. TaxDetail
| entry_id | line_id | tax_id | tax_category | tax_rate | tax_amount |
|---|---|---|---|---|---|
|
H002 |
D003 |
T001 |
Standard |
10 |
8.00 |
2.4. Account
| account_id | account_name | account_type |
|---|---|---|
|
1100 |
Cash |
Asset |
|
4000 |
Sales |
Revenue |
|
5000 |
Purchases |
Expense |
|
2100 |
Accounts Payable |
Liability |
For an application database, this decomposition is often exactly what is wanted.
Each class has its own relation, and foreign keys reconstruct the business structure.
For exchange and long-term preservation, however, the same design creates another problem:
one semantic business record is spread across several tables or files.
3. Historical archiving is a different problem
Suppose a historical JournalLine contains only:
account_id = 1100
amount = 100.00Years later, 1100 is meaningful only if the corresponding historical Account master data is also available and still carries the meaning that applied at the time.
The same issue applies to:
-
tax codes,
-
parties,
-
currencies,
-
products,
-
organizational units,
-
and other referenced master data.
Header and Line data must also be preserved as one consistent set.
Restoring one without the other can leave broken references or an incomplete business record.
A database backup can preserve the operational state, but future reuse may then depend on restoring the correct:
-
database version,
-
schema,
-
master data,
-
code tables,
-
application logic,
-
and join rules.
That is workable for disaster recovery.
It is not always the best form for a portable historical archive.
|
Important
|
Normalization is excellent for operational processing, but a normalized database is not automatically a self-contained historical record. For long-term preservation, the information needed to understand a transaction should travel together, even when the operational application stores it across several tables. |
4. Why not simply flatten everything?
One alternative is to export a flat CSV in which JournalEntry attributes are copied onto every JournalLine row.
That creates another problem.
| entry_id | entry_date | description | line_id | account_id | amount |
|---|---|---|---|---|---|
|
H001 |
2026-09-01 |
Sale of Product X |
D001 |
1100 |
100.00 |
|
H001 |
2026-09-01 |
Sale of Product X |
D002 |
4000 |
100.00 |
entry_date and description are repeated for every line.
If TaxDetail repeats, the duplication grows again.
The file is rectangular, but the ownership of facts becomes blurred.
A value that semantically belongs to JournalEntry is physically copied onto JournalLine rows.
Structured Tidy Data avoids both extremes:
-
it does not require one physical table per semantic class; and
-
it does not duplicate parent attributes on child rows.
5. What is a Structured Tidy Data Table?
A Structured Tidy Data Table is one sparse, model-governed table that can carry occurrences of several semantic classes.
The essential rules are simple:
-
One row represents one class occurrence.
-
An attribute value appears only on the row of the class that owns it.
-
Repeating classes create additional rows, not numbered columns such as
Tax1,Tax2,Tax3. -
Hierarchical occurrence columns preserve the parent-child context.
-
References remain explicit.
-
Blank cells are structural: they usually mean that the column belongs to another class, not that a required value is unknown.
A simplified example is shown below.
| class | dEntry | dLine | dAccount | dTax | entry_id | entry_date | line_id | debit_credit | amount | tax_id | tax_rate | tax_amount | account_id | account_name |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
JournalEntry |
1 |
H001 |
2026-09-01 |
|||||||||||
|
JournalLine |
1 |
1 |
D001 |
debit |
100.00 |
|||||||||
|
Account |
1 |
1 |
1 |
1100 |
Cash |
|||||||||
|
JournalLine |
1 |
2 |
D002 |
credit |
100.00 |
|||||||||
|
Account |
1 |
2 |
1 |
4000 |
Sales |
|||||||||
|
JournalEntry |
2 |
H002 |
2026-09-02 |
|||||||||||
|
JournalLine |
2 |
1 |
D003 |
debit |
80.00 |
|||||||||
|
TaxDetail |
2 |
1 |
1 |
T001 |
10 |
8.00 |
||||||||
|
Account |
2 |
1 |
1 |
5000 |
Purchases |
|||||||||
|
JournalLine |
2 |
2 |
D004 |
credit |
80.00 |
|||||||||
|
Account |
2 |
2 |
1 |
2100 |
Accounts Payable |
|
Note
|
The class column is included for explanatory purposes to indicate the Class to which each row belongs. It is not a column defined in the actual data. |
The table looks unusual to an RDB designer because several semantic classes coexist in one physical table.
That is deliberate.
The table is not intended to replace the operational database.
It is a portable representation of the semantic dataset.
|
Note
|
In the following example, TaxDetail and Account are non-repeating Classes and are therefore represented in the same physical row as their parent JournalEntry. This does not alter the semantic Class hierarchy; it only reflects the physical row layout. |
| class | dEntry | dLine | entry_id | entry_date | line_id | debit_credit | amount | tax_id | tax_rate | tax_amount | account_id | account_name |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
JournalEntry |
1 |
H001 |
2026-09-01 |
|||||||||
|
JournalLine |
1 |
1 |
D001 |
debit |
100.00 |
1100 |
Cash |
|||||
|
JournalLine |
1 |
2 |
D002 |
credit |
100.00 |
4000 |
Sales |
|||||
|
JournalEntry |
2 |
H002 |
2026-09-02 |
|||||||||
|
JournalLine |
2 |
1 |
D003 |
debit |
80.00 |
T001 |
10 |
8.00 |
5000 |
Purchases |
||
|
JournalLine |
2 |
2 |
D004 |
credit |
80.00 |
2100 |
Accounts Payable |
6. Why this form matters
The unusual shape preserves three useful properties at the same time:
-
One portable dataset.
A complete business record can travel as one governed table instead of a collection of application-specific relations. -
No duplication of parent attributes.
JournalEntryfacts stay on theJournalEntryrow and are not copied to everyJournalLine. -
Explicit hierarchy and reference context.
The relationship amongJournalEntry,JournalLine,TaxDetail, andAccountremains visible and machine-processable.
For an RDB architect, the key distinction is purpose:
| Operational RDB | Structured Tidy Data Table |
|---|---|
|
Optimized for current transaction processing |
Optimized for exchange, inspection, archival, and analysis |
|
Several normalized physical tables |
Several semantic classes can coexist in one sparse table |
|
Relationships are reconstructed through joins |
Hierarchy is carried by occurrence context and explicit references |
|
Master data may live elsewhere |
Reference information needed for interpretation can travel with the dataset |
|
Depends on the operational schema and application conventions |
Governed by the shared semantic model |
7. Why this is also useful for AI
Structured Tidy Data is well suited to AI-assisted analysis because the information needed to understand a transaction can be preserved in one model-governed dataset.
In a conventional normalized RDB, an AI system may need more than the transaction rows.
It may also need:
-
the database schema,
-
primary- and foreign-key relationships,
-
historical master data,
-
code tables,
-
join rules,
-
and application-specific knowledge.
An account_id such as 1100 has limited meaning by itself.
The AI also needs to know that, in the relevant historical context, 1100 represented Cash and belonged to the Asset category.
A Structured Tidy Data Table reduces that dependency.
JournalEntry, JournalLine, TaxDetail, Account, and other relevant classes can coexist in the same dataset.
Each value remains on the row of the class that owns it, while hierarchical occurrence information preserves the relationships among those rows.
The advantage for AI is therefore not simply that a table is easier to read than a database.
|
Important
|
The more important point is that the semantic context required to interpret the data can travel with the archived dataset. |
An AI system can inspect the historical transaction without first recreating the exact original operational database and all of its joins.
8. The key message
Structured Tidy Data does not argue against relational databases.
An operational RDB remains the right tool for real-time transaction processing.
Structured Tidy Data serves a different purpose: it provides a portable, self-contained, model-governed representation for long-term preservation, exchange, validation, analysis, and AI-assisted interpretation.
For RDB designers, the unfamiliar table shape is not a rejection of normalization.
It is a way to preserve the benefits of semantic separation while allowing the complete historical business record to travel together.
Leave a Reply