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:
> 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?

sounds like an excellent solution to me

strawberry wrote:
>> 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?
>
> sounds like an excellent solution to me

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:
> strawberry wrote:
> >> 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?
> >
> > sounds like an excellent solution to me
>
> 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...

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

8-)

nomail@hursley.ibm.com says...
>
> 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.

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

On Tue, 25 Jul 2006 16:35:07 +0100, Derek Fountain wrote:
> 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?

It's a good one. The only POSSIBLE improvement is one that would require
some application code (or a trigger, perhaps) to maintain and that would
be to add an "active" flag on the record to make finding current prices
much faster than a huge collection of MAX() functions to find the most
recent date. (I do this kind of thing all the time at my day job. The
choice of using a trigger or the application to set the flag mostly
depends on whether the scope includes OTHER applications updating the
data or not. If it's just my/our stuff, I'll have the application do it.
If it's something else potentially, I'll make the database enforce it
via a trigger, so someone else's process won't screw it up.)

--
60. My five-year-old child advisor will also be asked to decipher any code I
am thinking of using. If he breaks the code in under 30 seconds, it will
not be used. Note: this also applies to passwords.
--Peter Anspach's list of things to do as an Evil Overlord

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

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.

> It's a good one. The only POSSIBLE improvement is one that would require
> some application code (or a trigger, perhaps) to maintain and that would
> be to add an "active" flag on the record to make finding current prices
> much faster than a huge collection of MAX() functions to find the most
> recent date.

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