Deleting One Table Upon Entry In Another

Deleting One Table Upon Entry In Another

  

Hey guys, I was wondering is there a way to pull employee information from the Active Employees table into the Terminated Employees table upon entering that employee into the database? Or is there some kind of work around to get something similar to this happen?

So i guess when that employee Id is saved to the Terminated Employee table is there a way to get that action to also delete that person from the Active Employee table upon that save action? 

Hi, 

Just want to know, Why do you want to keep data into separate table? Can't we do it putting a flag into employee table.

Sachin

What do you mean by putting a flag into the employee table? 


Well I just wanted it to be easier for someone to see Employees who were terminated versus employees who were actively employeed. Is there a better method than splitting Terminated Employees and Active Employees? 

I just thought if you're collecting terminated employees for five years the organization would be better over the long term instead of having Active and termed employees in the same list table.

Hi, 

I think its good to have the data in one table with two columns as Active (Y/N) and Termination Date (containing the date of the termination of the employee).

Hi,


I am not sure what you are trying to achieve, but in any case,

you should already have wrappers around the cud-actions of the entities,

next up, what you are talking about is in essence, businesslogic, so one should expect an action in your business-layer that makes an employee-inactive.

In that action you will delete and copy that specific employee to the right table?


I am just trying to keep employees who are no longer working for the company in a separate entity than the active employee entitiy. So you dont have employees who are no longer working for the company mixed in with employees who are still working for the company.

Jared,

If you want 2 tables, you will have to, as J said, crate your own wrappers around CRUD actions and make sure when you remove from Active Employees table and create into Terminated Employees table. AFAIK there is no automatic process to do this. 

But, for me, the best option is the one Sachin said. You create an attribute in your Employees table of type Boolean (ex. IsActiveInCompany) and on your terminate process you just update the record with this attribute a False. To keep this records to show in Active Employees, just add a filter with this attribute = True.

Best regards,

Abílio Matos

Hmmm


So I would have 



Employee Entitiy  (to store all employees that have ever worked for us)

ActiveEmployee Entity

TerminatedEmployee Entity 




and they would operate off a IsActive Boolean and populate either or depending on if the Terminated Boolean is checked or not?

I think you are mixing things.

You have 2 options:

1) 1 Entity called Employee with one attribute IsActive Boolean - If true is an Active Employee, if false is a Terminated Employee;

2) 2 Entities - ActiveEmployee and TerminatedEmployee - in this case you should do your own login when setting an employee from active to terminated (create in TerminatedEmployee Entity and then delete from ActiveEmployee on one transaction)


Personally, i would go for option 1, but you might have some business rules to go to option 2.


Abílio Matos 

Solution

It's much easier to do this with one Employee entity and one EmployeeStatus static entity.  

1) Create the EmployeeStatus static entity with two attributes - the ID and the Status. 

2) Create records in the EmployeeStatus static entity - Active, Terminated, Retired, Medical Leave, etc.

3) In the Employee entity, add the attribute EmployeeStatusId - it should automatically link to the EmployeeStatus static entity.

4) Optional - add an EmployeeStatusDate and set it when the status changes so you know when it happened.

5) On the employee CRUD screens, add a dropdown to select the EmployeeStatus entity - this allows users to select the correct status type.  Optionally add the EmployeeStatusDate to this screen also.

6) Anytime you want just active employees, select records where EmployeeStatus is Active.  Note that you should NOT do this for employee maintenance screens since once you set it to something not Active, you'll never be able to set it to something else in the future, like someone on medical leave or retired that returns.

Hope this helps,

Curt


Solution

Curt Raddatz wrote:

It's much easier to do this with one Employee entity and one EmployeeStatus static entity.  

1) Create the EmployeeStatus static entity with two attributes - the ID and the Status. 

2) Create records in the EmployeeStatus static entity - Active, Terminated, Retired, Medical Leave, etc.

3) In the Employee entity, add the attribute EmployeeStatusId - it should automatically link to the EmployeeStatus static entity.

4) Optional - add an EmployeeStatusDate and set it when the status changes so you know when it happened.

5) On the employee CRUD screens, add a dropdown to select the EmployeeStatus entity - this allows users to select the correct status type.  Optionally add the EmployeeStatusDate to this screen also.

6) Anytime you want just active employees, select records where EmployeeStatus is Active.  Note that you should NOT do this for employee maintenance screens since once you set it to something not Active, you'll never be able to set it to something else in the future, like someone on medical leave or retired that returns.

Hope this helps,

Curt


When you say with two attributes, you mean the EmployeeStatus Id and an entity named Status, right? Delete Is_Active, Label, and order entities from the Static EmployeeStatus table? I apologize for asking so many questions about this. 


Jared Davis wrote:

Curt Raddatz wrote:

(...)

When you say with two attributes, you mean the EmployeeStatus Id and an entity named Status, right? Delete Is_Active, Label, and order entities from the Static EmployeeStatus table?

Hi Jared,

Here's what Curt meant:

Okay, understood. Thank you so much for your help. I have a more advanced problem now. Thank all of you for trying to help me! I'll make a new thread since it's a new issues within this solved issue and if someone has a similar issue in the future, they can find it more easily instead of it hiding in this thread.