I'd love to see native temporal tables at some point. Being able to step back in time without relying on extra code or a plugin would be great.
https://learn.microsoft.com/en-us/sql/relational-databases/t...
https://learn.microsoft.com/en-us/sql/relational-databases/t...
It makes so many things far easier, having a temporal relation for every record and saves a lot of headaches that would otherwise usually be dealt with on the application level.
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, ...
What we need is native support for bi temporal data like MariaDB.