Mobile databases: how to have the data you need in the palm of your hand. 10 keys to planning, building, and deploying databases to PDAs.

DATABASES ARE CRITICAL TO DAY-TO-DAY BUSINESS, so it's no surprise many companies want to combine the business information databases contain with the productivity and efficiency that comes with wireless technology. The mobile world has a wealth of options for shrinking databases for use on small devices such as PDAs. But, with so many options, it's hard to know which to choose for your application needs.

When selecting a database approach for PDAs, it's important to understand that building the database is only part of the process of constructing the application. Building your first application for a handheld or mobile device requires advanced planning. Not only do you have to understand the data, but the selection of a database for the mobile device requires coordination among database specialists, developers, telecommunications experts, users of the application, application server specialists, desktop support, and the Help desk. For small businesses or applications, one person might wear multiple hats. However, for large rollouts and complex databases, organizations shouldn't make the mistake of thinking that a small device and a small application only require a small effort. In fact, an organization should plan to spend more time designing, developing, and deploying its first mobile application than a traditional database application.

Setting the stage

This article doesn't focus on building pure browser-based applications where all the data resides on a back-end server. Although browser applications can be a valid approach to collecting data, they don't usually have a local data storage model, and they require a constant connection. Chances are good users will want to store data on their devices. Nor does this article focus on deploying databases to laptops. Laptops have plenty of processing power and storage. That would be too easy.

This article assumes you want your application on a small handheld device; maybe a smartphone or a Palm, Pocket PC, or Linux PDA. Any small device where processor power and storage are at a premium could be your target client. Now, that's a challenge.

After the user collects data on the mobile device, they'll want to synchronize the data to a desktop database, such as Microsoft Access, FileMaker Pro, Microsoft FoxPro, Visual dBase, or maybe even Microsoft Excel; or an enterprise database server, such as Microsoft SQL Server, Oracle, IBM DB2, Sybase, or others. Your ultimate goal is to coordinate the collection of data, download subsets of data, and keep all those remote, small data collection devices up-to-date--and do it in a secure, manageable manner. You also want users to be able to sort, search, add, delete, and edit data on the mobile device as appropriate.

There are many different kinds of databases out there; for example, models that are simply flat files, hierarchical, and object-oriented, etc. However, this article focuses on relational databases, the most common model. These databases use Structured Query Language (SQL) to manipulate and create data relationships in tables.

Now that the assumptions are out of the way, I'll look at the 10 critical planning points for mobile databases.

[1] Downsize the database

Your first inclination might be to try to transfer the whole database to the mobile device. Don't! You can, and should, downsize data; you have three options for doing so. First, omit tables that might not be applicable to a mobile version of the database. For example, a field service application probably doesn't need all the billing data on the device. Omit tables that don't add value to the data collection process in the field.

Second, consider reducing the columns of data from a table. In the mobile version, it might not be necessary to include all the data contained in certain columns of tables. Most databases don't require an exact match of tables because they provide a way to map columns from the mobile database to the back-end database. Using the field services example, you might have an inventory database with detailed columns for describing the product. Only use the information that helps a service person make decisions in the field; leave the details behind.

