structure dilema

[gogaak]

hello all

Consider i have an employee database.
And i want to keep a track and analyze all the training programs every employee has attended from the day he joined ( for both working and resigned employees).
The analysis part is based on division-wise, level-wise, year-wise, location-wise, trainingtype -wise etc..

I have a main employee database with his ID, name, location, age. And i have created seperate tables for his designation, dept, training program, year ,etc...THe reason for this is that the departments, designation , training programs available within the company get updated often. And I have created relationships between them.

When an employee resigns or leaves the company, i have to store the records, but I dont need to update it.
One option is ; whenever a employee leaves the company delete his related record and transfer them to to another table called OUT_Emp.
Just the delete the employee record from the Employee table. And keep the other relationships intact.

Or should i create seperate database and transfer all the tables from the main database, just for the employees who have retired, resigned or suspended. THis database will contain all the tables that the main table has along with the relationship.


I really suck at one liners

 

[pbaldy]

It would be more common I suspect to have a "status" field in your table, and switch it from "A" for active to "T" for terminated. Or a yes/no field that you check when they leave. Then you don't move the record anywhere.

 

[gogaak]

It would be more common I suspect to have a "status" field in your table, and switch it from "A" for active to "T" for terminated. Or a yes/no field that you check when they leave. Then you don't move the record anywhere.

yes .I thought if that too.
But the company wants a seperate table for the ex-employees . I guess to avoid complication and also to lock the ex-employee table from further updation.

Any more suggestions?

 

[pbaldy]

Sure, but it sounds like we're letting people who don't know anything about database design, design the database. Of the 2 options you mentioned, I would keep it in the same database in a separate table. The simple fact is that you're going to have to access and even update that data sometimes. What if an employee leaves now then moves before the end of the year? You'll need to change his address to be able to send his W2 (in the US, some other year end form in other countries).

I'd still use one table, and restrict access to those employees with a terminated status. Having more than one table or database is more complicated, not less (at least to me).

 

[gogaak]

gr8

u totally echo my mindset....
I guess i was too pressurised to get into their demands. After all its the user interface they would care about....And in the future it would be much easier to upgrade the database , if i keep it simple.

Thanx for all the replies..