Re: Primary Keys and Valid_From / Valid_To
Date: Tue, 24 Jun 2008 21:52:15 +0200
Hans Mayr wrote:
> Hello Thomas,
> Thanks for your mail. But you did exactly what I wanted to avoid, you
> argued about a system for articles, prices and currencies. They are
> just an example for the datastructure I have and which can not be
> changed in my context. Just imagine that a certain product is not
> always available and if it is not available there is no price. Thus,
> in the example, I need the valid_to and I need to check if there is a
> valid from, valid to period when I enter something in t_orders. One
> could solve that differently, too. I know. But given a datastructure
> like the one I described above, which I have seen in many places, how
> does one organize that?
If an item is unavailable, QTY_IN_STOCK = 0. Has nothing to do with ITEM_PRICE.
Rethink your model - you are reinventing relational databases using triggers - it will not scale as much as the native solution.
And I am worried about your opening mail, where you claim: "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."
That is simply not true*) - explain why an index read, followed by a table read would be faster than just a table read. Why do you think the optimizer uses full table scans, and ignores indexes so many times?
Try to understand Oracle; read the concepts manual, or fine printed matter by Tom Kyte, Jonathan Lewis and others.
*) true as in universal - there are exceptions. Point to be made is the misconception of Oracle as an RDBMS.
-- Regards, Frank van BortelReceived on Tue Jun 24 2008 - 14:52:15 CDT