Re: The fable of DEMETRIUS, CONSTRAINTICUS, and AUTOMATICUS

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Tue, 19 Oct 2004 15:29:09 -0400
Message-ID: <aup3lc.rg8.ln_at_mercury.downsfam.net>


Tony Andrews wrote:

> Kenneth Downs wrote:

>> CASE 1: CONSTRAINT SALARY <= 100,000 (from your other post).
>>
>> All discreet finite range constraints can be converted to RI by

> having a
>> table of allowed values.  Salary codes with dollar values (or direct

> salary
>> values in a table).
>>
>> Some will knee-jerk react against the idea of a table with 100,000

> rows, but
>> of course that is silly, we only enter the ones we are going to use.

>
> Call me silly then ;) Do you really think users will enjoy this? :-
> Boss: "Raise Joe's salary to 12,472"
> User: "I can't - 12,472 isn't in the valid salary table"
> Boss: "Call Jim: he has the necessary privilege to set up new valid
> salary values"
>
> The real rule here isn't a list of values, it is a limit. It would be
> good if we could put the limit in a table somewhere rather than
> hard-code it in the constraint.

OK, I'll call you silly ;)

Point 1: The limit maps to a range, allowing it to be expressed using RI.

Point 2: The example of Boss/User/Jim is not valid, either:

  -> The boss would be talking to Jim in the first place, who would

     create the entry and then give it Joe.  Or, if Joe is the only
     person with a certain entry, just change that entry, exactly the
     same amount of work as changing it in the EMPLOYEES table.
  -> They would send it to personnel anyway, the payroll system is
     one the most restricted (if not THE most restricted) areas of
     any system.

Point 3: The advantage of being able to change the "limit" without hiring a programmer. This advantage remains.

Point 4: The advantage of not having to have a programming infrastructure and policy around use of column constraints. This advantage remains.

>
> Well, I think your minimal set of 2 constraint types is just TOO
> minimal!

Yes, but you have given me no compelling reason to complicate my life with a column constraint when structural constraints so far work just as well and give the users a better experience.

>

>> CASE 2: NO HIRE BEFORE AGE 18
>>
>> Big problem, this one is fuzzy.  I would not implement anything here

> without
>> more information.  Why are we entering people who have not been hired

> yet?
>> Or are we?  Is it that they cannot be entered, or cannot be paid?

>
> Or perhaps it was a typo - user put 01-APR-1998 but meant 01-APR-1989.

Typos invalidate all validation systems, yours, mine, and Programmer X's. They are usually handled after-the-fact when anomalies show up.

>

>> CASE 3: EMPLOYEE ALLOWED IN ONLY ONE DEPARTMENT
>>
>> Um, unless I'm mistaken the departments are in a table and the

> employees
>> table contains a column called DEPARTMENT_ID.  Did I miss something?

>
> OK, I wasn't clear enough here. What I meant was assume we have these
> tables:
>
> create table emp (empno int primary key, ... );
> create table dept (deptno int primary key, ... );
> create table emp_dept_assign (empno references emp,
> from_date date,
> to_date date
> deptno references dept,
> primary key (empno, deptno));
>
> My intended rule is that no 2 records for the same employee should have
> overlapping dates - e.g.
>
> insert into emp_dept_assign values (1234, date '2004-01-01', date
> '2004-07-31', 11);
> -- OK
> insert into emp_dept_assign values (1234, date '2004-06-01', date
> '2004-08-31', 11);
> -- Error: overlaps an existing record

This is a common conceptual error. What you are calling a cross-reference is actually a history table, it accumulates rows. It should not be handled by the system or by the users in the same way as reference tables. Attempting to do so is what leads to complex constraints.

I will admit I have two approaches to this, neither of which closes the problem. The problem is that we have introduced time into the equation, and that tends to produce cross-row conditions that are not so easily dispensed with.

Approach 1 is to trap changes to EMPLOYEES.DEPARTMENT_ID and write changes to the history table as appropriate. This preserves the simple RI, but there is a coupling there between the foreign key and some kind of EFFECTIVE_CHANGE_DATE column, which can bring us back to complex constraints.

Approach 2 is to allow the user to perform only inserts into what amounts to a "change request" table. The user enters that EMPLOYEE 123 will transfer to department XYZ on effective date 10/1/2004. A trigger then closes the current department-history entry and inserts a new open one beginning on 10/1/2004. There are a host of unstated constraints in this approach, but they may be palatable if the system can generate them automatically and nobody has to code them.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me
Received on Tue Oct 19 2004 - 21:29:09 CEST

Original text of this message