Synchronizing databases

[MedicRanger]

Hello, I'm new with mobile databases so I have some concerns that you all can help me with.

 

I am designing a mobile patient reporting program that will be deployed on some 10 different laptops. Each night, the databases on the laptops will synchronize with a master database on a server. Supposed to be a simple process right?

 

One of my concerns is this. When uploading records from the laptops to the server, what happens if there are records that have the same ID? For example, what if users on three different laptops create records and the laptops assign the same ID number for the records? What will happen to child tables that are linked by that ID?

 

What kind of code will I need to accuratley upload records from laptops to the database while keeping unique ID's to the records and child tables?

 

Are there any straight-up resources that I can read to help me out with this problem? Any examples?

 

Thanks.

[kejpa]

Hi!

I was in the beginning of fiddling with this using Access when my project died :(

 

Access has some built in features for handling syncronized databases that is supposed to work no matter if you have two clients who have done inserts or updates to records. And it's possible to call those functions from an external program.

I think it's called replication, you have one master database and a number of replicas. Changes made in the replicas will be added to the master and changes done in the master after the replica is made are done in the replica when you syncronize. I have some vague memory of options in the syncronization but you better check the documentation.

 

As I said, I have just scratched a little on the surface of this.

Good luck!

/Kejpa

[Joe Mamma]

I like using guids (replication ID's in access). My ID's are rarely the primary key, but unique IDs I use as surrogate keys to enforce RI. But this is when there is another tuple in the record that can determine a uniqueness.

 

At work I have the ADO.NET cook book which outlines a manner of using negative numbers on the client side for identities that get changed to positive on posting to the database.

 

I recommend O'Reilly's ADO.NET Cookbook.

 

If I had the book here I would post a link to the code.

I did post the SQL server code to this site, but the access version is a little different.

 

I will try to remember in the morning.

[Joe Mamma]

Check out the code for chapter four:

http://examples.oreilly.com/adonetckbk/

 

here are the topics:

 

4. Adding and Modifying Data

4.1 Using Auto-Incrementing Columns Without Causing Conflicts

4.2 Getting an Identity Column Value from SQL Server

4.3 Getting an AutoNumber Value from Microsoft Access

4.4 Getting a Sequence Value from Oracle

4.5 Adding Parent/Child Rows with Auto-Incrementing Keys

4.6 Adding Records with a GUID Primary Key

4.7 Updating a Data Source with Data from a Different Data Source

4.8 Updating a Primary Key Value

4.9 Getting Stored Procedure Parameter Information at Runtime

4.10 Updating a DataSet with a Many-to-Many Relationship

4.11 Updating Server Data Using a Web Service

4.12 Updating Server Data Using .NET Remoting

4.13 Updating Data Asynchronously Using Message Queuing

4.14 Overcoming Keyword Conflicts When Using CommandBuilders