Re: Primary Keys and Valid_From / Valid_To

On Jun 24, 6:36 am, Hans Mayr <mayr1..._at_gmx.de> 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

Hans,
There are probably many different designs to work with different currencies and effective date pricing.
I would continue with your design, with small modifications. t_articles (article_id, article_name)

    PK article_id
t_prices (article_id, currency)

    PK article_id, currency , FK: t_articles (article_id) t_eff_prices (article_id, currency,effective_date,price)

    PK article_id, currency,effective_date FK: t_prices (article_id,currency)
t_orders (order_id, order_date, article_id, currency)

    PK order_id (?) , FK: t_prices (article_id,currency)

In the table t_eff_prices use effective date instaed of Date_from and Date_to.
New row in this table with new effective date automatically gives you new "date_to"
You don't have to worry about overlapping periods.

HTH
Thomas Received on Tue Jun 24 2008 - 07:49:09 CDT