I have a database design problem which I'd like a few pointers on, please.

I need to store a data point - it's the cost of something for a given
client. This cost will normally remain static, but on occasion it might
change. I'm thinking a few changes per year, no more. Updating a value
as required in the client's record is easy, but I need to be able to
find out what the value was on a given day. In other words I need to be
able to store the history of changes to this value, and to be able to
query the value from that history.

In my inexperience, the first (and only simple) design I've come up with
is something like:

client_id int
cost int
date_changed date

and adding a record to that table when the cost changes. I can then
query the table for the given client, ordered by date_changed
descending, where the date_changed is less than (i.e. before) the given
date, and take the top row from the result.

Is this a reasonable way to solve the problem? I'm sure others must have
solved similar problems in the past - is there a 'standard' solution?

Derek Fountain wrote:
sounds like an excellent solution to me

strawberry wrote:
If you knew how inexperienced I am with DB design, you'd understand why
I'm so chuffed to hear you say that. )

But I'm still open to better ideas if there are any...

Derek Fountain wrote:
Yeah, and IF YOU KNEW how inexperienced I am you'd take my praise with
a pinch of salt

8-)

nomail (AT) hursley (DOT) ibm.com says...
The other possibility is using the "slowly changing dimensions" model from
data warehousing theory. This is a more complex design model and may not
be warranted given the size/scope of your data.

Redo your products ("somethings"?) table like:

prod_rec_no prod_id start_date end_date price other_attributes
1 1 1-jan-2004 31-jun-2004 10.00 blah
2 1 1-jul-2004 31-jan-2005 11.00 blah
3 1 1-feb-2005 31-jun-2006 11.00 other blah
4 1 1-jul-2006 null 12.00 other blah
5 2 1-jan-2005 mull 25.00 widget
.....

The null end date indicates the current values for the particular prod_id.

Then in your transaction table, store the prod_rec_no rather than the
prod_id, and you will always be able to find the attributes of that
prod_id at the time of the transaction, without having to do date
comparisons:

client_id prod_rec_no prod_volume purchase_date
99 1 5 11-feb-2004
99 3 3 21-apr-2005
99 5 2 21-apr-2005
.....

If you want to (for some reason) preserve your historical client record
values, then do the same for your clients table and store client_rec_no
rather than client_id in your transaction table.

Geoff M

Something along those lines was the first method I came up with,
although I didn't get the details pinned down quite right. I decided it
was probably too complex for my needs and opens up the possibility of
overlapping date ranges (when the data says there is more than one price
at a given date) or no date range (and hence no price) for a given date.
Of course, if I do right this wouldn't be a problem, but implementing
something simple would make more sense for me at the moment.

But thanks for the input; I had a feeling there would be some form of
pattern or something that held a standard answer to the problem.

That's a decent idea, although the archival nature of the application
means that the current price is no more likely to be searched for than
any other date. I like the idea of a trigger though - I might have
another use for that... Thanks! )