I have a database to design, and one particular aspect is bugging me so I thought I'd post my issue up here and see if anyone can point me to some relevant reading material or suggest a good structure to address my issue?
The database (or at least this part of it) needs to deal with customers which have a weekly subscription to a service. I need to be able to allow each weekly subscription amount to be adjusted up to 4 weeks in advance of the current date.
As it's become more complicated than just having a simple recurring amount of money, and now the rate can vary week-to-week, I've become a bit stuck!
How about a Subscription Rate table that would include the Customer & Subscription IDs, and the Weekly Rate with a Valid Until Date
Then you can query for the Weekly Rate based on the current date. The challenge is making sure you only have one valid rate at a time or maintain a set of logic for choosing one if multiple results exist.
You can also expand the concept to include a baseRate field that would return if the Weekly Rate is not within the valid date range.