HTML5 App Database Syncing

Question

I currently am working on a project that involves storing data in an HTML5 SQL-Lite Database. Currently, I have a schema as follows (4 Tables):

TransData:
-----------------------------------------------------------
|   TID   | UserName | TransColor | ... |  Date  |  Note  |
-----------------------------------------------------------
|    6    | Brendan  |      Red   | ... |        |        |
-----------------------------------------------------------
|    7    | Brendan  |      Red   | ... |        |     1  |
-----------------------------------------------------------

FullData:
-----------------------------------------------------------
|   TID   | UserName | TransColor | ... |  Date  |  Note  |
-----------------------------------------------------------
|    1    | Brendan  |      Red   | ... |        |  Start |
-----------------------------------------------------------
|    ...  | Brendan  |      Red   | ... |        |        |
-----------------------------------------------------------
|   40    | Brendan  |      Red   | ... |        |  End   |
-----------------------------------------------------------

SalamanderData:
----------------------------------------------------
|   SID   | SalamanderName | Length | ... |  TID   |
----------------------------------------------------
|    1    | Northern-Slimy |   16   | ... |    6   |
----------------------------------------------------
|    2    |   Two-Lined    |   26   | ... |    6   |
----------------------------------------------------  
|    3    |   Two-Lined    |   12   | ... |    7   |
----------------------------------------------------  

SalamanderData:
----------------------------------------------------
|   SID   | SalamanderName | Length | ... |  TID   |
----------------------------------------------------
|    1    | Northern-Slimy |   16   | ... |    6   |       
----------------------------------------------------
|    2    |   Two-Lined    |   26   | ... |    6   |   
---------------------------------------------------- 
|    3    |   Two-Lined    |   12   | ... |    7   |
----------------------------------------------------

Note: The "Note" column in TransData is used to point to the beginning data point of a collection in the FullData field.

The database between my App and the server SHOULD NOT BE IN SYNC. I am merely trying to dump all of these tables into the database on the server (and by dump I mean, update the references to other tables, and then insert into the server database).

I was going to use MAX(TID-Server) + TID-App = new TID-Server, and cascade the updates down the tables.

How would you go about doing this?

Answer 1

From the comment by Dan Pichelman, the problem is that the client inserts records into the local DB and, to do so, has to determine primary keys for them. But, given all the different clients doing the same, the new PKs will clash when they hit the server.

This is a common problem is systems which are physically disconnected (at least sometimes) or where there cannot be a single point of failure such as in a shared sequence generator.

Some common solutions are:

GUID

Here the PK is a 128-bit (or larger) random number. The chance that any two PKs are the same is exceedingly small. But to reduce the change of collision even more, the GUID algorithm includes seeding with unique machine identifiers (the network MAC) and time. Two GUIDs produced on the same machine will never collide, and neither will GUIDs produced on machines with different MACs. Most machines and languages have native functions to generate GUIDs, but JavaScript doesn't. See:

• Create GUID / UUID in JavaScript?
• Collisions when generating UUIDs in JavaScript?

A partition naming scheme

In this scheme, the PK is again a large number (bitfield really), and you partition it into a hierarchical manner. A good example is the international telephone system (at least before portable numbers). Here the telephone number is divided into:

• Country code: eg USA - 1
• Area code: eg Sunnyvale - 615
• Subscriber number, controlled by the exchange.

In your case, you might partition the number by:

• User login (eg a number unique to each user)
• Session id (eg a number unique to each logon per user, to distinguish between different sessions of the same user but on different browsers/computers)
• Serial number

Combining all three you will have a guaranteed unique PK.

PK 'license' Server

The first two suggestions have the merit that they work completely disconnected. If you have a connected client, you could have a web service that supplies PKs when the client requests them.

For efficiency, it might return a batch of, say, 100 numbers. This might even be returned when the user logs on.

The client can use them all and request for more. There might be cases where the client forgets state and leaves a 'hole' in the global PK sequence. This would almost certainly not be a concern.

Some considerations

Sometimes you might like sequential PKs for table ordering purposes. In which case, do you need to order by client or by time of creation? If either is important, you might rate the partition naming scheme higher. Put the client or time as the first partition as appropriate. Alternatively, add more columns to your table.

If you don't want the fixed structure of the partition naming scheme, the GUID will work well.

If you want central co-ordination, use the PK license server.