Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need Architecture Solution Expand / Collapse
Author
Message
Posted Wednesday, October 14, 2009 2:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 6:34 AM
Points: 12, Visits: 64
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
Post #802566
Posted Wednesday, March 17, 2010 9:36 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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.
Post #885196
Posted Wednesday, March 17, 2010 9:46 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:49 PM
Points: 20,462, Visits: 14,092
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #885202
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse