[ale] OT: Design Question

[J.M. Taylor]

Mike,

If you're not going to be updating your inactive employee information, I 
would go with the 2nd option.  It saves your db a good bit of work...you 
have one transaction (putting the inactive employee's records into a 
table) versus potentially many transactions (add new dept, update employee 
table set deptid=new id where deptid=oldid and inactive is null). 

You have the added benefit of not storing useless data so your lookups 
will be that much faster, assuming it's rare that you pull data on 
inactives.

jenn

On Fri, 11 Jun 2004, Mike Millson wrote:

> Suppose we have an application that produces a report that shows the
> employees that work in each department. An admin is in charge of
> maintaining employee and department data.
> 
> The employee's dept is looked up in a Department table, as shown below:
> ______________
> |  Employee  |
> |============|
> |EmployeeID  |
> |Name        |
> |DepartmentID|
> |IsActive    |
> --------------
> 
> ______________
> | Department |
> |============|
> |DepartmentID|
> |Name        |
> |IsActive    |
> --------------
> 
> Suppose that employees can be made inactive, at which point they can
> never be made active again. Out the door, a one way street. And once
> they're inactive, the department they're assigned to is "locked down".
> 
> Suppose the admin does the following:
> 1) Adds Bob Smith and Joe Dog to the application and assigns them to the
> "Sweat Shop" department.
> 2) Makes Bob Smith inactive.
> 3) Renames the "Sweat Shop" department to "IT Shop".
> 
> Given this scenario, we would want the database to show that Bob Smith
> belongs to "Sweat Shop" and Joe Dog belongs to "IT Shop".
> 
> In other words, we want department name changes to flow through to
> active records but not to inactive records.
> 
> I can think of a couple of solutions:
> 1) Every time a department name is updated, if it has inactive employees
> assigned to it, create a new department record with the new name and
> assign it to all active employees that were assigned to the old
> department name.
> 2) When an employee is made inactive, copy all of its data to a special
> InactiveEmployee table where all the data is hard coded into character
> fields, no foreign keys.
> 
> Are either of these solutions any better than the other? What
> strategies/solutions/patterns have others seen and recommend for this
> type of requirement?
> 
> Thank you,
> Mike
> 
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://www.ale.org/mailman/listinfo/ale
> 
> 

-- 
Jenn Taylor
jtaylor at onlinea.com

------------------------------------------------------------------------
Obviously, a man's judgement cannot be better than the information on which he
has based it.  Give him the truth and he may still go wrong when he has
the chance to be right, but give him no news or present him only with distorted
and incomplete data, with ignorant, sloppy or biased reporting, with propaganda
and deliberate falsehoods, and you destroy his whole reasoning processes, and
make him something less than a man.
-- Arthur Hays Sulzberger