Implementing attribute and tuple rules

Previous post: Implementing business rules

The first of the five main classes of business rules according to CDM RuleFrame's classification scheme is the class "Static Data Constraint Rules". Each rule describes a state the data must always adhere to. By having them in place - and having them properly implemented of course - your data integrity is ensured. Static data constraints have 4 subclasses: attribute rules, tuple rules, entity rules and inter-entity rules. This post will cover the attribute and tuple rules.

Attribute rules



Simple attribute rules

Simple attribute rules describe the datatype and their length. These rules are rarely described separately as a business rule. Instead they are incorporated in the entity-relationship-model in the attribute definition. Although Oracle extends the number of possible datatypes with each version, the way of implementing them has not changed over the years. Just specify a datatype and length for each attribute.

Domain rules

Domain rules are implemented by check constraints. When there are a lot of domain values, or when the domain values change a lot, you are better of modelling a separate entity. And then the business rule won't be an attribute domain rule, but an inter-entity relationship rule. Domains are just the static ones with a few values. An example:

rwijk@ORA11G> create table persons
  2  ( id          number(9)
  3  , name        varchar2(10)
  4  , gender_code varchar2(1)
  5  , constraint psn_gender_code_ck check (gender_code in ('F','M'))
  6  )
  7  /

Tabel is aangemaakt.

rwijk@ORA11G> insert into persons (id,name,gender_code)
  2  values (1,'JOE','Z')
  3  /
insert into persons (id,name,gender_code)
*
FOUT in regel 1:
.ORA-02290: CHECK-beperking (RWIJK.PSN_GENDER_CODE_CK) is geschonden.

Other attribute rules

All other static data constraints that are not simple attributes or domain rules, fall in this other category. Most of them can be implemented by check constraints. For example:

rwijk@ORA11G> create table persons
  2  ( id     number(9)
  3  , name   varchar2(10)
  4  , salary number(6)
  5  , constraint psn_salary_multiple_of_100_ck check (mod(salary,100)=0)
  6  )
  7  /

Tabel is aangemaakt.

rwijk@ORA11G> insert into persons (id,name,salary)
  2  values (1,'JOE',1950)
  3  /
insert into persons (id,name,salary)
*
FOUT in regel 1:
.ORA-02290: CHECK-beperking (RWIJK.PSN_SALARY_MULTIPLE_OF_100_CK) is geschonden.

However, there used to exist a few attribute rules that could not be validated using a check constraint. For example, in the Netherlands, each bank account number must satisfy the "eleven test" (dutch: elfproef). This algorithm is designed such that switching two digits from a bank account number or mistyping one digit always makes an account number invalid. The individual digits of a bank account number are multiplied by a factor that increases from 1 until 9 when read from right to left. The sum of them should be divisible by 11. If not, the bank account number is invalid. The function below implements this small algorithm:

rwijk@ORA11G> create function satisfies_the_eleven_test
  2  ( p_bank_account_no in number
  3  ) return varchar2 deterministic
  4  is
  5    l_account_no pls_integer := p_bank_account_no;
  6    l_sum        pls_integer := 0;
  7    l_factor     integer     := 1;
  8  begin
  9    while l_account_no > 0
 10    loop
 11      l_sum := l_sum + mod(l_account_no,10) * l_factor;
 12      l_factor := l_factor + 1;
 13      l_account_no := trunc(l_account_no/10);
 14    end loop
 15    ;
 16    return case mod(l_sum,11) when 0 then 'Y' else 'N' end
 17    ;
 18  end satisfies_the_eleven_test;
 19  /

Functie is aangemaakt.

In earlier versions you would either:
- create a database trigger firing before each row insert or update calling the above function, or
- you would incorporate the function above into the api procedures and functions dealing with new or updated bank account numbers.

Note that in theory you could create some regular check constraint for this rule (think about it ...), but that would be a huge unmaintainable expression and one you cannot reuse. In Oracle11 we have virtual columns to implement them far more gracefully:

rwijk@ORA11G> create table persons
  2  ( id number(9)
  3  , name varchar2(10)
  4  , bank_account_no number(10,0)
  5  , account_satisfies_eleven_test as
  6      (cast(satisfies_the_eleven_test(bank_account_no) as varchar2(1)))
  7  , constraint psn_account_eleven_test_ck
  8      check (account_satisfies_eleven_test = 'Y')
  9  )
 10  /

Tabel is aangemaakt.

rwijk@ORA11G> desc persons
 Naam                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(9)
 NAME                                               VARCHAR2(10)
 BANK_ACCOUNT_NO                                    NUMBER(10)
 ACCOUNT_SATISFIES_ELEVEN_TEST                      VARCHAR2(1)

rwijk@ORA11G> insert into persons
  2  ( id
  3  , name
  4  , bank_account_no
  5  )
  6  values
  7  ( 1
  8  , 'JOE'
  9  , 123456789
 10  )
 11  /

1 rij is aangemaakt.

rwijk@ORA11G> insert into persons
  2  ( id
  3  , name
  4  , bank_account_no
  5  )
  6  values
  7  ( 2
  8  , 'JANE'
  9  , 183456789
 10  )
 11  /
insert into persons
*
FOUT in regel 1:
.ORA-02290: CHECK-beperking (RWIJK.PSN_ACCOUNT_ELEVEN_TEST_CK) is geschonden.

And now the rule is enforced declaratively. And it is faster than enforcing with database triggers. You can see a performance test that resembles this case at the end of this post.

So my conclusing is - until proven otherwise - that with Oracle11, all attribute rules can be enforced declaratively.


Tuple rules



The above shows two examples of tuple rules, but the one I've seen most often is the ubiquitous "startdate < enddate". Tuple rules are implemented in exactly the same way that other attribute rules are: mostly by check constraints. In real life I cannot recall ever having implemented a complex tuple rule for which a normal check constraint was not enough. But even if you have one, you can use the technique described above to implement it using a check constraint on a virtual column based on a deterministic function.

Next post: Implementing entity rules