[ale] OT: Design Question

[Mike Millson]

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