Re: The fable of DEMETRIUS, CONSTRAINTICUS, and AUTOMATICUS

From: Tony Andrews <andrewst_at_onetel.com>
Date: 20 Oct 2004 03:13:46 -0700
Message-ID: <1098267226.789096.56310_at_z14g2000cwz.googlegroups.com>


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

Yes, I can't deny that. It is an interesting solution, if somewhat unweildy. I bet even you have never actually done this!

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

Then it is no longer a constraint, it is merely a more laborious way of entering "any number you like" into the system.

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

Yes, if compared to a hard-coded constraint like "CHECK salary <= 100000". No, if compared to a more realistic constraint like "CHECK salary <= (select salary_limit from ...)". Of course I have no wish to see company-specified limits hard-coded into rules. Then, your approach has the obvious disadvantage that if the limit is raised to 150,000 then you need to get 50,000 new rows (or 5,000,000 new rows if pennies are allowed) into your reference table; mine requires an update to 1 row.

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

No, because whenever you hit a constraint too complex for a simple foreign key, your response is to deny that such a constraint could ever be required! Do you also have a "RISC" tool box, containing nothing but a hammer? ;-)

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

Another case of side-stepping the issue: assume there IS such a requirement; how would you DO it?

> >> CASE 3: EMPLOYEE ALLOWED IN ONLY ONE DEPARTMENT
<SNIP>
> 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.

But there ARE cases where such a table is what the business wants. For example, it may want to record a new employee's previous employement history, or a customer's addresses for the last 5 years. In some cases there may be gaps, so you can't just assume that the start of one record defines the termination of the previous.

If I can sum up: we started with a proposition that all constraints can be achieved "structurally", via unique and foreign keys. This has been relaxed to: structural constraints can be achieved structurally; anything more complex shouldn't be attempted anyway. ;-) Received on Wed Oct 20 2004 - 12:13:46 CEST

Original text of this message