Active Employees / Terminated Employees

Hey guys, I am really stumped on this issue. I am having a hard time with removing employees from an employee database and keeping that data organized.

Essentially, Human Resources will click a button that says Terminate Employee. This will take them to a form and they fill out Termination details, submit form. Employee added to the termination database no longer exist in the active employee database or table.

When the employee is added to the Terminated database, however, it's still not separated from the Active Employee database. They just appear in both databases.

Is there a way for me to make it so that employee will only exist inside the Terminated Database once they're terminated or would I just be better off going with an Employee Status type situation where you just sort / filter employees based off Status changes.

I really want to have these tables organized, though. Where Active Employees are not mixed in with Terminated Employees at all, but really needing some help thinking this through.
Jared DavisIT SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'm no DBA, so I am going on what I would do (meaning a non-technical answer).

I would keep one table, populate with terminated information and filter but am curious as to technical / best practice reasons to do it one way or another.
Chris StanyonWebDevCommented:
Generally speaking, you shouldn't delete records from your Employees table. For example, if you need to run a query to produce a particular report, (i.e. how much was the wage bill last year / 2 years ago, which employee was responsible for a particular action etc.), then those terminated employees should still be part of that query. Once they've been deleted from the Employees table, then your historic data is no longer accurate.

You should model your DB with a soft-delete option (marking a record as 'deleted' rather than actually deleting it). This can be achieved several ways depending on your needs. A Status column (option values), Active column (true / false), DeletedAt column (DateTime), TerminationRecordId (a foreign key to a related table storing additional Termination Info). You can then use these columns in your queries against the Employee table to either include or exclude them depending on your requirements.

Having said all that, it will be a design decision based based on your own requirements. If you have absolutely no need for the historic data, then go ahead and hard-delete the records (along with any foreign key constraints), and maybe store them in an 'archive' table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jared DavisIT SpecialistAuthor Commented:
Thank you guys!
Jared DavisIT SpecialistAuthor Commented:
Helped me to decide the best option is using Status instead of actually deleting the records.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.