Business Rule: Only One Clerk per Department – 11g Style
During the 7Up workshop, a workshop geared towards experienced developers who want to get up to speed with everything that happened in Oracle since release 7, one of the “tricks” that passes by is how to implement the Business Rule:
Only one Clerk per Department is Allowed
The way we show how to declaratively implement this Business Rule is by using a Unique Function Based Index. Every now and then someone will comment that the implementation is a hack, “cause an Index is not meant to implement a Business Rule, it’s there to enhance performance.”
I don’t necessarily agree with this, but I do believe that Oracle 11g offers a more elegant solution (be it very similar, but is considered less of a hack).
First let’s take a look at the “hack”, with the Unique Function Based Index. Then we’ll look at the way to do the same in Oracle 11g. Lastly I will show you the similarity between the two.
Unique Function Based Index
First off, the Function Based Index. FBI’s were introduced in Oracle 9i and allow you to create an index using a function. Especially useful when you have a query like
<span style="#606060"> 1:</span> <span style="#0000ff">select</span> *
<span style="#606060"> 2:</span> <span style="#0000ff">from</span> emp
<span style="#606060"> 3:</span> <span style="#0000ff">where</span> <span style="#0000ff">upper</span> (ename) = :ename;
When the ename column in the example above is indexed (the regular way) you can not use this index because of the UPPER function.
Indexing the ename column with the UPPER function would allow use of the index. Here is the code for this example:
<span style="#606060"> 1:</span> <span style="#0000ff">create</span> <span style="#0000ff">index</span> upper_name
<span style="#606060"> 2:</span> <span style="#0000ff">on</span> emp (<span style="#0000ff">upper</span> (ename))
When the index is created this way, it can be used with the first statement.
The way to implement the Business Rule “Only one Clerk per Department”:
<span style="#606060"> 1:</span> <span style="#0000ff">create</span> <span style="#0000ff">UNIQUE</span> <span style="#0000ff">index</span> one_clerk_per_deptno
<span style="#606060"> 2:</span> <span style="#0000ff">on</span> emp (<span style="#0000ff">case</span> job
<span style="#606060"> 3:</span> <span style="#0000ff">when</span> <span style="#006080">'CLERK'</span> <span style="#0000ff">then</span> deptno
<span style="#606060"> 4:</span> <span style="#0000ff">end</span>)
Using a simple CASE expression only DEPTNO are used in the index when the JOB is equal to CLERK. Because it is a unique index, there can only be one CLERK in each department.
Virtual Columns
Some might call the implementation of the Business Rule in the previous section a “hack”. Instead of using indexes to increase performance, the index is used to declaratively implement a Business Rule.
Oracle 11g introduced Virtual Columns. I really like Virtual Columns more and more because of the multitude of possibilities. Using Virtual Columns to implement this Business Rule is very straight forward.
Instead of using the simple CASE expression in an index, we will use it in the definition of the Virtual Column:
<span style="#606060"> 1:</span> <span style="#0000ff">alter</span> <span style="#0000ff">table</span> emp
<span style="#606060"> 2:</span> <span style="#0000ff">add</span> one_clerk_per_dept <span style="#0000ff">as</span> (<span style="#0000ff">case</span>
<span style="#606060"> 3:</span> <span style="#0000ff">when</span> job = <span style="#006080">'CLERK'</span>
<span style="#606060"> 4:</span> <span style="#0000ff">then</span> deptno
<span style="#606060"> 5:</span> <span style="#0000ff">end</span>)
<span style="#606060"> 6:</span> /
With the statement above an extra column is added to the EMP table. This Virtual Column, based on the CASE expression, will only show a value when the JOB equals CLERK.
<span style="#606060"> 1:</span> <span style="#0000ff">SQL</span>> <span style="#0000ff">select</span> job
<span style="#606060"> 2:</span> 2 , one_clerk_per_dept
<span style="#606060"> 3:</span> 3 <span style="#0000ff">from</span> emp
<span style="#606060"> 4:</span> 4 /
<span style="#606060"> 5:</span>
<span style="#606060"> 6:</span> JOB ONE_CLERK_PER_DEPT
<span style="#606060"> 7:</span> -------<span style="#008000">-- ------------------</span>
<span style="#606060"> 8:</span> CLERK 20
<span style="#606060"> 9:</span> SALESMAN
<span style="#606060"> 10:</span> SALESMAN
<span style="#606060"> 11:</span> MANAGER
<span style="#606060"> 12:</span> SALESMAN
<span style="#606060"> 13:</span> MANAGER
<span style="#606060"> 14:</span> MANAGER
<span style="#606060"> 15:</span> ANALYST
<span style="#606060"> 16:</span> PRESIDENT
<span style="#606060"> 17:</span> SALESMAN
<span style="#606060"> 18:</span> CLERK 20
<span style="#606060"> 19:</span> CLERK 30
<span style="#606060"> 20:</span> ANALYST
<span style="#606060"> 21:</span> CLERK 10
As you can see in the resultset above, the ONE_CLERK_PER_DEPT has a value in its column when the JOB is a CLERK. Because the Business Rule says “Only one Clerk per Department” we need to make this column UNIQUE. With this data this is not possible, because department has two CLERKS. One has to go…
<span style="#606060"> 1:</span> <span style="#0000ff">SQL</span>> <span style="#0000ff">update</span> emp
<span style="#606060"> 2:</span> 2 <span style="#0000ff">set</span> job = <span style="#006080">'NOCLERK'</span>
<span style="#606060"> 3:</span> 3 <span style="#0000ff">where</span> job = <span style="#006080">'CLERK'</span>
<span style="#606060"> 4:</span> 4 <span style="#0000ff">and</span> rownum = 1
<span style="#606060"> 5:</span> 5 /
<span style="#606060"> 6:</span>
<span style="#606060"> 7:</span> 1 <span style="#0000ff">row</span> updated.
<span style="#606060"> 8:</span>
<span style="#606060"> 9:</span> <span style="#0000ff">SQL</span>> <span style="#0000ff">alter</span> <span style="#0000ff">table</span> emp
<span style="#606060"> 10:</span> 2 <span style="#0000ff">add</span> <span style="#0000ff">constraint</span> one_clerk_uk <span style="#0000ff">unique</span> (one_clerk_per_dept)
<span style="#606060"> 11:</span> 3 /
<span style="#606060"> 12:</span>
<span style="#606060"> 13:</span> <span style="#0000ff">Table</span> altered.
<span style="#606060"> 14:</span>
<span style="#606060"> 15:</span> <span style="#0000ff">SQL</span>> <span style="#0000ff">update</span> emp
<span style="#606060"> 16:</span> 2 <span style="#0000ff">set</span> job = <span style="#006080">'CLERK'</span>
<span style="#606060"> 17:</span> 3 <span style="#0000ff">where</span> job = <span style="#006080">'NOCLERK'</span>
<span style="#606060"> 18:</span> 4 /
<span style="#606060"> 19:</span> <span style="#0000ff">update</span> emp
<span style="#606060"> 20:</span> *
<span style="#606060"> 21:</span> ERROR <span style="#0000ff">at</span> line 1:
<span style="#606060"> 22:</span> ORA-00001: <span style="#0000ff">unique</span> <span style="#0000ff">constraint</span> (ALEX.ONE_CLERK_UK) violated
And now the Business Rule is implemented in a more “natural” way, without resorting to a “hack”.
The Same Difference…
Both methods implement the same Business Rule. Both implement the Business Rule in a declarative way. To some people the first method feels like a “hack” and find the second method more “natural”. But how different are they really?
Not as much as you might have suspected. When you use the first method (hacking with Function Based Indexes), the USER_IND_COLUMNS datadictionary view reveals the implementation.
<span style="#606060"> 1:</span> <span style="#0000ff">SQL</span>> <span style="#0000ff">select</span> column_name
<span style="#606060"> 2:</span> 2 <span style="#0000ff">from</span> user_ind_columns
<span style="#606060"> 3:</span> 3 <span style="#0000ff">where</span> index_name = <span style="#006080">'ONE_CLERK_PER_DEPT'</span>
<span style="#606060"> 4:</span> 4 /
<span style="#606060"> 5:</span>
<span style="#606060"> 6:</span> COLUMN_NAME
<span style="#606060"> 7:</span> -------------------------------------------------------<span style="#008000">--</span>
<span style="#606060"> 8:</span> <span style="#008000">SYS_NC00010$</span>
<span style="#606060"> 9:</span>
<span style="#606060"> 10:</span> <span style="#0000ff">SQL</span>> <span style="#0000ff">select</span> job
<span style="#606060"> 11:</span> 2 , SYS_NC00010$
<span style="#606060"> 12:</span> 3 <span style="#0000ff">from</span> emp
<span style="#606060"> 13:</span> 4 /
<span style="#606060"> 14:</span>
<span style="#606060"> 15:</span> JOB SYS_NC00010$
<span style="#606060"> 16:</span> -------<span style="#008000">-- ------------</span>
<span style="#606060"> 17:</span> NOCLERK
<span style="#606060"> 18:</span> SALESMAN
<span style="#606060"> 19:</span> SALESMAN
<span style="#606060"> 20:</span> MANAGER
<span style="#606060"> 21:</span> SALESMAN
<span style="#606060"> 22:</span> MANAGER
<span style="#606060"> 23:</span> MANAGER
<span style="#606060"> 24:</span> ANALYST
<span style="#606060"> 25:</span> PRESIDENT
<span style="#606060"> 26:</span> SALESMAN
<span style="#606060"> 27:</span> CLERK 20
<span style="#606060"> 28:</span> CLERK 30
<span style="#606060"> 29:</span> ANALYST
<span style="#606060"> 30:</span> CLERK 10
A hidden column is added to the table with a very obscure name. Apparently a Function Based Index is pretty similar to a Virtual Column.
-
Comments are closed
-
Thanks for your comments, John.
You would probably call this technique a “hack”…
I’m curious as to why (the reason) you call it “the wrong way to enforce” this Business Rule. It’s a sure fire way to implement this rule, there is no getting around it.
The solutions you propose do not implement the Business Rule. In both solutions you expect the end user to know the rule. There is no way you can stop them for selecting a MANAGER or SALESMAN to be used as your DEPARTMENT_CLERK (first solution) or in the intersection (second solution). How do you make sure that your end user only picks those Employees who JOB is CLERK? What if a JOB changes, someone gets promoted from CLERK to OFFICE MANAGER?
If you assume that your end users only select CLERKs, than you might as well not implement this Business Rule.
It can be done using your solutions, but you are missing a vital ingredient, a VIRTUAL COLUMN. Create a Virutal Column which shows the EMPNO only when the JOB = CLERK and use this virtual column as the foreign key (in your first and second solution)… however…. in order to use it as a Foreign Key, the column needs to be Unique… And when you create this column unique, you might as well do it the way I showed you
An example of the latter can be found here: http://nuijten.blogspot.com/2008/12/business-rule-only-use-active-records.html -
As far as I’m concerned, this is the wrong way to enforce one clerk per department. There are two possible right ways:
1. Change DEPT to add a foreign key to EMP named DEPARTMENT_CLERK. This is the EMPNO of this department’s one and only CLERK. Make it NOT NULL to require each department to have a clerk.
2. Add a table called CLERKS as an intersection between DEPT and EMP with foreign keys to each or as a duplicate of EMP. Put all the clerks in this table. Make CLERKS.DEPTNO unique. - Comment Feed for this Post
John,
I get the feeling, also from a discussion earlier this month at the UKOUG, that there is a ‘tribe of database designers’ that believe that if a business rule cannot be expressed/modellted through PK’s, UK’s, and FK’s, then a) the “rule” is not a business rule, and/or b) your design is flawed and should be amended such that it becomes expresseable through UK/PK/FK’s.
Reading your response I got curious: are you one of those tribe-members too?
Toon