Many mobile workers need access to information to help them do their jobs and to communicate information back to others in their company.

Pat Brans looks at how database management can ensure the smooth operation of this process.

Consider three examples:

  • A salesperson spends most of the day on the road visiting different clients. She needs to know about the different clients and the different people working in each client company. She may place an order and need to communicate the order back to the home office.
  • A delivery truck driver picks up inventory in the morning and drives to different customers to deliver products that were ordered beforehand. He needs to know something about each client he visits. For example he may need to know about discounts promised to a client or about the buying history of the client. The driver needs to keep a record of each delivery for tracking purposes.
  • A home healthcare worker makes house calls to care for the elderly and handicapped. She needs to know about any care previously provided to a given patient, and she needs to record information on her visit to keep track of the state of the patient and any medication provided.

Companies are now equipping these kinds of workers with small, portable computers such as laptops, tablets, or personal digital assistants (PDAs).

Each of these devices has its advantages in different situations but the important thing is that they all allow mobile workers to get at the data they need.

Enterprise database

Usually this data is stored behind the company firewall in a relational database where it is made available to other people in the company.

For example the mobile salesperson needs information on client companies, key contacts within those companies and order information. Her colleagues back in the office need some of the same data: the marketing department needs information on the client companies, the finance department needs access to the order information and so on.

It might be nice if mobile workers simply accessed enterprise databases in the same way they would in the office - that is, using a computer in a client/server arrangement, retrieving and updating data directly on a central server. But this presents at least two sets of issues.

First there is a set of issues around the fact that mobile workers can only connect over a wireless link. These include:

  • Holes in network coverage: There are still a lot of areas where wireless network connectivity is not provided.
  • High cost: It is expensive to transfer a lot of data over a wide-area wireless network.
  • Low bandwidth: A wide-area wireless link does not provide anywhere near the same bandwidth one gets in an office environment.
  • Unreliable connection: The wireless connection may be dropped from time to time, especially if the user is moving around a lot. This may result in data loss. It may also cause problems on the server side. The application may not react well to suddenly losing the connection. In cases where the connection is dropped the user may be required to perform troubleshooting, or at the very least take manual steps to recover. The result is frustrated users and potentially corrupt data.

Second, if mobile workers are accessing data directly on a back-end server, there are performance issues. The server has a higher workload, taking requests from hundreds - or even thousands - of mobile workers.

Independently of the server workload, since all client requests have to go all the way to the server and back, retrieval is slower than if the data were stored directly on the device.

On-device data

Indeed the best solution is to have a subset of the data stored on the small computers used by mobile workers.

In addition to solving the problems listed above, a nice side-effect is that the data is physically separated. Only the information needed by a given worker is made available to the worker's device.

Why not just put all the data in a flat file and download that to the device? For very simple applications using very small amounts of data this might be the right solution.

However using flat files is limiting in several ways, including the following:

  • Synchronisation: It is very difficult to synchronise the data on the mobile device with the central database. If changes were made in both places a file comparison has to be performed and then some decision has to be made on what to do about differences.
  • Inserting and deleting: Inserting and deleting data is complicated, especially if the data is ordered. A large part of the flat file has to be rearranged anytime there is an insertion. For a deletion you can either leave a hole in the file or you have to rearrange all subsequent records.
  • Searching: Searches are generally much slower when data is represented in a flat file. Your application either has to know specifics of how data is represented in the operating system or the search has to be sequential. When there are thousands of records to search you start to notice how long it takes to do a compare on each record.
  • Use of a relational database has some nice advantages. You can synchronise between the mobile device and the central database on a row-by-row basis. The operations of insertion and deletion are easy since the data does not need to be stored sequentially to maintain logical order. Search timescales logarithmically, since indexes can be organised in trees. Finally, relational databases allow you to handle more complex problems by dividing information into logical units and linking those units in relationships that mirror the real world.

Mobile database management

This is why many solution providers choose to use a mobile database management system. Each mobile device runs a database (remote database), which is synchronised with the central database (consolidated database).

Each remote database gets a subset of data. The remote database is synchronised at appropriate intervals, which could be every few minutes, every few hours, once a day or even less frequently.

At every synchronisation the remote database receives updates from the consolidated database and vice versa. After each synchronisation the remote database and the consolidated database should be consistent.

Consider the following scenarios:

  • The salesperson synchronises at night using an internet connection from home (or from a hotel). At this time any new orders she entered on the device are posted to the consolidated database. During the same synchronisation session the consolidated database may push to the remote database updates, such as new product information, new customers, new contacts or new pricing.
  • The delivery truck driver synchronises over Wi-Fi at the warehouse in the afternoon to post new orders and delivery logs. He also synchronises in the morning to get route and on-truck inventory information. Throughout the day he might synchronise wherever a Wi-Fi link is available.
  • The home healthcare worker synchronises at night from a home internet connection to upload a log of what she did on each patient visit and to get information on patients she has to visit the next day.
    In most cases the data is partitioned. For example the delivery truck driver only gets information on his routes, or the home healthcare worker only gets information on the patients she sees.

Important considerations

There are certain aspects of the mobile environment that require special attention. The things solution providers have to consider when using a mobile database management system include security, transactional integrity, data consistency and performance.

In an office environment, simply the fact that data sits on a server within the walls of the company goes a long way to securing the data. In a mobile environment you have copies of data sitting on mobile devices outside of company premises. Who knows what might happen to those devices?

Some of the security concerns that have to be handled by the mobile database management system are:

  • Access control: Only known users should be able to access the remote database; and different known users should have different rights on data. This kind of protection can be provided through user IDs and passwords, and through administration functions allowing an administrator to configure user rights and privileges.
  • Data privacy: It should not be possible for anybody to read the data on the device without using the database tools. To ensure privacy, data can be stored in encrypted form.
  • Unwanted access to the synchronisation process: The synchronisation process itself must be protected. To prevent unwanted access the server can authenticate the remote database and data can be encrypted over the communication link.

In any database application transactional integrity is a concern. But in a mobile environment, where there is a higher risk of interrupted communication, one should take special care.

A transaction is a set of operations that must be taken together as a logical unit.

It is necessary to ensure transactional integrity in cases where if one of the operations is carried out but another fails the database is left in an inconsistent state. For example a salesperson might take an order from a customer.

This order may raise the total amount spent by the customer to a level that kicks in a new discount. The application used by the salesperson would need to insert a new order into the remote database and also update the discount field in the customer record.

At the time of synchronisation the 'insert' operation adding the new order and the 'update' operation updating the customer record must be taken as a transaction. Either both operations are applied to the database or neither is.

The nature of the mobile environment is that there are hundreds or thousands of replicas of the database. Data consistency is something to which solution providers have to pay special attention.

The database management system must ensure that all changes are replicated to each remote database over time in a consistent manner. Different devices may have different copies of data at any given time but on synchronisation they are brought back to a state that is consistent with the consolidated database.

Finally we have to remember that our relational database management system is running on a small - and therefore constrained - computer. Performance is important. Caching must be efficient, and database operations must be carried out with optimal use of time and resources.

On many small computers RAM is limited. It is therefore important for the database management system to have a small memory footprint.

Closing thoughts

Data is an essential part of almost any mobile application. Getting the data to and from the device in a way that ensures security and data consistency is no simple task. Yet these functions, as well as the speed of data retrieval, make a big difference in the success of the entire mobile application.

When designing a mobile solution, think carefully about your data needs.

Pat Brans manages strategic alliances at iAnywhere Solutions, vendor of mobile and embedded databases. iAnywhere Solutions is a Sybase subsidiary.