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

On Jun 24, 6:36*am, Hans Mayr <mayr1...@gmx.de> wrote:
> 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

Hans,
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)
PK article_id
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
(article_id,currency)
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
Date_to.
New row in this table with new effective date automatically gives you
new "date_to"
You don't have to worry about overlapping periods.

HTH
Thomas

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?

Best,

Hans

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?
>
> Best,
>
> Hans

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 Bortel

On Jun 24, 10:34*am, Hans Mayr <mayr1...@gmx.de> 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?
>
> Best,
>
> Hans

Hans,
If you cannot change the model nor the structure, you may be left with
only one option.
That is a 'trigger option'.

HTH
Thomas

Hans Mayr wrote:
> 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

The more often I reread this, the more it begins to smell
like homework.

Since when does any firm keep articles in different
currencies? Check with your legal department - afaik,
bookkeeping is done in the currency of the country
where your firm resides.

Of course, you can do business in different currencies,
but that means currency is not introduced before it hits
the order, possibly order_line (which I doubt).

Now, introduce discount and sale actions, valid only
for a certain period. These are a nightmare, but hey.

That leaves:
currency (id, date, roe); PK is (id, date).
article (id, name, description, uom); PK is id; name should
probably an UK.
price (art_id, amount, qty_per); art_id points to article(id)
customer (id, name)
order (id, cust_id, cur_id, date_ordered); cust_id points
to customer(id); cur_id points to currency(id).
order_line (id, ord_id, art_id, qty, uom)
actions (art_id, start_date, end_date, discount, uom)

You happily produce, acquire of whatever articles.
I want to buy these, and enter an order for X, Y and Z.
I pay in euro, your accounting is in USD.
You process that order:
- I am a known customer (if not, enter me)
- you create a new order for me, cur_id "EUR".
- three order lines are added;
-- 1 for X , qty 1, unit-of-measure: kg
-- 1 for Y, qty 1000, uom: pcs
-- 1 for Z, qty 3, uom: cake-box-of-100
When you process the bill, you:
-- process the order, pick up currency and date.
-- process all order lines, per line, you:
---- calculate the order line value, based on:
------ price (use art_id), and actions (use art_id,
------ and order(date_ordered), and roe (again,
------ using order(date_ordered).

Very basic, of course, and very rough. It's the order_line
price calculations that will hurt the most, as you cannot
purge your actions table. And if your action is
from July, 1st to July, 14th, and I order on July, 4th,
there's not an index that will have a hit.
--

Regards,
Frank van Bortel

Frank van Bortel wrote on 24.06.2008 22:21:
> Since when does any firm keep articles in different
> currencies? Check with your legal department - afaik,
> bookkeeping is done in the currency of the country
> where your firm resides.
I know at least one large computer manufacturer who manages prices in several
currencies using quite sophisticated rules for converting (based on stord
conversion rates) and rounding to get "nice" prices (e.g. 299€ instead of
287,43€ which would result from applying the conversion rate).

> Of course, you can do business in different currencies,
> but that means currency is not introduced before it hits
> the order, possibly order_line (which I doubt).

So you wouldn't display prices in a shop before the user hits the "Order now"
button?

> Now, introduce discount and sale actions, valid only
> for a certain period. These are a nightmare, but hey.

Nightmare or not, this is *very* common when building shops or similar systems
(B2B websites, procurement systems)


Thomas

On 24.06.2008 12:36, Hans Mayr wrote:

> 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".

An alternative approach would be to store just a single Date (start or
end of the interval). Downside is that you cannot manage holes or -
depending on the end of the range you store - articles starting or
stopping being available at some point in time.

> * I can not use a foreign key anymore to make sure that there is
> exactly one price / currency for each entry in t_orders.

Last time I checked FK's were used to ensure referential integrity - not
uniqueness. :-)

> 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?

I guess so. At least I would not know another solution off the top of
my head. Triggers are no black magic and this does not seem a bad use
case for them - combined with a check constraint that ensures valid_to >
valid_from.

The basic reason why you cannot use a PK any more is that a range
basically represents multiple values and the comparison is set based and
not equivalence based.

Kind regards

robert

Hi everybody,

Thanks for all the feed back. I picked up the valid_from / valid_to
concept as I had seen it in many places and I was told that this was
the way to do it. I never felt quite comfortable with it as I never
really understood it. From your posts I understand that this concept
is not as universally used as I was told, to the contrary. Yes, I will
look into the concepts manual and I will not pick up concepts anymore
which I do not really understand. I'll have to see if I will redesign
the existing parts of my DB or work with triggers.

Frank's statement "Point to be made is the misconception of Oracle as
an RDBMS." hit me. It is not an RDBMS? Any good links on that? Well I
know that Oracle is more powerful than a simple RDBMS.

Thanks and best,

Hans