Re: Q: How do I avoid overlapping values?

Bjørn Augestad wrote:

> Greetings,
>
> Does anyone know a good way to solve the following problem, using Oracle 8.1.5?
>
> I have a table called foo, with 3 columns, dateFrom, dateTo and price, looks something like this:
> create table foo (
> dateFrom date,
> dateTo date,
> price number
> );
>
> The table contains multiple rows, and what I need to avoid is that 2 rows overlap.
> dateFrom dateTo price
> Row 1: '01/01/1990' '12/12/1990' 100.00
> Row 2: '02/02/1990' '02/02/1991' 120.00
>
> The rows above tries to illustrate the problem, that both rows contains a (different) price for the same date range(02/02 to 12/12).
>
> I know that I can use triggers to test for overlap, but that solution doesn't scale, and the table may contain 1M rows or more.
>
> TIA
> Bjørn Augestad

Hi,
  Why don't you use two other columns. Say number of dates from "01/01/1970'. That way, you can build an index on those two columns and can test for overlapping faster. I am assuming your granularity is date.

Dogan Received on Wed Jun 02 1999 - 10:57:53 CDT