0

Using Java and Oracle.

We need to update changes in Email, UserID of employee to third party. Actual table is Employee and intermediate table we keep which we will use for comparison of changes before sending to third party.

Following are database designs coming in mind for intermediate table:

Only Single table:

EmployeeiD|Value|Type|UpdateDate 

Value is userid or email, type will be 'email' or 'userid'. Update date is kept so to figure out that which of email or userid was different and update to third party.

Multiple Table:

 Employee_EmailID
      EmpId|EmailID|Updatedate

 Employee_UserID
      EmpId|UserID|Updatedate

Java flow will be: - Pick employee from actual table. - Pick employee from above intermediate table. - Compare differences. Update difference to third party. - Update above table with updated value and last update date.

Which one is consider as best way, single table approach or multiple table or is there any standard way to implement the same? There are 10,000 Employees in system. Also consider approach when 10 intermediate tables are required like phonenumber, salary etc.

0

For the employee table you should have two audit tables.

1 employee_audit (employee_audit_id , ref_employee_id, reason,update_date, update_by)

and

2 employee_audit_detail (id,ref_employee_audit_id , field_name, old_value)

So you have every value saved only once and you have a complete record,what was changed and by whom grouped the date it was change.

Programming is every time you save data for an employee , you save first a new row in employee_audit get the last id and then enter a new row for every changed value the name of the value and old content

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.