Using Indexes for your business logic

Intruction

In a recent post I wrote about indexing of NULL values. We can utilize the fact that NULL values are not indexed under normal circumstances to implement our business logic inside the data model itself. You wonder what I mean by that? I will show you in a short example.

The business rule

In our example application we manage contracts between different parties at a time. The contracts can be active or canceled. Only one active contract is allowed between two parties. Canceled contracts do not matter.
Many times I see those logic being implemented using triggers or code inside the application. But how do you handle concurrency and locking? How do you handle changes that check old versions of the data while someone else is also doing changes? Why not use the database to handle concurrency and locking? The database should now it best.

The data model

These are the two simple tables, should be self-explaining:

SQL> create table parties (
  2    id     number,
  3    party_name varchar2(30),
  4    constraint pk_parties primary key (id)
  5  );

Table created.

SQL> create table contracts (
  2    party1_id number,
  3    party2_id number,
  4    contract_state varchar2(10) check ( contract_state in ('enabled', 'canceled') ),
  5    constraint fk_party1_id foreign key (party1_id) references parties(id),
  6    constraint fk_party2_id foreign key (party2_id) references parties(id)
  7  );

Table created.

Now I need some simple test data:

SQL> insert into parties values(1, 'Party 1');

1 row created.

SQL> insert into parties values(2, 'Party 2');

1 row created.

SQL> insert into parties values(3, 'Party 3');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from parties;

        ID PARTY_NAME
---------- ------------------------------
         1 Party 1
         2 Party 2
         3 Party 3

The implementation

Now we could start inserting the contracts into the mapping table. But as of now we could insert anything:

SQL> insert into contracts values (1, 2, 'enabled');

1 row created.

SQL> insert into contracts values (1, 2, 'enabled');

1 row created.

According to our business rule these two entries must not exist at the same time.

SQL> rollback;

Rollback complete.

We implement the rule by creating a unique index which only contains value pairs of enabled contracts. IDs of canceled contracts will be mapped to NULL which suppresses indexing of these value pairs and thus uniqueness will not be checked by the database.

SQL> create unique index uq_contracts_business_rule on contracts (
  2    case when contract_state='enabled' then party1_id else null end,
  3    case when contract_state='enabled' then party2_id else null end
  4  );

Index created.

Let’s check how and if that works:

SQL> insert into contracts values (1, 2, 'enabled');

1 row created.

Ok, this is the first row, nothing special.

SQL> insert into contracts values (1, 3, 'enabled');

1 row created.

This is a contract between another value pair, inserting is permitted.

SQL> insert into contracts values (1, 2, 'canceled');

1 row created.

That value pair has already a contract, but this contract is in ‘canceled’ state, so it does not matter according to our business rule. This is achieved by the CASE expression inside the index. If the state is not ‘enabled’ the CASE expression returns NULL which in turn means that no index entry is generated and thus also uniqueness is not being checked.

SQL> insert into contracts values (1, 2, 'enabled');
insert into contracts values (1, 2, 'enabled')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UQ_CONTRACTS_BUSINESS_RULE) violated

Now the same value pair with ‘enabled’ state. The CASE expression inside the index now returns the IDs, the database tries to add row to the index and fails because an identical value pair is already indexed. That means our implementation of that business rule works 🙂

Conclusion

This simple example outlines the implementation of complex business rules inside the data model itself. You don’t need to take care of locking or handling concurrency issues or anything else that could happen. The database does it for you. You can also create your own functions inside the database that control indexing of values which allows you to build more complex rules. Just be creative. And the best, it’s all for free, function based indexes are part of Standard Edition of Oracle Database.

Advertisements