how to retain foreign key relationship while SQL Database sync

Question

5.00/5 (1 vote)

See more:

I have following scenario:
My client has 2 offices at different locations not dedicatedly connected to each other in terms of data connectivity. Lets call these offices as HO and Branch
The HO will be generally responsible in entering the master data related to the application configuration. And both the offices will be entering, both master and transaction data for their respective clients of their geography.
The data entered in the transaction table at the Branch office needs to be approved by a person sitting at the HO.
Also the Branch needs to access at least the Master data entered in the application with regards to the application configuration.
Now the problem is how do I sync both the databases in both the offices such that the foreign key relation ship is maintained in the tables.
For e.g. lets say that there are 2 tables in the database at both the location each of which will be getting data from both the offices.
Table 1  Client_master
Table 2  Client_transaction_Header
Table 3 Client_transaction_Details

Lets say the following are the columns:
Client_master
Client_ID int (Auto increment Primary key)
Client_Name Varchar(50)

Client_Transaction_Header
CL_TR_HD_ID int (auto increment Primary Key)
CL_TR_Client_ID int (Foreign key of table 1)
CL_TR__Hd_Date Datetime
CL_TR__HD_ship_Addr Varchar(50)

Client_transaction_Details
CL_TR_Det_ID int (Auto increment Primary Key)
CL_TR_HD_ID int (foreign key of Table 2)
CL_TR_Det_item Varchar(40)
CL_TR_DET_Price decimal

How do I sync data if both the offices will enter data in all the above tables?

I am using Windows Forms application with .net framework 3.5 code language c# and SQL Edition 2005 Std

Constraints:
Cannot use Web application
Cannot use single SQL Database server.
If possible would want to use SQL Express Editions.
Ready to migrate from 2005 to 2008 SQL Server.

Posted 19-Mar-11 9:38am

Kiranjjain

Updated 19-Mar-11 10:19am

Henry Minute

v2

Add a Solution

Comments

Henry Minute 19-Mar-11 16:21pm

How is the data sent from the branches to HO?

Kiranjjain 21-Mar-11 3:49am

to send Data / Sync Data between Branch and the HO, we intend to use VPN over internet. This connection will be just temporary connection for the purpose of syncing Data only.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

dasblinkenlight · Answer 1 · 2011-03-19T10:52:00

You cannot use autoincrement fields alone for your primary keys - you need something else to avoid collisions when merging the data.

There are two relatively simple ways of dealing with this issue, in the order of my personal preference:
- Switch to using uniqueidentifier fields, and set them to a new GUID in your C# code. This approach has an added security of avoiding unintended FK matches, which might happen when you use straight integers.
- If you must use an autoincrement field, add a second column (call it OFFICE_ID), and make it part of a two-column key. Set OFFICE_ID to 1 in HO, and to 2 in the Branch. This is a lot less convenient, because all your foreign keys become composite as well.

I used a third approach in the remote past (more than 10 years ago), and it was the least reliable: we assigned each of our several installations a unique number N, and started our autoincrement sequences for all tables in N*10^9. There was a limit of 10^9 records per site, and we constantly ran into silly errors when copying DBs for testing between production and development sites.