Re: The fable of DEMETRIUS, CONSTRAINTICUS, and AUTOMATICUS

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Tue, 19 Oct 2004 18:46:08 -0400
Message-ID: <hf54lc.c2a.ln@mercury.downsfam.net>

Tony Andrews wrote:
>

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

Well, now that I've had more time to think about it, here is the next reply.

This is a history table. Users must never maintain a history table directly, the information should be gleaned from "primary sources" as it were. What does this mean? It means throw away the table, if you want to know the person's history of department placements then look in the payroll history table. If it's not there, put it in.

Thanks for an entertaining and edifying thread, cheers!

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

Received on Tue Oct 19 2004 - 17:46:08 CDT