I agree with filmfact's comments. I've built a lot of temporal tables in SQL, by hand. Time can be a tricky concept, for both users and developers too. Time

I agree with filmfact's comments. I've built a lot of temporal tables in SQL, by hand. Time can be a tricky concept, for both users and developers too. Time travelling is even more confusing for people. Temporal tables were added to the SQL standard in SQL:2011 [1], so a number of SQL databases support it in the DBMS itself. I have not personally used any of these implementations in my own projects yet, and had been using my own implementations for this previously. But, I have been looking into these features over the last few years and considering switching to using something provided by the DBMS itself. I have seen a number of different implementations/patterns of temporal support: 1) Record effectivity to/from dates, "current flag" to make it easier for people to find the current row. This can be used in normal 3NF relational databases, but a good example of where this is often applied (I see someone else mentioned it) is in denormalized dimensional models - slowly changing dimension (SCD) type 2. That is more for data warehousing, but the point of the temporal tracking columns is the same. 2) On the note above about dimensional models, a most common pattern is OLTP and OLAP, where transactional/OLTP data is append only with no deletes (or only soft deletes) and the historical/temporal tracking is done in a separate purpose built data warehouse/system (OLAP). 3) "Current" table vs. "history" table, so only current records are in one table and history are in another. This pattern can be helpful, if you have strict constraints on the "current" table, but don't want to enforce them on the "history". For example, imagine a column that needed to be unique. You want to ensure that this constraint is enforced by the database, but you can't enforce it in history necessarily where there would be multiple instances of a row. There are other ways around this problem, but something to think about. Filtered indexes (indexes that only apply to certain rows) can help with this problem. 4) "Audit logs" are not temporal solutions. I don't know how many times vendors have said "oh we track history" when in reality, they only track changes to records, and that is not a temporal database. 5) Data "snapshots" are another solution to being able to go back in time. I haven't used such a solution myself to solve a bi-temporal problem, as I don't think it's really intended for that, but there are solutions that exist that do this. Usually this is implemented by the DBMS or storage system, and it involves storing a snapshot of data on disk and then storing deltas/changes - but usually not for extremely long periods of time. A few other thoughts: 1) Temporal databases are often append-only, this can actually be an advantage - because you never delete, it opens up certain database technologies as a possibility that only allow append (for example, many serialized data storage formats in HDFS are append only). HOWEVER, keep in mind privacy concerns. If you never delete, but have to comply with privacy laws (such as GDPR, right to be forgotten, etc.) you will have to think of how to address that. 2) Storage naturally grows faster with temporal systems since you can maintain many copies. Physical table/collection/dataset partitioning often comes in handy when you need to scale this pattern. You have to pay special attention to partitioning keys. For example, if you partition by a date tied to how fast your data grows, historical data might end up unevenly distributed throughout partitions. This applies if you are talking about a distributed system (e.g. cluster of nodes) or even a single system with a partitioned dataset across multiple disks, arrays, etc. EDIT: 3) One simple thing people overlook - what to use as a date/time stamp! Temporal databases that natively support this often have sequence number management internally. If you are doing this yourself, make sure to use either GMT dates, or date/time with timezone offset. It will make your life easier in the long run if you have to deal with global time differences, and since such a system usually lasts a long time, it makes sense to use a standard. I would avoid using Unix timestamps or anything that is too implementation dependent (internal sequence numbers, etc.) if you are going to build it yourself. [1] https://en.wikipedia.org/wiki/SQL:2011 https://en.wikipedia.org/wiki/SQL:2011