PHP MySQL - Storing Configuration Then Reffering To The Config By Date

Question

I'm building an Insurance Site, and will be selling policies online.

I'm designing the database at the minute and need some advice on the best way to store the rates I'm using to calculate the premium.

There's a clever way of selecting the rates, based on the date the policy was purchased - from a rates table, using like an 'effective date' field or something.

Has anyone done this? I'm struggling to find examples. I know what i'm trying to achieve just not how to build the tables to do it.

Answer 1

Order the rates table by the date effective and pick the first.

SELECT * FROM Rates
  WHERE effective > ?
  OREDR BY effective 
  LIMIT 1

This will return the first (lowest) date for which the rate is effective.

PS: The ? is where your date goes.