Checking for overlaps of rectangles

I have a problem with checking data integrity if I want to cover a ndimensional  space with rectangles. I look for a solution which runs on Oracle.

2-dimensional example: Imagine a bank lending money. The two parameters determining the interest rate are credit duration and credit amount. Neither increasing duration nor increasing amount will necessarily lead to higher interest rates (compare market interest rates during autumn 2008).

CREATE TABLE interestrates

             (
                          Rate number,
                          Min_Amount number,
                          Max_Amount number,
                          Min_Duration number,
                          Max_Duration number,
                          PRIMARY KEY (`Min_Amount`,`Min_Duration`)
             );

Through a trigger or a check constraint I will have to make sure that min_amount < max_amount and min_duration < max_maxduration for all entries. I assume that the lower borders are not part of the interval and that all durations are between 1 and 36 and all amounts are between 0 and 20000.

INSERT INTO interestrates VALUES (0.05, 0, 10000, 1, 12);
INSERT INTO interestrates VALUES (0.06, 0, 10000, 12, 24);
INSERT INTO interestrates VALUES (0.055, 10000, 20000, 1, 24);
INSERT INTO interestrates VALUES (0.053, 0, 20000, 24, 36);

Now the problem is determining if there is any (amount, duration) which has no defined interest rate or more than one interest rates.

In http://groups.google.de/group/comp.databases.theory/browse_thread/thread/e208bfa769f01abb I wrote a long message describing my thoughts and a possible but very complicated solution. But nobody could provide me with an easy solution or the "standard approach" there. So I try here again with this simplified version of my problem.

I am looking forward to hearing from you.

Thanks and best,

Hans Received on Wed Jul 01 2009 - 01:14:08 CDT