The third way to downsize a database is the most critical--minimize the rows of data in a table. In addition to the storage savings, minimizing the rows or records in a table minimizes conflict resolution problems when users synchronize. Because users can work with the database directly on the device in a disconnected mode, the devices work with the data independently of each other. For example, if a sales force application only downloads records of clients each sales person is responsible for (and there's no overlap in responsibilities), no two salespeople or devices will be able to change the same client record concurrently, and you minimize the data on the device. Another way to minimize conflicts is to mark certain database tables as read-only on the device. Remember: The focus of your mobile databases application is collecting critical data on site, not managing all the tables that make up the database application.

[2] Consider your connection method

Most people think mobile database applications require wireless service. However, mobile doesn't equal wireless. Mobile databases can be completely self-contained. There are three ways to architect the synchronization with your back-end database.

First is a store-and-forward architecture. With this approach, the user synchronizes data on a regular basis via a wired or wireless connection. For example, a user might sync their field service application at the beginning and end of each workday. The most efficient setup would be to load only the mobile database with the list of work orders or customers. Through the course of the day, the user updates the record to reflect their interaction with the customer, services performed, and time and material used. They could even capture a customer signature and, if they have a mobile printer, print a simple receipt. At the end of the day, they upload this information to the main database and remove it from the device. The user would have to store only incomplete and new service orders on the device. This method doesn't allow access to the most current data, but it's a cost-effective start to mobilizing your application.

The second database synchronization method uses a constant connection. This seems like the ideal solution because it looks and acts like a

wired connection. With a constant connection model, you may not even use synchronization; you could build dynamic SQL statements and pass them over the connection to the server. The application would then return subsets of data to the device. Unfortunately, users won't always have a connection. You'll also run into problems when dropped connections leave transactions incomplete.

The third option is a hybrid architecture for intermittent connection. This combines the first two models and makes the best use of the services available. If a device is within wireless coverage, the application can perform dynamic queries and synchronization (it depends on the product's capabilities). If the user is out of range or coverage, they can still capture data and sync later. Sounds like the perfect solution, right? Unfortunately, perfection has its price: Using an intermittent connection architecture may be ideal from an implementation standpoint, but it requires more development effort.

Whatever model you use, it's important, to build in conflict detection and resolution capabilities, such as log-based histories, business rules, transaction rollbacks, role-based conflict resolution, or time-based resolution. You can take care of the physical connection to the database with a combination of docking solutions such as Ethernet connection cards, cradles, cables, or wired dial-up. You can also use wireless docking solutions such as Bluetooth and infrared. Of course, you also have many options if you want a wide area wireless connection.

[3] Know your Wireless options

One wireless option is wireless LAN; Wi-Fi is the best known example. There's a lot of hype about wireless LAN connections being everywhere from coffee shops to the factory floor. But, this technology is hardly pervasive--yet. Wireless LAN connections are a great solution for applications in places such as warehouses and retail showroom floors. However, they have little value today for applications used by roaming salespeople or field service representatives on the road.

There are other wide area wireless options, but don't be lured by the simple pitch that faster is better. There are many considerations beyond speed. Two key features to look for and evaluate for your wireless connection are the use of a packet or switched service and the opportunity to use TCP/IP services.

Packet-based services generally offer a constant wireless connection and are priced based on the amount of data transmitted. Next-generation 2.5/3G services such as GPRS and CDMA are packet-based services. Switched data connections are services that follow a connection-transmission-disconnect cycle. These services are priced based on connection time, not the amount of data transmitted. Examples are GSM or high-speed circuit switched cellular.

From an application perspective, you have to understand how the wireless service will connect with your back-end data. Does your wireless connection offer IP based services? Some connection technologies act like wired connections and offer IP-based services such as CDPD and the 2.5/3G services offered by most wireless carriers. IP-based wireless connections are easier to build because they act like wired connections. Other wireless services (Motient, Cingular Interactive, two-way paging connections) aren't IP-based and require additional software tools or more complex architectural approaches to push/pull data.

Ultimately, your selected wireless service will directly influence your synchronization architecture.

[4] Choose your target client(s)

Organizations often try to standardize on a particular mobile device platform, typically Palm OS or Pocket PC 2002. However, new clients based on Linux, Symbian, and phone-based systems are becoming more common. Do you want to build an application for multiple target clients? If so, you must select a database that runs on all of them. Indirectly, the selection of a mobile database may limit your platform options. For example, Microsoft Server CE is only for Microsoft CE-based devices.

Because you'll probably require removable storage and other wireless add-ons, you should assemble the entire device with all components. Do you have enough slots to add removable storage, a wireless card, a keyboard, bar code reader, GPS devices, or other hardware connections? Find out if the database and application can run off removable storage. Some software can't.

The mobile device should have a keyboard. Depending on users to enter data with Graffiti or other handwriting recognition techniques will be an obstacle to adoption of the application. On-screen keyboards work only for small amounts of data entry. To speed up the data-entry process, you should strive to collect data from related database lookup tables when feasible, and let users submit as much data as possible via drop-down boxes or other controls that simplify the data entry process.

[5] Know your data

What type of data will you be dealing with on the mobile client? Mobile data collection and storage offers a new world of options for rich data types. This data isn't just video and audio, but also GPS and map images, photographs, and signatures. These data types require your mobile database to accept binary large object (BLOB) data types. (BLOBs require special handling because of their size.) You also have to make sure date and time formats are consistent and that significant digits of integer and floating-point data align. You also must format binary or logical values consistently in both the mobile and back-end databases.

[6] Match skillsets in your organization

The skillsets in your organization should be the driving force behind your choice of database. The critical skillsets for PDA database application development are those of your developers. Are you a heavy VB organization? Do you have C/C++ skills? Are your developers Java-centric? Do you have applications in Access you want to mobilize? Or, does your organization lack heavy development skills? Your skillsets will influence your selection of development tools to integrate the database engine.

Knowledge of a back-end database doesn't necessarily translate to an understanding of the corresponding mobile database. Because most organizations support some combination of Oracle, DB2, Microsoft, and others, it's important to consider the tradeoffs among your choices. You can select one mobile database platform that has a database-agnostic approach for all mobile development efforts (for example, Sybase iAnywhere provides a open architecture for optimizing mobile database connections on a variety of client devices and across most back-end databases) versus letting the back-end database determine its mobile counterpart.

[7] Plan for maintenance

Now you know that picking a mobile database isn't as easy as just looking to your back-end database. You must create a match with the development tool, the mobile database, the connection method, the target device(s), and the back-end data. When you assemble these pieces, you're ready to build and run your application. But, before you roll out your application code, mobile data, and devices, you should plan for the long-term distribution and maintenance of your application and the handheld devices.

This requires mobile asset management software. This software lets you control groups of users and devices, enforce application policies, and assist with scheduled updates of application code. Some products also hook into other enterprise management tools such as Microsoft Management Console, HP Openview, CA Unicenter, IBM Tivoli, and other enterprise IT management software.

[8] Secure data and devices

Data security is particularly important on mobile devices. You should consider encryption and authentication over wireless services. You can often adapt your existing remote wired technologies, such as VPNs or RSA SecurID, to work with your wireless solution.

However, it's more likely the user will lose the device than it is the data will be compromised over the wireless connection. Plan on losing some devices and protecting the data on them. You can password-protect the database; this security can range from high-level access to the application, down to locking tables and records.

Be careful about locking removable storage, infrared beaming, and serial port leakage of data. It's easy to lose valuable data on a removable storage card. Also, if the device is lost and the data is stored on the card, someone only needs to remove the storage card and put it in another device to gain access to the data. Open IR and serial ports offer ways for someone finding a lost device to defeat its security.

If you want to increase the chances of recovering a lost device, consider small asset tags offered by a company called StuffBak (http://www.stuffbak.com). StuffBak is a loss protection service that helps you retrieve missing devices. Just place the unique ID labels on the equipment. Lost devices have a number and Web site to contact, but there's no information linking the device to your company. You can offer rewards for the returned item.

[9] Create a total mobile infrastructure plan

Along with putting together all the components for the device in preparation for your database application, it's a good time to consider other applications you want to install on the device. Will users need wireless e-mail? Mobile versions for word processing, spreadsheet, and presentation software? What other applications will the user need to leverage the mobile platform effectively?

You can't duplicate the desktop, but you should consider adding any tools that could be effective on the mobile device. Planning, packaging, and training on your entire suite of applications at the launch of your database application makes or a more effective deployment.

[10] Think outside the box

When dealing with databases and emerging wireless services, it's important to think outside the box. Notification tools are one of the many interesting categories of products available.

Over the last few decades of computing, data management has progressively become more personal. Originally, data was relegated to the backroom gurus. Users received standard volumes of reports. The PC generation saw the advent of growth for personal databases and approaches to downloading periodic subsets of data that were crunched and manipulated on the local machine. The client-server era made data access more distributed; silos of large applications let users create custom reports directly from the data. With the Internet, we saw the re-assembly of all these large silos of data into data warehouses. New tools allowed not just custom reporting, but a staggering amount of online slicing and dicing of data.

So what's the next natural progression in this move toward real-time control of data and information? Notification services! Notification services act like small alert messages. If you can define what you're looking for when you slice and dice data, and can define the rules and trends you're looking for in the data, you can begin to set up business alerts. You can push these alerts to a wide variety of wireless devices. Microsoft SQL Server Notification Services offers this type of wireless alert reporting.

You can use these alerts to notify supply chain partners of re-order points. You could also use them for consumer applications to help track changes in individual financial portfolios or notices of special events individuals want to track. Using Microsoft SQL Server Notification Services, the New York Times developed an application that provides a real-time real estate tracking alert that notifies subscribers of new listings posted based on their individual criteria such as price, location, and square footage.

Help for admins

As a final thought, pity the poor database administrators in your organization. They are probably on call 24x7, constantly monitoring the magic things that happen (or don't happen) on your database servers. Luckily, a company called Expand Beyond (http://www.xb.com) offers a product that provides some relief. PocketDBA helps restore some sanity to the never-ending task of database server maintenance in organizations (figures 1 and 2).

[FIGURES 1-2 OMITTED]

Using their choice of a variety of wireless devices on the Palm or Pocket PC platform, database administrators can reach out with a secure connection and manage almost all aspects of their enterprise servers including Oracle, SQL Server, and IBM DB2. If you really want to get your database administration people behind your mobile project, get them tools to help them do their job. One day, your mobile database application may need some critical real-time care, and you'll welcome the timely attention these tools offer.

Lay the groundwork

Your first efforts at mobilizing your database should include some consideration of all the points listed. However, your first efforts won't seem as Herculean if you take steps to plan for all the issues I've discussed. Focus on smaller applications that offer a quick ROI, select development tools your staff feels comfortable with, and roll out a pilot project. Add the level of security your application warrants and plan a full deployment as you assemble the remaining pieces. Finally, adding a mobile asset management component will help you make your incremental deployment a reality and help you set up an infrastructure within which you can deploy future mobile applications.

Key Mobile Database Companies

Sybase

Sybase Adaptive Server Anywhere consists of two main components: MobiLink and UItraLite. MobiLink provides the synchronization component, and UltraLite is a small relational database, down-sized for handheld deployment. The UltraLite component exposes the API and allows a direct connection with AppForge MobileVB control or a Microsoft ActiveX or a Java class library. The MobiLink synchronization works with ODBC data sources including Microsoft SQL Server, Oracle, IBM DB2, and Sybase. Target clients include Pocket PC, Windows CE, and Palm devices.

http://www.ianywhere.com

Oracle

Oracle 9i Lite provides a mobile database for offline applications for Pocket PC and Palm. The lightweight database component, along with the Oracle 9i Application Server provides Java support, centralized management of applications and devices, and end-to-end secure data synchronization.

http://www.oracle.com

IBM

IBM DB2 Everyplace uses Java, XML, and a scaled down (180KB) DB2 database to provide support for the major mobile platforms. The DB2 Sync Server provides secure synchronization between the mobile database and the data source.

http://www.ibm.com

Microsoft

Microsoft SOL Server CE is targeted at Windows CE platforms and can be used with Microsoft eVB, eVC++, AppForge, Microsoft Visual Studio .NET, and the Microsoft .NET Compact Framework. Data synchronization is handled in one of two ways: remote data access or merge replication. Remote data access allows remote execution of SQL statements on the server to push and pull records from the server. Merge replication automatically synchronizes data between the device and the server.

http://www.microsoft.com

Synchrologic

Synchrologic provides simple visual data mapping tools to help organizations quickly create a synchronized solution among various device databases and back-end databases, Key products include Synchrologic iMobile Data Synchronization and Synchrologic Real Sync Server. http://www.synchrologic.com

Extended Systems

Extended Systems Xtnd Connect Mobile Objects lets you create and access objects on your Windows-based server from handheld devices. Separate products also allow PIM synchronization and mobile device management.

http://www.extendedsystems.com

TIP 7

Mobile Asset Management

Just like desktop management tools, mobile asset management software helps organizations track their devices and control what's on them; specifically, they can:

* Provide automated back-up and restore capability for files stored on devices

* Deploy new applications, content, and updated files (push data to devices), as well as remove data from devices (collect files, remove old versions of software, delete corrupted files, etc.)

* Manage the devices; for example, track OS type, device type, free storage, battery life, last connection, serial numbers, etc.

* Manage distribution by groups of users

* Monitor connections, log usage, provide audit trails of activity

* Allow remote support of the device

Xcellenet

Xcellenet Afaria

http://www.xcellenet.com

Aether Systems

Aether ScoutIT

http://www.aethersystems.com

Extended Systems

XTNDConnect Server

http://www.extendedsystems.com

iAnywhere

Manage Anywhere Studio

http://www.ianywhere.com

Puma Tecn

Enterprise Intellisync Server with

Administrator Console

http://www.pumatech.com

Mobile Automation

Mobile Lifecycle Management

http://www.mobileautomation.com

Synchrologic

Systems Management

http://www.synchrologic.com

Novell

ZenWorks

http://www.novell.com

TIP 8

Mobile Security Companies

Most mobile devices have a security option to secure the device with a password. However, this option doesn't force the user to password-protect the device, nor does it help you set up a policy for password security. You need a product that automatically password-locks the device when it powers down or is inactive for a period of time.

Hem's a list of vendors that provide advanced security solutions.

Asynchrony Solutions

Asynchrony Solutions PDA Defense Enterprise provides security for Palm OS devices to lock down potential security holes such as data transfer over HotSync and IR ports, reset options, and debugging mode techniques. It lets you use various password encryption options for selected applications and data.

http://www.asolutions.com

Communication Intelligence Corporation

Communication Intelligence Sign-On provides a biometric signature that uses advanced behavioral mechanics of a handwritten signature for Palm and Pocket PC devices. This software not only secures the device, but gives you a way to capture and use a legally valid e-signature.

http://www.cic.com

Certicom

Certicom offers a full collection of security products such as movian-Crypt, movianVPN, movianMail, movianDM, TrustPoint client, and development tools to add security to your applications.

http://www.certicom.com

Diversinet

Diversinet Passport Authorization provides security software for digital signatures, authentication, and encryption with PKI products for wireless and mobile devices.

http://www.diversinet.com

TealPoint Software

TealPoint Corporate TealLock is a secure automatic locking program for Palm. It gives you serial and infrared lockout, data encryption, optional data self-destruct, and numerous exclusive activation and customization options, including flexible timed activation, configurable locking screen, and Graffiti shortcuts for locking the device and hiding private records.

http://www.tealpoint.com

TrustDigital

TrustDigital offers a suite of eight security tools for mobile and wireless protection on both Palm and Pocket PC devices. http://www.trustdigital.com

Pocket Access Substitutes

Missing from the current version of the Microsoft Pocket PC 2002 Office suite is a version of Pocket Access, instead, Microsoft has developed a rich version of SQL Server for CE called, appropriately. Microsoft SQL Server CE. Even though you can transfer Microsoft Access data tables to the Pocket PC and handle development with the free VB development tool (eVB), you might still want the simplicity of an Access environment on the Pocket PC.

Here are a few products that have filled the Access gap on Pocket PC devices:

Pocket Innovations Pocket Database

Version 5.1 of this product offers Visual Basic scripting and module support for Pocket PC applications. The product is faithful to Access' relational model, and the latest version offers advanced data integrity capabilities by including features such as relational integrity, cascading updates, and cascading deletes.

http://www.pocket-innovations.com

PocketSoft abcDB Pro

PocketSoft provides a fully relational database with support for forms. queries, and SQL. The query designer allows both grid-based and SQL design modes. It can use ActiveSync's Access conduit to synchronize files with a desktop Access database.

http://www.pocketsoft.ca

DDH Software HanDBase

HanDBase offers a collection of database application modules you can use for development and deployment to Palm and Pocket PC. You can sync mobile databases with Access, FileMaker Pro, and other ODBC databases.

http://www.ddhsoftware.com/handbase.html

MOBILE BUSINESS BENEFITS

If you think a mobile database is just a stripped-down version of its enterprise counterpart, think again. There are several factors to consider when planning your first mobile database application. Some up-front planning will save you major headaches down the road.

Looking for more resources on mobile database development? Subscribers can read an Extended version of this article containing a book list and product information at http://Advisor.com/doc/12170.

Francis Rabuck is a thought leader and nationally recognized expert in mobile technology. He's frequent speaker, writer, advisor and judge at major computer industry events.