Current values are better stored with the inventory item.

Use the current price table for lookup, but store the value, not the
link, as the current value is not relevant once the value has changed.

Raymond.

Bob Murdoch wrote:

> 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.
>
> Thanks in advance for sharing your thoughts.
>
> Bob M..
>
>
>
> Community email addresses:
> Post message: Firebird-general@yahoogroups.com
> Subscribe: Firebird-general-subscribe@yahoogroups.com
> Unsubscribe: Firebird-general-unsubscribe@yahoogroups.com
> List owner: Firebird-general-owner@yahoogroups.com
>
> Shortcut URL to this page:
> http://www.yahoogroups.com/community/Firebird-general
> Yahoo! Groups Links
>
>
>
>
>
>
>
>