Re: The fable of DEMETRIUS, CONSTRAINTICUS, and AUTOMATICUS

Tony Andrews wrote:

>>
>> The thesis I am developing is that there are not that many places

>> should use a passive constraint, it may be possible to limit advanced
>> complex systems solely to Unique and RI.  The real service and power

>> system will be in the automatically generated information.  If I am

>> I hope I've the gumption to report here why it did not work.  If I'm

>> I'll be releasing something under GPL someday.  Wish me luck :)

Laconic2's reply with Marshall's reply to that actually gives the insight better than I have: you want to provide the constraints structurally.

I'll do the details here.

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.

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

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?

After we talk to the users more, we may do something like this (or maybe something different of course when we get the information):

1. Create an auto-calculated column in EMPLOYEES Table, EARLIEST_PAY_DATE = Birthday + 18 years.
2. Each payroll batch has a START_DATE and END_DATE in the PAYROLL_BATCHES table.
3. Put the predicate not in the EMPLOYEES table, but in a view of the EMPLOYEES table joined to PAYROLL_BATCHES table, where the JOIN specifies EMPLOYEES.EARLIEST_PAY_DATE >= PAYROLL_BATCHES.START_DATE
4. Restrict paychecks within a batch to those employees who are in the view for that batch. QED.

This is not mere sleight of hand. We expect a view to be filtered, so once again I would claim that resorting to a *new* *primitive*, the explicit column constraint, must be justified.

Advantage 1: More data is good. Generally speaking, well-formed data can be more easily mined for useful information. The more explicit, the better.

Advantage 2: The RISC/CISC argument, see above.

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?

If the user changes their mind, you create a cross reference that can put employees into multiple departments.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me