Let's look into this via an example. We want to build a thin app which gives access to travel reports. Our aggregated data record - what the thin app's search interface will filter on - is a report, which has a header (travel date, reason, employee info, etc.) and a list of transactions (cost items).
The following simple tables hold data about a travel report.
Table 1. Travel table - holds one record per trip
| travel id | employee id | date | reason |
|---|---|---|---|
| t1 | e1 | 2019-05-15 | Customer meeting in Oslo |
Table 2. Employee table - one row per employee
| employee id | first name | second name |
|---|---|---|
| e1 | Ola | Nordmann |
Table 3. Transaction table - one row describes one transaction
| transaction id | travel id | item name | cost | currency |
|---|---|---|---|---|
| c1 | t1 | bus ticket | 100 | NOK |
| c2 | t1 | accommodation | 1000 | NOK |
Can we make an aggregate table where a single record holds the whole report? Only partly.
Table 4. Aggregate table
| travel id | empl.id | first name | second name | date | reason | trans id | item name | cost | currency |
|---|---|---|---|---|---|---|---|---|---|
| t1 | e1 | Ola | Nordmann | 2019-05-15 | Customer meeting in Oslo | c1 | bus ticket | 100 | NOK |
| t1 | e1 | Ola | Nordmann | 2019-05-15 | Customer meeting in Oslo | c2 | accomodation | 1000 | NOK |
The aggregate table is highly redundant; still, it does not let us add all report data into a single record. Any application that uses this as a source will have to do further aggregation beyond record filtering.