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:


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:



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.

1 Answer 1


For the employee table you should have two audit tables.

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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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