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 <notxc...@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`)
