This is another solution to such a common problem that one might be surprised at there being no solution baked into the SQL standard. Instead, we have some vendor specific features like automatic audit tables and time travel, and a huge array of bespoked techniques like in the article: everything from adding a deleted_at column through to re-architecting your system around event-sourcing.
Why such diversity of solutions? I believe this is because the problem of adding time dimensions to your data model is heavily dependent on exactly what you intend to do with your data. Enabling theoretical audits is a very different requirement than enabling admins to rollback changes to data, which is a very different requirement to enabling users to undelete their data - and then there’s a performance angle to layer on top: your design will depend on whether you need your INSERTs/UPDATEs to be fast or your SELECTs.
It’s definitely not a case of one of these approaches being definitively better than another. You can’t shortcut talking to your users/clients/stakeholders about how the whole system is intended to work.
The SQL:2011 standard does describe a mechanism for solving this called System Versioned Tables, but the only database I've encountered that implements it so far is MariaDB: https://mariadb.com/kb/en/system-versioned-tables/
I happened to fall down this rabbit hole a few days ago because of another HN thread. In it, a commenter mentioned [1] a talk by Markus Winand where he goes over some of the more interesting additions to the SQL standard since 92, including versioned tables [2] (I've linked to the timestamp, but the whole talk is good).
I got real excited about that feature because I could think of a few tables at work that could use it. Sadly, PostgreSQL doesn't implement it [3] (I can't permalink to the feature but you can search for "T180", "System-versioned tables", "T181", or "Application-time period tables").
Why such diversity of solutions? I believe this is because the problem of adding time dimensions to your data model is heavily dependent on exactly what you intend to do with your data. Enabling theoretical audits is a very different requirement than enabling admins to rollback changes to data, which is a very different requirement to enabling users to undelete their data - and then there’s a performance angle to layer on top: your design will depend on whether you need your INSERTs/UPDATEs to be fast or your SELECTs.
It’s definitely not a case of one of these approaches being definitively better than another. You can’t shortcut talking to your users/clients/stakeholders about how the whole system is intended to work.