Income tax brackets. Sales commission rates. Loan pre-payment penalty charges. Those are types of master data that get defined as range-based matrices. An important validation for such data is that the slabs should have no overlapping ranges: any input value should match only a single row in the master range definition.
When range-based matrices are stored in a database table, how can overlapping ranges be prevented? Read on to find out the solution for an Oracle database.
Let’s assess the problem first, with the example of an affiliate marketing rates table (affiliate_rate), which defines the benefit percent (rate_value) in terms of count of goods sold by the affiliate.
SQL> desc affiliate_rate
Name Null? Type
----------------- -------- ------------
COUNT_FROM NUMBER(4)
COUNT_TO NUMBER(4)
RATE_VALUE NUMBER(5,2)
SQL> select * from affiliate_rate;
COUNT_FROM COUNT_TO RATE_VALUE
---------- ---------- ----------
1 6 4.00
7 30 6.00
31 110 6.50
111 320 7.00
count_from and count_to representing the lower and upper limits of the range, both inclusive. So, an affiliate with 10 goods sold will have a rate_value 6.00, which another affiliate with 200 goods sold will have a rate_value 7.00.
In the absence of any validation on affiliate_rate to stop overlapping ranges, a new record with range (310, 630) can get inserted into the table:
SQL> -- Insert new row with SQL> -- overlapping count_from SQL> insert into affiliate_rate 2 (count_from 3 , count_to 4 , rate_value) 5 values 6 (310 -- Overlapping value 7 , 630 8 , 7.50 9 ); 1 row created.
SQL> select * from affiliate_rate;
COUNT_FROM COUNT_TO RATE_VALUE
---------- ---------- ----------
1 6 4.00
7 30 6.00
31 110 6.50
111 320 7.00
310 630 7.50
Now, we are no longer sure of the rate_value when the count of goods sold is , say, 314. Is it 7.00 or is it 7.50?
Overlapping Ranges: What to check for
There are four overlap scenarios to check for and block:
- count_from of the new range falls within an existing range
- count_to of the new range falls within an existing range
- The new range is a subset of an existing range
- An existing range is a subset of the new range
This SQL should result in an error:
-- Condition 1: Insert overlapping count_from
insert into affiliate_rate
(count_from
, count_to
, rate_value)
values
(310 -- Overlapping value
, 630
, 7.50
);>
This SQL should result in an error:
-- condition2: Insert overlapping count_to
insert into affiliate_rate
(count_from
, count_to
, rate_value)
values
(0
, 2 -- Overlapping value
, 1.00
);
This SQL should result in an error:
-- Condition 3: New range is a subset
insert into affiliate_rate
(count_from
, count_to
, rate_value)
values
(120
, 300 -- subset of {111,320}
, 7.50
);
This SQL should result in an error:
-- Condition 4: Old range is a subset
insert into affiliate_rate
(count_from
, count_to
, rate_value)
values
(100
, 330 -- superset of {111,320}
, 7.50
);
Overlapping Ranges Blocker Toolkit
The validations to check for and block overlapping ranges can be implemented with a combination of these DB objects:
- A package that defines two table object types — one to store new rowids, another empty.
- A before, statement level trigger to set the state to a known, consistent state using the table object types
- An after, row level trigger to capture the changes for each row
- An after, statement level trigger to do the overlap validation
Creating the objects in the database for affiliate_rate table:
Step 1: Creating the package
SQL> -- Package to maintain the "state" SQL> -- of new or updated rows. SQL> create or replace package 2 util_pkg 3 as 4 type rowid_tbl is 5 table of rowid 6 index by binary_integer; 7 8 new_rowid_tbl rowid_tbl; 9 -- to hold new rows 10 11 empty_rowid_tbl rowid_tbl; 12 -- to reset the table type 13 end; 14 / Package created.
Step 2: Creating the before, statement level trigger
SQL> -- Before insert, reset the SQL> -- state of new_rowid_tbl. SQL> create or replace trigger 2 affiliate_rate_bi 3 before insert on affiliate_rate 4 begin 5 util_pkg.new_rowid_tbl := 6 util_pkg.empty_rowid_tbl; 7 end; 8 / Trigger created.
Step 3: Creating the after, row level trigger
SQL> -- After insert of each row, SQL> -- store the rowid in new_rowid_tbl. SQL> create or replace trigger 2 affiliate_rate_airow 3 after insert on affiliate_rate 4 for each row 5 begin 6 util_pkg.new_rowid_tbl( 7 util_pkg.new_rowid_tbl.count + 1 8 ) := 9 :new.rowid; 10 end; 11 / Trigger created.
Step 4: Creating the after, statement level trigger for overlap validation
SQL> -- After insert, serialize access to SQL> the table with dbms_lock and check for SQL> -- overlaps between new and existing ranges SQL> create or replace trigger 2 affiliate_rate_ai 3 after insert on affiliate_rate 4 declare 5 status int; 6 l_rec affiliate_rate%rowtype; 7 l_cnt number; 8 begin 9 status := dbms_lock.request 10 (id => 123, -- random id for this e.g. 11 lockmode => dbms_lock.x_mode, -- exclusive 12 release_on_commit => TRUE ); 13 14 for i in 1 .. util_pkg.new_rowid_tbl.count 15 loop 16 select * into l_rec 17 from affiliate_rate 18 where rowid = util_pkg.new_rowid_tbl(i); 19 20 select count(*) 21 into l_cnt 22 from affiliate_rate 23 where rowid <> util_pkg.new_rowid_tbl(i) 24 -- New range overlaps with existing count_from 25 and (count_from between l_rec.count_from 26 and l_rec.count_to 27 -- New range overlaps with existing to 28 or count_to between l_rec.count_from 29 and l_rec.count_to 30 -- New range is a subset of existing range 31 or (count_from < l_rec.count_from 32 and l_rec.count_from < count_to)); 33 34 if ( l_cnt > 0 ) then 35 raise_application_error( -20001, 36 'Overlap: ' || l_rec.count_from || ', ' || 37 l_rec.count_to || ' rejected' ); 38 end if; 39 end loop; 40 end; 41 / Trigger created.
Overlapping Ranges Blocker — in Action
Let’s check the outcome in each of the four overlap scenarios described above, as well as in a no-overlap scenario.
- count_from of the new range falls within an existing range
- count_to of the new range falls within an existing range
- The new range is a subset of an existing range
- An existing range is a subset of the new range
- No overlap
SQL> -- Condition 1:
SQL> -- Insert overlapping count_from
SQL> insert into affiliate_rate
2 (count_from
3 , count_to
4 , rate_value)
5 values
6 (310 -- Overlapping value
7 , 630
8 , 7.50
9 );
insert into affiliate_rate
*
ERROR at line 1:
ORA-20001: Overlap: 310, 630 rejected
ORA-06512: at "HR.AFFILIATE_RATE_AI", line 32
ORA-04088: error during execution of trigger 'HR.AFFILIATE_RATE_AI'
SQL> -- Condition 2:
SQL> -- Insert overlapping count_to
SQL> insert into affiliate_rate
2 (count_from
3 , count_to
4 , rate_value)
5 values
6 (0
7 , 2 -- Overlapping value
8 , 1.00
9 );
insert into affiliate_rate
*
ERROR at line 1:
ORA-20001: Overlap: 0, 2 rejected
ORA-06512: at "HR.AFFILIATE_RATE_AI", line 32
ORA-04088: error during execution of trigger 'HR.AFFILIATE_RATE_AI'
SQL> -- Condition 3: New range is a subset
SQL> insert into affiliate_rate
2 (count_from
3 , count_to
4 , rate_value)
5 values
6 (120
7 , 300 -- subset of {111,320}
8 , 7.50
9 );
insert into affiliate_rate
*
ERROR at line 1:
ORA-20001: Overlap: 120, 300 rejected
ORA-06512: at "HR.AFFILIATE_RATE_AI", line 32
ORA-04088: error during execution of trigger 'HR.AFFILIATE_RATE_AI'
SQL> -- Condition 4: Old range is a subset
SQL> insert into affiliate_rate
2 (count_from
3 , count_to
4 , rate_value)
5 values
6 (100
7 , 330 -- superset of {111,320}
8 , 7.50
9 );
insert into affiliate_rate
*
ERROR at line 1:
ORA-20001: Overlap: 100, 330 rejected
ORA-06512: at "HR.AFFILIATE_RATE_AI", line 32
ORA-04088: error during execution of trigger 'HR.AFFILIATE_RATE_AI'
SQL> -- Condition 5: No overlap
SQL> insert into affiliate_rate
2 (count_from
3 , count_to
4 , rate_value)
5 values
6 (321
7 , 330
8 , 7.50
9 );
1 row created.
SQL> select * from affiliate_rate;
COUNT_FROM COUNT_TO RATE_VALUE
---------- ---------- ----------
1 6 4.00
7 30 6.00
31 110 6.50
111 320 7.00
321 330 7.50
Notes
Thanks to asktom for the template for this solution — have worked it in various projects with success.
If you want contiguous ranges, a design that requires less validation is the one using upper bounds only – see grade_master table in Using LEAD/LAG to Place a Value within Extents.
