4 ms·
I never understood why we don’t have SQL databases that track all changes in a “third dimension” (column being one dimension, row being the second dimension).
by joshspankit 6y ago
I never understood why we don’t have SQL databases that track all changes in a “third dimension” (column being one dimension, row being the second dimension).
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).
- tthun 6y agoMS SQL server 2016 onwards has temporal tables that support this (point in time data)
- joshspankit 6y agoHuh. 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)"
- predakanga 6y agoThis 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/07.industry.kulkarni.pdf https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs... Last time I checked, it was supported in SQL server and MariaDB, and Postgres via an extension.
- kenniskrag 6y agoBecause you can do that with after update triggers or server-side in software.
- mcrutcher 6y agoThis 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.