DB design for synced desktop application

Question

I'm building a desktop application that will run on multiple laptops. It will need to sync up to a central database whenever the user is back in the office and has access again.

My biggest problem to overcome is how to design the database so that it is easily synced with the central database server. One of the major hurdles is trying to determine how to handle keys such that they don't duplicate across the multiple laptop databases that'll be in use.

For example, say Laptop 1 enters a new customer called "Customer A" - using a unique id, it might be assigned a customer ID of 20. Laptop 2 enters "Customer C" - it could also assign the ID of 20 to that customer. When it comes time to sync, both customer A & C would end up on the server with a duplicate ID.

Has anyone worked with an app similar to this that has an elegant solution?

Answer 1

What DBMS you will use?

Answer 2

Alternatives are:

• ranges of ids, which are difficult to implement and to enforce. They are very hard to manage, is difficult to provision an new site/range and is even more difficult to cope with retired sites/ranges.
• composite keys, (siteId, EntityId) are better than ranges but require upfront design and may cause problems on queries that aggregate multiple/all sites (central DWs) because the leftmost key in all indexes (siteId) is not specified.
• GUIDs. In theory they are perfect as they guarantee uniqueness (they may conflict in theory, I'm yet to see a true guid conflict). In practice they are very poor clustered key choices because of the size (16 bytes) and because of the fragmentation. Still, they are much easier to get right than the composite key approach.

Answer 3

I don't know that the is an "elegant" solution to this very inelegant problem. Remus has good thoughts, also suggest you do some reading on replication.

You had also better design a de-dup process because sure as anything, rep A is going to upt in customer A and rep b is going to put in customer A as well and because they came from differnt sources with differnt primary keys, they will be different records.

Answer 4

Use Guids (aka uniqueidentifier) for your primary keys, and all of your problems with replication will go away. The penalties for using Guids are almost always drastically overstated by proponents of auto-increment int PKs. The extra size they require (16 bytes vs. 4 bytes for an int) is so trivial that I'm amazed it ever comes up in discussions on this subject. Queries that JOIN on Guid PKs will run slower than queries that JOIN on int PKs, but it is not an order-of-magnitude (i.e. 10X) slower. Your results may vary, but I've found that Guid JOIN queries take around 40% longer to execute (which may seem like a large penalty until you remember Moore's law).

Using Guids for PKs also gets you out of doing really hacky stuff when you're inserting related data. Instead of having to insert the child records and then retrieve the IDs for the just-inserted rows before inserting the parent rows, you just create all the IDs client-side with Guid.NewGuid().

I've worked with replicated systems before that used assigned ranges with int PKs to solve this problem, but I will never ever do that again.