Need Architecture Solution

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Strategies » Need Architecture Solution

Need Architecture Solution

Rate Topic

Display Mode

Topic Options

Author

Message

Swartkruit

Posted Wednesday, October 14, 2009 2:35 AM

Grasshopper

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

Sergiy

Posted Wednesday, March 17, 2010 9:36 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 4,557, Visits: 8,213

I'm afraid you're wrong with you requirements.
Smile

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