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
'userid'. Update date is kept so to figure out that which of email or userid was different and update to third party.
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.