[BLOG] 'Building a bitemporal index (part 1): a taxonomy of bitemporal data'

[jarohen]

Afternoon all! 👋

I've written a blog outlining different types of temporal data - what they look like in practice, how they're updated, how they're queried.

Also, why you need to use a database that understands how temporal data actually works rather than just adding some columns to an existing table - although maybe I'm a little biased on that one 😅

Building a bitemporal index (part 1): a taxonomy of bitemporal data

Let us know what you think in the comments below!

[itai-spiritt]

hey, i enjoying reading about your work
how would you categorize references (foreign keys, etc.)?
what about the use cases of chats, bidding, public transport location (buses, trains, metro movements)?

[jarohen]

hey @itai-spiritt 👋

how would you categorize references (foreign keys, etc.)?

These would likely fit under the content-filters: let's say you've got a 1:N FK - how many children each parent has determines the 'selectivity' of the join. i.e. if you've got 100 parents and 100000 children you're probably looking through most pages of the children table (assuming it isn't partitioned) to find the children for one parent; if it's 100:300, the join condition is much more selective.

what about the use cases of chats, bidding, public transport location (buses, trains, metro movements)?

It'd be worth categorising these under the dimensions I've outlined in the blog - what do you think each one would come under? Or would these be the fabled sixth use case? 🙂

[itai-spiritt]

these 3 use cases do update frequently (especially transportation GPS).
would you say it's IoT reading? because the frequency is not as fast as mentioned (1 ms, I would say a few seconds)
so I guess it's somewhere between static ref data and IoT... what do you think?
and how do you model that, as separate entities that gets updates (with history)?

[jarohen]

Transportation, yep - almost certainly IoT; bids/chats, I'd probably say are under the trades/orders one? grey area, though. In this case, the absolute frequency is probably less of a factor - it's more whether each entity gets updated a handful of times or is constantly changing.

In terms of modelling them:

• Chat/bids: probably one entity for each message/bid, because that's the entity granularity at which you want to have a valid-time timeline.

• Transportation: one entity per vehicle, use valid-time to model the timing of the readings. I'd use the reading time as the valid-from, and then decide on a TTL for valid-to - i.e. for how long would you want that reading to appear as the 'current' reading, if you stopped getting readings for that vehicle? Sometimes, in this category - let's say CPU temperature readings - a reading is not going to be accurate a few minutes later; if it's a truck, its location is probably still 'current' the next morning, if it hasn't been superseded? Depends on your situation 🙂

  It doesn't matter too much if this period is too long - if another reading comes in, it'll supersede the previous one, because of the semantics of valid-time. Handy!