Re: Primary Keys and Valid_From / Valid_To
Date: Tue, 24 Jun 2008 05:49:09 -0700 (PDT)
On Jun 24, 6:36 am, Hans Mayr <mayr1..._at_gmx.de> wrote:
> 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
> * 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
> 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
> Thanks and best,
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)
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
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
New row in this table with new effective date automatically gives you new "date_to"
You don't have to worry about overlapping periods.
Thomas Received on Tue Jun 24 2008 - 07:49:09 CDT