What is the name of DB design pattern which support only soft-deletes and soft-updates?

Question

I heard that there is some DB design in relational databases where updating a record will create a new record with different timestamp or status. Also, deletion will be soft-delete (it updates the status or effective date to past date). It does not actually delete the record. I would like to know if there is a name for such a design pattern. In which scenarios do we need to use such patterns?

Answer 1

The concept you're looking for is probably "data versioning". It can be useful when it's important for auditing to keep track of all changes made to certain entities (think of configuration parameters, bank accounts of vendors or accounting records in general). To list all possible scenarios would not be practical in a Stack Exchange answer.

Here is an example article explaining how automatic data versioning works for a certain type of database (MariaDB). Articles like these might save you from reinventing the wheel; you don't want to roll your own versioning system except maybe for very simple cases.

Answer 2

This would be called a rather crude form of audit-trail.

Answer 3

Tuple versioning? ie. https://en.wikipedia.org/wiki/Tuple-versioning

As for the question when to use, "it depends". In many systems it is crucial that all changes to state can be tracked and/or restored, but cost is pretty high (both space- and operation wise)

Answer 4

A term often used is temporal tables. The data contains additional columns holding the time a row became into use and the time that row stopped being current. These columns can be explicit, under the control of a developer, or system-generated when the DML operations set the begin and end values.

Time can be measured by clock-time or internal measures like CPU ticks and log sequence number (LSN). Clocks are preferred because the value measured is not affected across system re-starts. It can also be compared with reasonable accuracy between systems. When using a clock it is important to have sufficient resolution that concurrent updates to a row can be distinguished.

History can be stored in the same on-disk structure as the current value or separate structures. It is possible to implement temporal stores using normal tables and triggers.

If all tables in a database have temporal functionality then the DB can be queried as it was at any point in the past. This allows, say, point-in-time reporting with concurrent updates (HTAP). Such a scheme could increase the freshness of reports and save cost by eliminating dedicated reporting infrastructure.