I've to design Professional Tax table for Payroll(INDIA) , For every state it has its own prof tax according to their gross salary
Consider One state Say MAHARASHTRA
IF PR-GROSS < 2500.00 TAX= 30.00
IF PR-GROSS < 3500.00 TAX= 60.00
IF PR-GROSS < 5000.00 TAX= 120.00
IF PR-GROSS < 10000.00 TAX= 175.00
Another State AP
IF PR-GROSS < 15001.00 TAX= 0.00
IF PR-GROSS < 20001.00 TAX= 150.00
IF PR-GROSS > 20000.00 TAX= 200.00
Like these I've 8 States & respective Prof Tax according to Their gross salary
So what is the best way to represent prof tax in TABLE
A simple table would probably suffice.
SQL> create table TAX (
2 state_code varchar2(20) not null,
3 gross_threshold number(10,2),
4 tax_amount number(6,2) not null
5 );
Table created.
SQL>
SQL> insert into tax values ('MAHARASHTRA',2500,30);
1 row created.
SQL> insert into tax values ('MAHARASHTRA',3500,60);
1 row created.
SQL> insert into tax values ('MAHARASHTRA',5000,120);
1 row created.
SQL> insert into tax values ('MAHARASHTRA',10000,175);
1 row created.
SQL>
SQL> insert into tax values ('AP',15001,0);
1 row created.
SQL> insert into tax values ('AP',20001,150);
1 row created.
SQL> insert into tax values ('AP',null,200);
1 row created.
SQL>
SQL>
SQL> create or replace
2 view v_tax as
3 select
4 state_code,
5 lag(gross_threshold,1,0) over ( partition by state_code order by gross_threshold) as inclusive_lower_bound,
6 gross_threshold as exclusive_upper_bound,
7 tax_amount
8 from tax;
View created.
SQL>
SQL> select * from v_tax;
STATE_CODE INCLUSIVE_LOWER_BOUND EXCLUSIVE_UPPER_BOUND TAX_AMOUNT
-------------------- --------------------- --------------------- ----------
AP 0 15001 0
AP 15001 20001 150
AP 20001 200
MAHARASHTRA 0 2500 30
MAHARASHTRA 2500 3500 60
MAHARASHTRA 3500 5000 120
MAHARASHTRA 5000 10000 175
7 rows selected.
SQL>
By deriving (rather than storing) upper and lower bounds, you dont have to worry so much about erroneous data such as overlaps etc. The view could a materialized view as well if performance demanded it.