Primary Keys and Valid_From / Valid_To

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
.

Relevant Pages

  • Re: Primary Keys and Valid_From / Valid_To
    ... I have basic questions on how one best organizes primary keys (and ... also foreign keys) and data integrity in an enviroment where one has ... t_prices on article_id and currency. ... exactly one price / currency for each entry in t_orders. ...
    (comp.databases.oracle.misc)
  • Re: Primary Keys and Valid_From / Valid_To
    ... I have basic questions on how one best organizes primary keys (and ... also foreign keys) and data integrity in an enviroment where one has ... t_prices on article_id and currency. ... exactly one price / currency for each entry in t_orders. ...
    (comp.databases.oracle.misc)
  • Re: Why US dollars ?
    ... the safest currency. ... You can see the result on the price of oil. ... Let's take your house as an example. ... Then George Soros comes along and short-sells your house, ...
    (uk.politics.economics)
  • Re: Obama
    ... Without knowing more about the relationship and currency markets, I'm not going to comment further on this 'linking.' ... There's probably some much slower "normal" increase in price in things they get from elsewhere and in things they buy that are produced within their own country. ... Oil used to cost 30 euros or 30 dollars. ... As far as I can recall, you haven't even admitted that the 40% drop in the dollar against other currencies means we'll have to pay 40% more for imports, and also have to pay more for home-based goods that are affected by imports. ...
    (soc.retirement)
  • Daily Forex Commentary
    ... "Derivatives are constructed on the basis of the theory of efficient markets. ... greed validated by price action leads to more money being thrown onto the price ... currency they have, but synchronized fiscal policies they have not. ... Europe on the other ...
    (soc.culture.malaysia)