Resolving Some of The Identified Problems

Primary Keys

One of the major problems identified above is data duplication. To be able to implement a system for reducing this, we must be able to uniquely identify a record (or row, as it is known in client/server parlance). We do this using a Primary Key (PK). A Primary Key is defined as "a column, or set of columns, whose values uniquely identify every row in the table".

To continue our example on the Palm, we may make a primary key out of the FirstName and LastName columns. By doing so, we would be prevented from adding a new record bearing the same first and last name as an existing user and we could always guarantee finding the correct record by using a combination of the first name and last name. This would allow us to be able to change the address on both the Palm and the desktop, and have HotSync be able to handle that update correctly. That is, update the record without duplicating it.

In practice, creating a key based on multiple columns (especially string based columns) produces a result that is slow and inflexible. The speed is diminished by the fact that PKs are indexed (to allow for faster searching). This index is larger when using strings and the computer less easily manipulates strings. The result is inflexible in that it is wholly possible that you may have two friends who do bear the same first and last names.

Recall the definition of a PK. We simply need to be able to uniquely identify a record. The most efficient way to do this is to use a single integer value, as this is both fast and space efficient. The speed comes from the fact that an integer is based on the operating system bit size (except for Java applications) and is handled natively by the computers CPU. The space efficiency comes from the fact that only 4 bytes are required to store the value.

Another reason that makes the integer effective is that it can store a value between 0 and 4 billion (and change). It is extremely unlikely that you will ever exceed this number of rows in one table.

A final reason to use PKs is based on the requirements for a PK pool (covered later). For now, suffice it to say that Asta SkySync requires that any table intended to be used for synchronization or replication MUST have an integer based primary key.

Time Stamps

As discussed earlier, it is highly likely that time will be a vital factor in any synchronization or replication solution. To be able to use this effectively, it is important that both the consolidated database and remote databases use the same basis for time; that they synchronize their watches, if you will.

Asta SkySync uses GMT (UTC) as its base time reference. This ensures that remote databases have a base reference that will work irrespective of where in the World they are located. It follows that all records at a remote database be time stamped using GMT and that SkySync uses GMT when deciding which update was applied most recently.

One problem that arises is that of maintaining the synchronized time state. Computers have notoriously poor internal clocks. Asta SkySync servers regularly update their internal time from the atomic clock and it is strongly recommended that remote database applications also use this methodology. This will help ensure that synchronization policies that depend upon time operate most effectively.

Column & Row Limitation

It is highly probable that remote databases will not want to make use of all rows and all columns in all tables. Asta SkySync allows the developer to select which tables, columns and rows remote databases use to synchronize with the consolidated database. This methodology is often referred to as 'publish and subscribe', where the consolidated database states what it makes available (publish) and the remote database states what it would like to use (subscribe).

This is done by means of a wizard and, uniquely in this regard, Asta SkySync will allow you to sync any database with any database. For example, you may sync a remote dBase database with an MS-SQL consolidated database, a remote PDA database with a consolidated Access database, and so on.

User

One of the things that Palm does very well is to uniquely identify a user from a PDA that starts the HotSync process. It is important for synchronization and replication to be able to uniquely identify a user and a connection from a remote database. To do this, Asta SkySync requires that a username and password combination be used every time the synchronization is performed. The username and password are not used only to identify a valid connection, but also to select the correct 'publish and subscribe' policy.

By this stage it will be clear that, having resolved some of the identified problems, we still have not resolved the biggest of them all - how to handle multiple updates to the same row in different, disconnected situations. This is a large discussion that leads us neatly into the next section.

What is Replication?