Dates - start and end vs only start

Question

In my database, I have a prices table. Prices are valid for a set period of time or forever, but there always is a valid price at a given time.

I was wondering would you rather have a starts_at and ends_at columns, where ends_at would always be equal to the previous starts_at value, or only a starts_at and query for the last one at a given date?

I can see the benefit of having between capabilities, but on the other side it's a hassle to update a row, since it might impact ends_at dates here and there.

Answer 1

If you have only thousands of rows, do whatever. If you have millions of rows, then you will find that "between start and end" is inefficient, and there is no INDEX possible to make it fast.

When you have both a start and end in each row, the Optimizer does not know whether there are overlapping ranges. So it has to assume there could be multiple results for any lookup you do.

ip-ranges shows how to make non-overlapping ranges work efficiently. (Alas, it will take some adapting to change to your application.)

Answer 2

You can do it either way.

If you like the idea of between, then I would add a trigger on after insert that updates the ends_at of the previous record/instance.

Triggers are the best way to have duplicate(non-normalised) data synchronised.