Imagine you're building an support ticket tracker whose reporting capabilities should allow to provide data on various historic items of your tickets such as "time per assignee", "time per state", "time between creation and solution" etc.
In an environment built on "traditional" SQL you'd have a history table, where you first and foremost would have to maintain records for those values:
- ticket ID
- time of state change
- type of state change
But for the stats to be actually valuable, you might need additional history context (different per type), such as the user ID, the state, criticality, etc for which either an additional table per history type item type would be required or the data could be stored as serialized value with a metadata column of the history table. One requires complexity on the DB level, the other requires application level logic and slows down report generation tremendously.
Temporal tables to the rescue to remove all this complexity.
The full state of a ticket record including it's related entities at the given time of an UPDATE is fully preserved by the DB natively, so all you'd have to do to retrieve historic records at a given time is to append "AS OF $TIMESTAMP" to your query.
In the end, there's no need for extra history tables, much better performance when building reports, no need for application level data wrangling, ...
In an environment built on "traditional" SQL you'd have a history table, where you first and foremost would have to maintain records for those values: - ticket ID - time of state change - type of state change
But for the stats to be actually valuable, you might need additional history context (different per type), such as the user ID, the state, criticality, etc for which either an additional table per history type item type would be required or the data could be stored as serialized value with a metadata column of the history table. One requires complexity on the DB level, the other requires application level logic and slows down report generation tremendously.
Temporal tables to the rescue to remove all this complexity.
The full state of a ticket record including it's related entities at the given time of an UPDATE is fully preserved by the DB natively, so all you'd have to do to retrieve historic records at a given time is to append "AS OF $TIMESTAMP" to your query.
In the end, there's no need for extra history tables, much better performance when building reports, no need for application level data wrangling, ...