Implementing inter-entity rules

Previous post in this serie: Implementing entity rules.



Relationship rules

Relationship rules are implemented by foreign keys. This seems very straightforward, but there is a little more to it. You might have to add a not null constraint to the foreign key column to implement the optionality of the relationship. Or an extra unique key constraint on the foreign key column to implement a 1:1 relationship. And when you have allowed N:M relationships in your entity-relationship-diagram, you might even have to implement extra intersection tables.

Also, a relationship rule doesn't necessarily have to be an inter-entity rule, in case of a recursive relationship, when a row refers to a row of the same entity. Because the drawing in an entity relationship diagram of such a relationship when drawn from the top bar of the entity to the side bar, resembles a pig's ear, it's often called as such. The best known example of such a relationship rule is the "EMP.MGR -> EMP.EMPNO" relationship in the EMP-table. In this case it's really an entity rule. But this change of classification doesn't matter much, since no code is involved, the relationship rules are typically not mentioned separately.


Restricted relationship rules

The example in the picture above "An employee can only be managed by an employee with job 'MANAGER'" is a restricted relationship rule, but it isn't exactly "inter-entity". But in general, they are. And no matter if the referring entity is the same entity or another one, the implementation of these kind of rules has become quite easy. The trick is to have a fast refreshable materialized view with a join of both ends of the relationship and the relevant attributes. Once you have the rows joined together into one materialized view row, the implementation is as easy as creating one check constraint. An example:

rwijk@ORA11G> create table myemps as select * from emp where 1=0
2  /

Table created.

rwijk@ORA11G> create materialized view log on myemps with rowid
2  /

Materialized view log created.

rwijk@ORA11G> create materialized view myemps_and_their_managers
2  refresh fast on commit
3  as
4  select e.rowid erid
5       , m.rowid mrid
6       , m.job
7    from myemps e
8       , myemps m
9   where e.mgr = m.empno
10  /

Materialized view created.

rwijk@ORA11G> alter table myemps_and_their_managers
2    add constraint mgr_is_manager_ck
3    check (job = 'MANAGER')
4  /

Table altered.

rwijk@ORA11G> insert into myemps (empno,ename,job,mgr)
2  values (1,'ALAN','MANAGER',null)
3  /

1 row created.

rwijk@ORA11G> insert into myemps (empno,ename,job,mgr)
2  values (2,'BOB','CLERK',1)
3  /

1 row created.

rwijk@ORA11G> commit
2  /

Commit complete.

rwijk@ORA11G> insert into myemps (empno,ename,job,mgr)
2  values (3,'CLIFF','SALESMAN',2)
3  /

1 row created.

rwijk@ORA11G> commit
2  /
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (RWIJK.MGR_IS_MANAGER_CK) violated

Other inter-entity rules

For most other inter-entity rules the check-constraint-on-a-fast-refreshable-materialized-view solution will work as well, since most inter-entity rules describe a condition that exists between various attributes of different entities. The materialized view can take care of putting the related data in a single row and then the check constraint is a piece of cake.

The example in the picture above "The project assignment start and end dates for an employee should lie between the start and end dates defined for the project" can therefore be implemented in exactly the same way as restricted relationship rules:

rwijk@ORA11G> create table projects
  2  ( id number(9) primary key
  3  , name varchar2(30) not null
  4  , startdate date not null
  5  , enddate date not null
  6  )
  7  /

Table created.

rwijk@ORA11G> create table project_assignments
  2  ( id number(9) primary key
  3  , employee varchar2(10) not null
  4  , project_id number(9) not null references projects(id)
  5  , startdate date not null
  6  , enddate date not null
  7  )
  8  /

Table created.

rwijk@ORA11G> create materialized view log on projects with rowid
  2  /

Materialized view log created.

rwijk@ORA11G> create materialized view log on project_assignments with rowid
  2  /

Materialized view log created.

rwijk@ORA11G> create materialized view projects_date_ranges
  2  refresh fast on commit
  3  as
  4  select p.rowid prid
  5       , pa.rowid parid
  6       , p.startdate project_startdate
  7       , p.enddate project_enddate
  8       , pa.startdate assignment_startdate
  9       , pa.enddate assignment_enddate
 10    from projects p
 11       , project_assignments pa
 12   where p.id = pa.project_id
 13  /

Materialized view created.

rwijk@ORA11G> alter table projects_date_ranges
  2    add constraint valid_assignment_periods_ck
  3    check (   assignment_startdate >= project_startdate
  4          and assignment_enddate <= project_enddate
  5          )
  6  /

Table altered.

rwijk@ORA11G> insert into projects
  2  values (1,'MJPS',date '2008-01-01',date '2008-12-31')
  3  /

1 row created.

rwijk@ORA11G> insert into project_assignments
  2  values (1,'ALAN',1,date '2008-01-01',date '2008-06-30')
  3  /

1 row created.

rwijk@ORA11G> commit
  2  /

Commit complete.

rwijk@ORA11G> insert into project_assignments
  2  values (2,'BOB',1,date '2008-01-01',date '2009-01-01')
  3  /

1 row created.

rwijk@ORA11G> commit
  2  /
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (RWIJK.VALID_ASSIGNMENT_PERIODS_CK) violated

But the question is: can all other inter-entity rules be implemented using a materialized view? All the ones I can think of, can be, since the attributes are always somehow related and can be joined. I would be very interested to know if someone who is reading this knows an inter-entity rule that cannot be implemented using materialized views. Theoretically such rules must exist.

And when you come across such a rule, you would have to go back to the old method of decomposing an inter-entity rule into several dynamic data constraints, one for each triggering moment. And then handle each one separately. For example, let's just assume that the business rule "The project assignment start and end dates for an employee should lie between the start and end dates defined for the project" cannot be implemented using materialized views. The triggering moments are:

• insert of a new project assignment
• update of an existing project assignment start and/or end date
• update of the relation between project and project assignment
• update of a project's start and/or end date

If you choose database triggers to implement these decomposed rules, you'll have to accomodate for all four situations and write code for each situation. When using an api approach, you'll have to map these four triggering moments to your existing api functions and procedures and see which one of these triggering moments is actually possible. You can maybe skip a few implementations this way. And then don't forget to check which business rules to enforce when adding an extra api procedure. And make sure the resulting code, whether using the database trigger approach or the api approach, takes care of serializing access for a correct implementation.

Next post: Implementing dynamic data constraints