Modelling Meter Rates in Rails

I have been working on a small Rails app to display household energy readings. The application receives power usage updates for a set of Meters. Each Meter can have a Rate attached to it, to calculate the cost of the energy.

Over time the rates can change, and we need to keep the old (or future) rates to calculate historical usage.

Rates with Start and End Date

We need a model that allows:

1. A date the rate becomes valid
2. A date the rate ends
3. A potentially open ended rate for the present day into the future.

Initially I considered modelling this with a simple table:

create_table :meter_rates do |t|
      t.references :meter, null: false, foreign_key: true
      t.decimal :rate, precision: 10, scale: 2, null: false
      t.date :start_on, null: false
      t.date :end_on
end

This meets all the requirements, where an open ended "present day" tariff has a null end date. However there are a series of hidden complexities:

1. We need a validation, ideally at the database that start_on is <= end_on. This is easily solved with a simple constraint.

2. Ideally, we need a constraint to ensure no tariffs start on the same day. Again easily achieved with a unique index.

3. An open ended tariff can be represented with a null end date, so adding a new tariff requires ending the current one and adding a new row. Adding a rate in the middle of two existing rates involves modifying the previous end date, adding a new row and then modifying the start date of the later row. This may be further complicated as below.

4. Ideally, we need to ensure tariffs do not overlap. Ie the start date or a new tariff is not between the start and end of another tariff. This is where things start to get tricky. Constraints and indexes only cover the newly inserted row. Therefore a database trigger is needed to validate the new rows against others.

5. When adding a new tariff, we should ensure there is no gap between the rates. At the database level, this would require a trigger.

For 4 and 5 Rails can probably validate this before insert, but validations don't protect against concurrent inserts, so a data integrity problem could creep in.

Who Needs End Date Anyway?

What if we remove end_on from the model entirely? Interestingly the problem is greatly simplified.

A tariff change is indicated by a new row with the start date of the tariff.

Reviewing the earlier problems:

1. We no longer have an end_on field to worry about

2. Uniqueness of the tariff start_on is still enforced via a unique index.

3. Adding a tariff for the future or in the past between existing tariffs is a simple additional row.

4. Tariffs can no longer overlap.

5. Tariffs can no longer have gaps. The end date of a tariff is signaled by the new tariffs start date.

Finding the tariff for a given date is simple enough, and should be efficient on a large table with an index on meter_id, start_on:

select *
from tariffs
where meter_id = ?
and   start_on <= ?
order by start_on desc
limit 1;

While there is nothing ground breaking in this post, but I thought it was interesting how much more complex adding an explicit tariff end date made the problem.