Re: The fable of DEMETRIUS, CONSTRAINTICUS, and AUTOMATICUS

From: Tony Andrews <andrewst_at_onetel.com>
Date: 19 Oct 2004 10:11:53 -0700
Message-ID: <1098205913.543542.175120_at_z14g2000cwz.googlegroups.com>


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.

> Advantage 1: With a constraint, policy changes require a programmer.
Now
> you are truly Constrainticus, preventing things from getting done.
If the
> salary values are in a table, policy changes can be implemented as
soon as
> they are authorized, the top dog goes in and types it in. Keeping to
the
> Greek philosophy idiom, this is much more virtuous, i.e, it conforms
to The
> Good.
>
> Advantage 2: The RISC/CISC argument. Putting in a constraint
requires a
> new primitive, the column constraint, to do something that could have
been
> done with the existing unique/referential primitives. This means
guidelines
> on when to use a column constraint, policies on creating, modifying,
and
> changing them, upgrade procedures to ensure implementation, creating
a
> unit test system, creating an integration testing system, etc,
etc.....

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

> 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.

> 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 Received on Tue Oct 19 2004 - 19:11:53 CEST

Original text of this message