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 Received on Tue Jun 24 2008 - 05:36:41 CDT