Interesting Data Integrity Issue

[benvegiard]

I have an employee history table that has EmpID (Int) and hire and terminate date columns (both Date/Time) among other columns. I would like the database to ensure that only one record per employee has a NULL terminate date so that we can be assured no employee has more than one “active” history record (I know, it’s an oxymoron J)

Can a check constraint do this? Or do I have to go with a trigger? Any other techniques (other than using UI Code) that anyone can think of?

Thanks!
Ben

 

[TheBugSlayer]

What is the content of the employee history table? If your taable structure is

emp_history(emp_id, hire_date, terminate_date)

then you cannot have more than a record in the table for each employee unless there is the possibility that the company rehires employees than had previously been fired. Setting emp_id as the primary key will ensure that there is not more than one record for that employee.

I believe the employee hire date and terminate date should be in the EMPLOYEE table and then any other information relating to the employee's achievements or movement's in the organizational chart should go to the history table.

The problem here is that it is know that an employee will most likely only terminated once within the organization, why make a provision for a field that will be empty in 99% of the occurences of the employee? If the employee has 245 occurences in the history table, for example, and he is terminated, in 244 of them the terminate_date would be NULL and the hire_date redundant. Flagrant vilation of normalization rules.

 

[benvegiard]

My fault. A bit more history is in order. For this project, hire/terminate is a term used both for the actual hire and termination of employement, and as markers for the start/end of a salary, location posting, etc. The Hire/Term dates are not broken out as people are oftern re-hired (or in this case, re-posted) to a position within this agency from another agency, so there is often "holes" in the history.

So, an employee will likely have 5-10 history records. Each record has a unique hire/term date (and the time periods should not cross, which is something else I'd like to check at the database level) So, in this case, all term dates should have a value (indicating they left permanantly or have had some change in their status (again, salary, location, etc)) except for the most recent record, which would have a blank term date. The exact nature of the termination is noted in a seperate field.

I hope that clears up confusion. Stored Procs the best way to go?

Thanks,
Ben

 

[royjimenez]

I think that
you will need a table for keeping information about the Employed. Also, u will need other table for keeping your history ( EmpId, dateStart, dateFinish, ... )
Many tables must be linked to History_Employed, like Salary Components and deductions. In this way, u can get a Employed with any "Histories". U can mark one like "Actual" and the others ones will be ignored when u need the current state for each employed.

Regards.

 

[TheBugSlayer]

Well, in any case in doesn't look like keeping all that data in one table is a good solution. Royjimenez is thinking along that line too.

To solve your problem if you decided to keep the status quo, use a FOR INSERT trigger in which you will use a query to check if there is already a record in the history table where the hire/term date is NULL, in which case you will revert the operation.

Exple:
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'chk_date' AND type = 'TR')
DROP TRIGGER employee_insupd
GO
CREATE TRIGGER chk_date
ON empl_history
FOR INSERT
AS
IF EXISTS( SELECT * FROM empl_history WHERE job_id = 13)
-- DO SOMETHING
ELSE
-- DO SOMETHING ELSE
GO

 

[TheBugSlayer]

Oops, it should be:

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'chk_date' AND type = 'TR')
   DROP TRIGGER employee_insupd
GO
CREATE TRIGGER chk_date
ON empl_history
FOR INSERT
AS 
IF EXISTS( SELECT * FROM empl_history JOIN INSERTED INS ON INS.emp_id = emp_id AND terminate_date IS NULL) 
    -- DO SOMETHING
ELSE
    -- DO SOMETHING ELSE
GO