Automatic Adjusting Range Table

Question

I have a table with a start date range, an end date range, and a few other additional columns. On input of a new record, I want to automatically adjust any overlapping date ranges (shrinking them, splitting them, or deleting them to allow for the new input -- see algorithm below). I also want to ensure that no overlapping records can accidentally be inserted into this table.

I'm using Oracle and Java for my application code. How should I enforce the prevention of overlapping date ranges and also allow for automatically adjusting overlapping ranges? Should I create an AFTER INSERT trigger, with a dbms_lock to serialize access, to prevent the overlapping data. Then in Java, apply the logic to auto adjust everything? Or should that part be in PL/SQL in stored procedure call? This is something that we need for a couple other tables so it'd be nice to abstract.

If anyone has something like this already written, please share :)

I did find this reference: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:474221407101

Here's an example of how each of the 4 overlapping cases should be handled for adjustment on insert:

= Example 1 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')

Input
(20, 50, 'A')
Gives
(0, 10, 'X')
**(20, 50, 'A')
**(51, 100, 'Z')
(200, 500, 'Y')


= Example 2 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')

Input 
(40, 80, 'A')
Gives
(0, 10, 'X')
**(30, 39, 'Z')
**(40, 80, 'A')
**(81, 100, 'Z')
(200, 500, 'Y')


= Example 3 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')

Input
(50, 120, 'A')
Gives
(0, 10, 'X')
**(30, 49, 'Z')
**(50, 120, 'A')
(200, 500, 'Y')


= Example 4 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')

Input
(20, 120, 'A')
Gives
(0, 10, 'X')
**(20, 120, 'A')
(200, 500, 'Y')

The algorithm is as follows:

given range = g; input range  = i; output range set = o

if i.start <= g.start
  if i.end >= g.end
    o_1 = i
  else
    o_1 = i
    o_2 = (i.end + 1, g.end)
else
  if i.end >= g.end
    o_1 = (g.start, i.start - 1)
    o_2 = i
  else
    o_1 = (g.start, i.start - 1)
    o_2 = i
    o_3 = (i.end + 1, g.end)

Answer 1

I've generally seen data models like that having the starting point of the range being the only one being tracked, where the ending point is then implicit. So it'd be

CREATE TABLE MY_TABLE
(START_AT    NUMBER,
 VALUE       NUMBER,
 CONSTRAINT MY_TABLE_PK (START_AT)
);

If you need to present values in the existing format, you could use analytics and a materialized view, using LEAD(START_AT) OVER (ORDER BY START_AT) (I think it's right, but untested) to get the interpreted end value.

Answer 2

AskTom's article gives a good example of how it can be done, but note that this example locks the whole table which will severely impact concurrency of your application.

If concurrency is a matter for you, you should just add a sequence column (with ORDER option if you use RAC) and write a query like this:

SELECT  *
FROM    (
        SELECT  *, rownum AS rn
        FROM    mytable
        WHERE   start_date <= :date
                AND end_date >= :date
        ORDER BY
                seq DESC
        )
WHERE   rn = 1

to find out effective range (and other data) for a given date.

This will return the last inserted range containing the given date.

You can make this query a little bit more efficient by running a maintenance procedure that would get rid of the overlapping ranges on a timely basis (like described in the post) and rewriting the query like this:

SELECT  *
FROM    (
        SELECT  *, rownum AS rn2
        FROM    (
                SELECT  *
                FROM    (
                        SELECT  *, rownum AS rn
                        FROM    mytable
                        WHERE   seq <= :lseq
                                AND start_date <= :date
                                AND end_date >= :date
                        ORDER BY
                                start_date DESC
                        )
                WHERE   rn = 1
                UNION ALL
                SELECT  *
                FROM    (
                        SELECT  *, rownum AS rn
                        FROM    mytable
                        WHERE   seq > :lseq
                                AND start_date <= :date
                                AND end_date >= :date
                        ORDER BY
                                seq DESC
                        )
                WHERE   rn = 1
                )
        ORDER BY
                seq DESC
        )
WHERE   rn2 = 1

Create the indexes on start_date and seq for this to work fast.

The latter query will select the first matching range from the processed ranges (which are known to be non-overlapping), the first matching range from the unprocessed ranges (which are few) and of the two records, will select the one with the highest seq.