Sync for ADO.NET - Server

So we have the basic idea of the application, and due to the limited number of tables our Domain Design matches Data Design quite nicely. This means that for simplicity we can use LINQ to SQL, but I'll discuss that more in a post about the client.

For the server all we really need to do is create our database for now. It's worth noting that to support sync we'll need to add a few extra columns to each table. It's also important to decide how a delete will be handled, either as a logical delete (row is 'flagged' somehow to mark it as deleted) or a physical delete (row is removed from the database). If a physical delete is going to be used you'll need to add a "tombstone" table, somewhere to copy the row to when you delete it, otherwise there's no way for the clients to know the row has been removed (since they'll just ask for changes since last check).

Extra columns I suggest are:

• UpdateVersion timestamp NOT NULL
• CreateVersion binary(8) NOT NULL DEFAULT @@DBTS + 1
• DeleteVersion varbinary(8) NULL
• UpdateID uniqueidentifier NOT NULL
• CreateID uniqueidentifier NOT NULL
• DeleteID uniqueidentifier NULL

I've made an assumption here that only sync-aware applications will modify rows, this avoids the need for triggers and empty Guid values as defaults. Please note that we'll be filtering rows on all 6 columns so they should all have an index added.

Here's my database diagram:

You'll notice that I haven't included the DeleteVersion or DeleteID columns for the 3 related tables, since I don't want to allow deletes on them (later we could add an IsActive flag to allow marking them as inactive while not breaking existing links).