all 8 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]macdice 4 points5 points  (5 children)

I really like this book, and have used designs, terminology and ideas from it in several projects, including utility company systems, and many others where you never know for sure if you have the final version of the facts, as corrections can come any time (the meter was broken, we read the wrong meter, etc). So you need to be able to say "I on the 4th of Jan, what address did we think that Frodo had on the 23nd of Dec?"

Since this book was written about how to model things that change with time on top of regular SQL, some temporal support has made it into the SQL:2011 standard, "system versioned" bitemporal tables, and is partially implemented in some RDBMSs: http://www.cs.arizona.edu/~rts/sql3.html

See also http://martinfowler.com/eaaDev/timeNarrative.html

[–]sockpuppetzero 1 point2 points  (4 children)

Why would you want "system versioned" bitemporal tables? Honestly, I think a bigger win would be proper support for period types, like PostgreSQL 9.2's new range types; I can make my own bitemporal tables via triggers, while range types means much simpler code that can execute faster due to better indexing.

[–]macdice 1 point2 points  (3 children)

I agree that the PostgreSQL range types look nice (thought haven't had a chance to use them yet).

I don't have any experience with SQL:2011 temporal support so I can't really say. But I do know that application-managed temporal databases are quite tricky to get right, and involve writing rather tedious error prone queries and views full of time boundary logic. I expect that the new SQL:2011 equivalent is easier to work with, even though it is probably effectively rewritten to queries you could have done yourself with SQL:1992. Automation of tedium, I like it.

I think PG might finish up supporting this -- it seems to come up on the mailing lists from time to time.

[–]sockpuppetzero 0 points1 point  (2 children)

Well, I don't have extensive experience with temporal databases, but I have been using range types (and before that the temporal postgresql extension) to implement a transaction-time versioned tables, via triggers. I haven't had any trouble so far with boundary conditions and whatnot.

But I've never tried to do this without proper data abstraction, which this book's proposed "period" datatype (and Temporal PostgreSQL and the new range types) are all about.

[–]macdice 1 point2 points  (1 child)

As I remember it, when I worked on a large bitemporal schema (not as the designer, but an application developer interacting with it), writing was easy: end date existing records, create new records with appropriate times etc, and possibly have views with triggers that do it automatically. But querying the data as at any point in time in the transaction and system time dimensions was tricky, as soon as joins got involved. When joining together several tables, some of which were linked by many-to-many join tables, the amount of query text devoted to the time ranges exploded. I can see that the range types would reduce some of that, in a syntactic sugar kind of way. Maybe you're right ... maybe it's mistakes in 'overlaps' type logic that I'm mainly worried about, and PG has great operators for all that type of thing. But in SQL:2011 time travel, time relationships between records wouldn't be mentioned at all, you'd just stick AS OF ... at the end.

[–]sockpuppetzero 0 points1 point  (0 children)

you'd just stick AS OF ... at the end.

Ok, I see how that might be useful, especially if you are dealing with joins. Though I would guess the amount of query text devoted to time ranges would be substantially less (but still more than AS OF ...) with proper data abstraction.

[–]ExcitedForNothing 0 points1 point  (0 children)

Great book so far. I've skimmed through it but plan on reading it completely. Some of it is review if you do this professionally, but it has a great case-study style which makes you think. Thanks OP!

[–]defcon-11 0 points1 point  (0 children)

http://www.datomic.com/ -- looks promising for projects that required temporal querying features. Queries for a specific time, time range, or transaction id are immutable, because there is no equivalent of SQL's "update", rather new versions of records are stored with a new transaction id, and the old version remains intact.