tpetry
commented
3 years ago System-versioned tables are not really supported by the open source databases:
- Mysql: mysqldump does not support them, so backups will not work correctly
- PostgreSQL: currently no official support, there are multiple different 3rd party extensions
- SQLite: currently no support too?
You could do something similar with Global Scopes and valid_from timestamptz and valid_to timestamptz nullable columns ;)
Mostly databases are used without timelined versioned entries. An update of a record "deletes" the old information.
For some applications it's necessary to store temporal information like addresses, interests, prices etc. Database handling with one or two dimensional timelines (business validity period and system-versioned period) was very complex. Therefore modern databases like MySQL, DB2, Oracle, SQL Server, Postgre introduced this functionality some years ago. The standard is defined in SQL:2011. Systems-versioned tables allows auditability and timeline analyses.
Information about bi-temporal data storage Temporal features in SQL:2011
Neither Laravel's schema builder nor query builder support SQL:2011 commands. The grammar between some database providers could be different. (eg. SQL Server doesn't support application-time periods yet)
I tried to understand how to extend commands and modifiers in Laravel's builders for MySQL. It's too complex for my knowledge.