Hacker News Reader

Hacker News

Comment by pjungwir | Hacker News Reader

Parent Full thread

pjungwir

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

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.

View on HN

Also 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.ht...

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/ (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?

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.

Okay, I appreciate the reply! I admit they had some good points about problems they found in some TSQL2 edge cases (mentioned in that paper of theirs). I will reconsider how quickly I get to their temporal book. I agree knowing a more pure way to do relational is helpful even if your day-to-day is still SQL.

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.

If you could add links for the papers you mentioned that'd be nice. Assuming they are available to the public.

Reply on news.ycombinator.com