October 14, 2009 at 2:35 am
Hi Everyone,
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.
Regards,
S
March 17, 2010 at 9:36 pm
I'm afraid you're wrong with you requirements.
🙂
These 2 statements contradict each other:
On the other side an Oracle DB, also with staff, but only active.
when a staff member leaves and comes back, the key will always stay the same on the Oracle-side.
When a staff member leaves then it must be deleted from Oracle database because it holds only active ones.
When the same staff member comes back the old key should be gone, and therefore there is no way to re-assign it to this staff member.
Something is wrong in the task description.
_____________
Code for TallyGenerator
March 17, 2010 at 9:46 pm
Sergiy (3/17/2010)
I'm afraid you're wrong with you requirements.🙂
These 2 statements contradict each other:
On the other side an Oracle DB, also with staff, but only active.
when a staff member leaves and comes back, the key will always stay the same on the Oracle-side.
When a staff member leaves then it must be deleted from Oracle database because it holds only active ones.
When the same staff member comes back the old key should be gone, and therefore there is no way to re-assign it to this staff member.
Something is wrong in the task description.
I agree with Sergiy here. These requirements don't quite make total sense.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply