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
|
