Re: Primary Keys and Valid_From / Valid_To

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 Received on Wed Jun 25 2008 - 00:50:10 CDT