Re: Primary Keys and Valid_From / Valid_To

Hans Mayr wrote:
> Hello,
>
> I have basic questions on how one best organizes primary keys (and
> also foreign keys) and data integrity in an enviroment where one has
> valid_from / valid_to columns. Example:
>
> Say I have tables t_articles (article_id, article_name) and t_prices
> (article_id, currency, price). Then I would create a primary key on
> t_prices on article_id and currency. Oracle will make sure that
> article_id and currency are unique. And it will make data access on
> t_prices faster through the associated index. If I have a table
> t_orders (order_id, order_date, article_id, currency) I can make shure
> through a foreign key that (article_id, currency) exist in t_prices.
> Everything is wonderful.
>
> But now I want the prices to change and I introduce two new columns
> valid_from and valid_to in t_prices. Suddenly I loose a lot of the
> power of keys:
>
> * I have to make sure that the intervals [Valid_from, Valid_to] do not
> intersect.
> * A primary key (article_id, currency, valid_from) on t_prices is not
> clean (in my understanding) because I actually do not identify one
> line by this tripple but by article_id, currency and date (e.g.
> order_date) "between valid_from and valid_to".
> * I can not use a foreign key anymore to make sure that there is
> exactly one price / currency for each entry in t_orders.
>
> How does one solve these problems? Is there a way to reactive the
> power of primary and foreign keys? Or do I have to go through
> triggers?
>
> And just to make sure that there is no misunderstanding: My example
> given above is just an example to illustrate the problem and if one
> really had to work with orders, articles and prices one might solve it
> differently.
>
> Thanks and best,
>
> Hans

The more often I reread this, the more it begins to smell like homework.

Since when does any firm keep articles in different currencies? Check with your legal department - afaik, bookkeeping is done in the currency of the country where your firm resides.

Of course, you can do business in different currencies, but that means currency is not introduced before it hits the order, possibly order_line (which I doubt).

Now, introduce discount and sale actions, valid only for a certain period. These are a nightmare, but hey.

That leaves:
currency (id, date, roe); PK is (id, date). article (id, name, description, uom); PK is id; name should   probably an UK.
price (art_id, amount, qty_per); art_id points to article(id) customer (id, name)
order (id, cust_id, cur_id, date_ordered); cust_id points   to customer(id); cur_id points to currency(id). order_line (id, ord_id, art_id, qty, uom) actions (art_id, start_date, end_date, discount, uom)

You happily produce, acquire of whatever articles. I want to buy these, and enter an order for X, Y and Z. I pay in euro, your accounting is in USD. You process that order:

- I am a known customer (if not, enter me)
- you create a new order for me, cur_id "EUR".
- three order lines are added;
-- 1 for X , qty 1, unit-of-measure: kg
-- 1 for Y, qty 1000, uom: pcs
-- 1 for Z, qty 3, uom: cake-box-of-100

-- process the order, pick up currency and date.
-- process all order lines, per line, you:
---- calculate the order line value, based on:
------ price (use art_id), and actions (use art_id,
------ and order(date_ordered), and roe (again,
------ using order(date_ordered).

Very basic, of course, and very rough. It's the order_line price calculations that will hurt the most, as you cannot purge your actions table. And if your action is from July, 1st to July, 14th, and I order on July, 4th, there's not an index that will have a hit.

-- 

Regards,
Frank van Bortel