Here is the situation:
On the one side we have a SQL DB with staff (active & terminated). On the other side an Oracle DB, also with staff, but only active. Then the primary key for the two systems differ. On the SQL-side the key is CHAR (6) and on the Oracle-side the key is BIGINT or Numeric (Oracle).
The two systems need to update each other. The dynamics of this combination is that when a staff member leaves and comes back, the key will always stay the same on the Oracle-side. On the SQL-side if the staff member leaves and comes back, the old record's status will become terminated when they leave and a new record is created when they come back.
Then, a terminated record can also be updated a month or two in arrears from the Oracle-side and vice versa. My suggestion for the solution is a composite cluster with the 2 keys and a status field making it an ongoing unique combination i.e.
SQL Key----- Oracle Key----- Status
PK-A----- PK-1----- A
PK-B----- PK-2----- T
PK-B1----- PK-2----- A
PK-C----- PK-3----- A
PK-D----- PK-4----- A
PK-E----- PK-5----- A
PK-F----- PK-6----- A
PK-G----- PK-7----- A
If I'm completely wrong with my solution, please advise. If you guys have implemented or know of better real world solutions, please let me know as well.