In 1 or in 2 tables? 3

[Katya85S]

What is more efficient?
There are Active Employees and Terminated employees records in the database.
Should Active and Terminated Employees records be kept in one table or separated in two tables?

 

[forecasting]

What is efficient? If all you need to do is know the status of the employee, a simple boolean field (EmployeeStatus) solves the problem. Then you can add the field in a WHERE clause and get only the records you want (including any linked tables). And if you are searching for a person by name, you can access the records from one query.

If you use two tables, then you need to have aprocess which copies employees from one table to the next, and possibly all of the linked records. That seems like more work

 

[SQLSister]

I too would probably put them in one table with a status flag, otherwise you could have data integrity problems if you have related tables (which you almost certainly do have). For instance, suppose you have a relted table of training which also relates to a training course table. When you ruin a report of who attended that training, you would want to be able to pull up the names of the people who attended whether they were still employees. If you have two separate tables you then have to rewrite all your queries to also join to the terminated table. ALso terminated employees sometimes come back and changing a flag could be the best way to reinstate them.

"NOTHING is more important in a database than integrity." ESquared

 

[Katya85S]

Thank you Forecasting and Sister!

 

[Katya85S]

I appreciate both answers equally, though somehow SQLSister happen to get 2 stars (which i don't mind at all). I wanted to give the second start to Forecasting then too, but somewhat teh system didn't do that...

 

[ESquared]

Someone else gave SQLSister a star. You can't give the same person two stars in the same thread.

 

[Katya85S]

Now i see tahnks for teh explanation