Relational data

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 idemployee iddatereason
t1e12019-05-15Customer meeting in Oslo

Table 2. Employee table - one row per employee

employee idfirst namesecond name

Table 3. Transaction table - one row describes one transaction

transaction idtravel iditem namecostcurrency
c1t1bus ticket100NOK

Can we make an aggregate table where a single record holds the whole report? Only partly.

Table 4. Aggregate table

travel idempl.idfirst namesecond namedatereasontrans iditem namecostcurrency
t1e1OlaNordmann2019-05-15Customer meeting in Osloc1bus ticket100NOK
t1e1OlaNordmann2019-05-15Customer meeting in Osloc2accomodation1000NOK

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.