Subject Re: [firebird-support] Looking for ideas - price change history
Author Jacqui Caren
Bob Murdoch wrote:
> I'm looking for general principles on tackling this kind of thing. I
> am free to rearchitect the design of the SKU_CHANGE table to
> accomplish this. I know I had seen something similar posted about
> this type of topic a few years ago, but can't seem to find the magic
> words to successfully search for that thread.

Two noddy solutions come to mind - and yes they are "noddy".

1) keep a "last" date and change date triggered by a before
update trigger.
This will allow where A.purchase_date > B.last_date and
A.purchase_date <= B.change_date

2) store changes against some form of unique ID

> create table SKU_CHANGE (
> sku_nbr integer not null,
> change_date date,
> cost numeric(9,2),
> retail numeric(9,2),
> ...);
for each purchase tie purchases to the change_id
then where A.change_id = B.change_id
(and A.sku_nbr = B.sku_nbr) if you want a compound key.