cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Peter Lin <wool...@gmail.com>
Subject Re: Help on temporal data modeling
Date Fri, 23 Sep 2016 11:28:02 GMT
Ignoring noSql for a minute, the standard way of modeling this in car and
health insurance is with effective/expiration day. Commonly called
bi-temporal data modeling.

How people model bi-temporal models varies quite a bit from first hand
experience, but the common thing is to have transaction timestamp,
effective day and expiration day. This way, when the system needs to get
all the policies that expires in 30 days, it would run a query like this

select * from policies where expiration = '2016-10-25'

the batch application is responsible for calculating the expiration by
getting today's date + 30 days. The first thing I would change is modify
next_billing_date from timestamp to date and set the hour to 00:00:00. It
doesn't make sense to use timestamp datatype for this particular use case.

Getting back to Cassandra and CQL. You should be able to just query it
without having to resort to materialized views and secondary indexes. If
you really want to index it, use Solr or buy a license of DSE. The temporal
database I built on top of Cassandra uses Solr, which gives me greater
flexibility and control over indexes and query performance.


On Fri, Sep 23, 2016 at 4:42 AM, Denis Mikhaylov <notxcain@gmail.com> wrote:

> Hi!
>
> I have question regarding data modelling.
>
> Let’s say that I have `subscriptions` table with two columns
> `subscription_id text` and `next_billing_date timestamp`.
>
> How do I model a table to efficiently query all subscriptions due today
> (something like `where next_billing_date <= today`)

Mime
View raw message