sync framework and syncing multiple client tables to one server table

Question

Is it possible to sync multiple client tables to one server table? The issue I see it with the primary key being duplicated in the multiple clients. Is there a work around?

I tried to sync without the primary key but seems sync framework needs a key for it to work.

Or is my best bet to create multiple tables on the server and then create a view to combine the data without the primary key column?

Answer 1

An easy way to avoid such duplicates is to use:

1. a natural key
2. a PK providing service (you'll need some sort of number provider)
3. not recommended: a randomly generated PK (instead of the usual +1) (a guid works sometimes but is very tricky)

A natural key differs from the auto generated number: it's often an intrinsic property of the "thing" you are trying to store. Think of something like a license plate on a car.

By using this property, you can ensure that when you have this key, you have the same record.

Keep in mind, there are always some exceptions, even with license plates.

---

As for now:

You might be able to drop the insert identity, and reassign some numbers. But that will break the PK server / client relation (your server will have completely different PK than the clients). Over time this can give you a big headache.

An other way is to use a composite key:

Use your PK, but add a column like ClientID or ClientLocation, create a composite PK from both (not sure if the auto numbering will accept this). This might be the easiest option.

For the latter, in this example you can see that the combined key is unique:

//Client 1)
|---composite PK---|
|  ID  | ClientID  |
|------|-----------|
      1      cust1
      2      cust1
      3      cust1
//etc
//Client 2)
|---composite PK---|
|  ID  | ClientID  |
|------|-----------|
      1      cust2
      2      cust2
      3      cust2
//etc

//Server)
|---composite PK---|
|  ID  | ClientID  |
|------|-----------|
      1      cust1
      1      cust2
      2      cust1
      2      cust2
      3      cust1
//etc