On Jun 24, 6:36=A0am, Hans Mayr <mayr1...@[EMAIL PROTECTED]
> 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
|
