5 ms·

This is a standard bi-temporal model; valid time and transaction time are the terms the author is looking for. We plan to add more dynamic query control over t

This is a standard bi-temporal model; valid time and transaction time are the terms the author is looking for. We plan to add more dynamic query control over temporality to FaunaDB to solve this problem; you will be able to do a half-temporal join and get your past blog post versions by their current tags and other tricks.

valw 9y ago
Thanks, it's good to know there's an established terminology. I'll do my research and update the article.
- pjungwir 9y ago
  I saw your note in the post apologizing for making up your own terminology. I wouldn't feel too bad. In the SQL world no two authors I've read use the same terminology for these things! :-) Perhaps some terms are more "standard" than others, but they are all far from universal.
  - pgt 9y ago
    The least ambiguous terminology for me has been Martin Fowler's: `occurred_on`, `noticed_on` and additionally `recorded_on`. That way you keep track of when an event occured, when the accountants realized it and when the system recorded it. Sometimes these matter, esp. when dealing with distributed state.
deleted 9y ago
[deleted]
deleted 9y ago
[deleted]
pjungwir 9y ago
Temporal tables are a pet interest of mine, so here are some links that people might find useful: Richard Snodgrass, Developing Time-Oriented Database Applications in SQL. Hugh Darwen & C.J. Date, "An Overview and Analysis of Proposals Based on the TSQL2 Approach". Krishna Kulkarni & Jan-Eike Michels, "Temporal features in SQL:2011". Tom Johnston, Managing Time in Relational Databases. (I haven't read this one yet.) Tom Johnston, Bitemporal Data. Magnus Hagander, "A TARDIS for Your ORM": https://www.youtube.com/watch?v=TRgni5q0YM8 https://www.youtube.com/watch?v=TRgni5q0YM8 Also I've yet to see anyone even talk about managing DDL changes in temporal databases (well Magnus mentions that he won't cover it), but it's the same problem as discussed here. It's very practical, but you could write a dissertation or a book about it.
- evanweaver 9y ago
  Temporal DDL is the reason why FaunaDB late binds validations and indexes. It decouples the history of the schema from the history of the data, and migrating the past is not very practical anyway.
- eriknstr 9y ago
  If you could add links for the papers you mentioned that'd be nice. Assuming they are available to the public.
- jeffdavis 9y ago
  Also Temporal Data and the Relational Model https://www.amazon.com/gp/aw/d/B005UY0W0E/ref=mp_s_a_1_1?ie=UTF8&qid=1499712804&sr=8-1&pi=SL75_QL70&keywords=temporal+data+and+the+relational+model https://www.amazon.com/gp/aw/d/B005UY0W0E/ref=mp_s_a_1_1?ie=... A great book which inspired Range Types in PostgreSQL: https://www.postgresql.org/docs/current/static/rangetypes.html https://www.postgresql.org/docs/current/static/rangetypes.ht...
  - pjungwir 9y ago
    Thank you for range types! They are awesome and I have lost count of how often I've used them. I even have a blog post you might enjoy about defining an inetrange: http://illuminatedcomputing.com/posts/2016/06/inet-range/ http://illuminatedcomputing.com/posts/2016/06/inet-range/ (Corrections/suggestions welcome. :-) I know about that Date book but it is pretty low on my priority list. I've read a few other things by him and they seem like great ideas but hopelessly impractical for a working programmer like me (e.g. Tutorial D). And I am a little sore that rejecting Snodgrass's TSQL2 seems to have set back temporal databases by about 20 years. The Date paper I mentioned above has a lot of criticism but gives no real alternative proposals. So is that book better? Does it have something to add?
    - jeffdavis 9y ago
      Glad to see all the ways people use them! Date and Darwen are clear-thinking and they write well. But you are right that they are quick to criticize and some of the alternative solutions they offer are unconvincing. The most obvious example is their handling of NULL, which had a bunch of great criticisms followed by a totally impractical altenative ("special values"). If they had been a bit more humble, maybe they would have just borrowed Maybe/Option types from ML. On the other hand, they really helped me make the connection between logic and databases. And that helped me a lot in practical ways (e.g. exploring dirty data precisely). Regarding temporal data, they divorced from SQL and started from first priciples. That clarified a lot of things for me and then I adapted those ideas to SQL. So: their books are great, but I don't recommend joining their cult ;-) EDIT: I think a lot of SQL implementations are going about temporal the wrong way. They should focus on the fundamental building blocks first (like ranges of time) and build up from there.

valw 9y ago

Thanks, it's good to know there's an established terminology. I'll do my research and update the article.

pjungwir 9y ago
I saw your note in the post apologizing for making up your own terminology. I wouldn't feel too bad. In the SQL world no two authors I've read use the same terminology for these things! :-) Perhaps some terms are more "standard" than others, but they are all far from universal.
- pgt 9y ago
  The least ambiguous terminology for me has been Martin Fowler's: `occurred_on`, `noticed_on` and additionally `recorded_on`. That way you keep track of when an event occured, when the accountants realized it and when the system recorded it. Sometimes these matter, esp. when dealing with distributed state.

deleted 9y ago

[deleted]

deleted 9y ago

[deleted]

pjungwir 9y ago

Temporal tables are a pet interest of mine, so here are some links that people might find useful: Richard Snodgrass, Developing Time-Oriented Database Applications in SQL. Hugh Darwen & C.J. Date, "An Overview and Analysis of Proposals Based on the TSQL2 Approach". Krishna Kulkarni & Jan-Eike Michels, "Temporal features in SQL:2011". Tom Johnston, Managing Time in Relational Databases. (I haven't read this one yet.) Tom Johnston, Bitemporal Data. Magnus Hagander, "A TARDIS for Your ORM": https://www.youtube.com/watch?v=TRgni5q0YM8 https://www.youtube.com/watch?v=TRgni5q0YM8 Also I've yet to see anyone even talk about managing DDL changes in temporal databases (well Magnus mentions that he won't cover it), but it's the same problem as discussed here. It's very practical, but you could write a dissertation or a book about it.

evanweaver 9y ago
Temporal DDL is the reason why FaunaDB late binds validations and indexes. It decouples the history of the schema from the history of the data, and migrating the past is not very practical anyway.
eriknstr 9y ago
If you could add links for the papers you mentioned that'd be nice. Assuming they are available to the public.
jeffdavis 9y ago
Also Temporal Data and the Relational Model https://www.amazon.com/gp/aw/d/B005UY0W0E/ref=mp_s_a_1_1?ie=UTF8&qid=1499712804&sr=8-1&pi=SL75_QL70&keywords=temporal+data+and+the+relational+model https://www.amazon.com/gp/aw/d/B005UY0W0E/ref=mp_s_a_1_1?ie=... A great book which inspired Range Types in PostgreSQL: https://www.postgresql.org/docs/current/static/rangetypes.html https://www.postgresql.org/docs/current/static/rangetypes.ht...
- pjungwir 9y ago
  Thank you for range types! They are awesome and I have lost count of how often I've used them. I even have a blog post you might enjoy about defining an inetrange: http://illuminatedcomputing.com/posts/2016/06/inet-range/ http://illuminatedcomputing.com/posts/2016/06/inet-range/ (Corrections/suggestions welcome. :-) I know about that Date book but it is pretty low on my priority list. I've read a few other things by him and they seem like great ideas but hopelessly impractical for a working programmer like me (e.g. Tutorial D). And I am a little sore that rejecting Snodgrass's TSQL2 seems to have set back temporal databases by about 20 years. The Date paper I mentioned above has a lot of criticism but gives no real alternative proposals. So is that book better? Does it have something to add?
  - jeffdavis 9y ago
    Glad to see all the ways people use them! Date and Darwen are clear-thinking and they write well. But you are right that they are quick to criticize and some of the alternative solutions they offer are unconvincing. The most obvious example is their handling of NULL, which had a bunch of great criticisms followed by a totally impractical altenative ("special values"). If they had been a bit more humble, maybe they would have just borrowed Maybe/Option types from ML. On the other hand, they really helped me make the connection between logic and databases. And that helped me a lot in practical ways (e.g. exploring dirty data precisely). Regarding temporal data, they divorced from SQL and started from first priciples. That clarified a lot of things for me and then I adapted those ideas to SQL. So: their books are great, but I don't recommend joining their cult ;-) EDIT: I think a lot of SQL implementations are going about temporal the wrong way. They should focus on the fundamental building blocks first (like ranges of time) and build up from there.