1

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.

2
  • 1
    Hmmm... if you can change servers, you could do worse than looking at MariaDB's temporal tables, or you could look at PostgreSQL's range types - I'm not sure what's available in this area for MySQL! Jun 20 at 19:34
  • 3
    You do not need in end column - the end of the price actuality period is defined by the next start value if exists. Also you may easily convert this structure to start-end representation with according query or view at any time.
    – Akina
    Jun 21 at 4:39

2 Answers 2

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.)

1
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.