|
|
|
|
|
|
by sjwright
2173 days ago
|
|
|
> it was rarely practical to consider retaining database history for the first few decades of SQL That does make sense from a historical perspective and I don't doubt that's why. But still I find it unsatisfying because any competent database schema will always retain the history that needs to be retained. If you don't have the storage capacity, you choose to not store so much history. If you don't have native concepts for storing history, you kludge it yourself. Whether you have native temporal support or have to kludge a DIY solution in the schema, the data you need to store gets stored. My frustration is that I feel that temporal concepts should have been deeply native to SQL right to its core. History should have been as fundamental to database design as columns and rows. It should be a thing you turn off when you don't want it, not a thing you turn on when you do. |
|
|
Oh absolutely. I think the original intuition by Snodgrass et al. in TSQL2 to model temporality outside of the actual relational structure was a more promising direction, otherwise the complexity of composing joins across many tables, each with independent temporal versioning, seems rather overwhelming (note that the article doesn't discuss joins at all). Schema migration is another hairy topic that the 2011 standard barely addresses.
Modelling temporality outside of the database is what we've been pushing ahead with on Crux, which provides Datalog as the native query language but will imminently also support a simple bitemporal flavour of SQL for point-in-time querying (using Apache Calcite), where application & system time are defined by a single "as-of" prefix at the beginning of the statement that applies across the entire query: https://github.com/juxt/crux/tree/master/crux-sql#bitemporal... (queries over ranges and periods are currently out-of-scope)