Re:Dolt is Git for Data: a SQL database that you can fork, c...
by predakanga on Saturday March 06, 2021 @06:13PM
(#26371411)
This does exist, though support for it is pretty sparse; it's called "Temporal Tables" in the SQL:2011 standard - https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs...
Last time I checked, it was supported in SQL server and MariaDB, and Postgres via an extension.
Re:Dolt is Git for Data: a SQL database that you can fork, c...
by mcrutcher on Saturday March 06, 2021 @06:03PM
(#26371348)
This has existed for a very long time as a data modeling strategy (most commonly, a "type 2 dimension") and is the way that all MVCC databases work under the covers. You don't need a special database to do this, just add another column to your database and populate it with a trigger or on update.
Re:Dolt is Git for Data: a SQL database that you can fork, c...
by tthun on Saturday March 06, 2021 @06:12PM
(#26371403)
MS SQL server 2016 onwards has temporal tables that support this (point in time data)
Re:Dolt is Git for Data: a SQL database that you can fork, c...
by joshspankit on Saturday March 06, 2021 @06:57PM
(#26371718)
Huh. I just read the spec. Not quite three 'dimension', but looks like exactly what I was asking for: a (reasonably) automatic and transparent record of previous values, as well as timestamps for when they changed.
I'll call this a "you learn something every day" and a "hey thanks @tthun (and @predakanga)"
Re:Dolt is Git for Data: a SQL database that you can fork, c...
by kenniskrag on Saturday March 06, 2021 @05:49PM
(#26371248)
Because you can do that with after update triggers or server-side in software.
It might be a bit slower to write, but hook the logic in to write/delete, and suddenly you can see exactly when a field was changed to break everything. The right middleware and you could see the user, IP, and query that changed it (along with any other queries before or after).