I am doing this in a project where every table must be 100% auditable.

Every table has audit information ...

Item ID (I use a GUID because of other requirements)
Sequence ID (Integer)
Effective Timestamp
Ending Timestamp
... plus others

before any of the business information.

No business data is ever deleted or updated. Instead of a delete, I
invalidate (set the ending timestamp). Instead of an update, I
invalidate the old record and create a new one.

The current record always has a null timestamp in a real time system, or
you can anticipate using "where current time between (Effective and
Ending)

On Thu, 2005-01-06 at 07:20, Bob Murdoch wrote:
>
> (reposted from Firebird-general per admins advice)
>
> I'm having a mental block solving this problem -
>
> I have a table that contains information regarding the price of a
> piece of merchandise, and a table that contains the inventory of a
> warehouse:
>
> create table SKU (
> sku_nbr integer not null,
> cost numeric(9,2),
> retail numeric(9,2),
> ...);
>
> create table INVENTORY (
> sku_nbr integer not null,
> warehouse_nbr integer not null,
> receipt_date date default 'today',
> ...);
>
>
> The price of a sku may change a couple of times per month. I had
> created a table quite a while ago that was populated via an after
> update trigger on the sku table, which simply wrote the old values of
> the row to a table, along with the timestamp of when the change
> occurred:
>
> create table SKU_CHANGE (
> sku_nbr integer not null,
> change_date date,
> cost numeric(9,2),
> retail numeric(9,2),
> ...);
>
>
> My task is to now create a query that will, for each piece of
> inventory, return the cost and retail of the piece as of the day of
> receipt - not the current values in the SKU table. This is
> complicated by the fact that the sku_change table only contains the
> old values upon change, so it's not a simple matter of just joining to
> that table on SKU_CHANGE.CHANGE_DATE = INVENTORY.RECEIPT_DATE.
>
>
> 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.
>
>
> Thanks in advance for sharing your thoughts.
>
> Bob M..
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>