Two PC Databases

2-3 years ago I was bracing for the onslaught that was to be Yukon – the PC database from Microsoft built on a SQL Server core that would be part of the Windows operating system. This personal database would anchor all sorts of Office, Exchange, Internet and MS Messaging services. Somehow, Yukon got lost in the shuffle that has been the stampede to get Longhorn/Vista out the door.

Now two personal databases have emerged which will make the eventual entry of Yukon more problematic. These two superb personal databases are SQLite, an Open Source gold nugget of unbelievable value and Apples Bento – a resized and integrated Filemaker Pro subset for Apple Leopard users. Both have interesting value propositions which we examine below.

Bento

For those in the know, Bento has appeared in the Apple software pantheon as a decades ago Open Doc system. This re-incarnation may have some inspiration from that and a return of Filemaker to its “a simple database for end users” roots. But for $50 single user ($100 for Family edition), Bento offers 4 important ingredients to Mac and Leopard end users:
1)A simple to use database with easy to understand documentation and samples – entry into the database world has been simplified and made accessible by the next three Bento features;
2)Simple forms, reporting and data access templates that are GUI good looking and simply work reasonably well out of the guided usage box;
3)Integration with Mac Office and other programs that produce CSV and other tabular output
4) A database with immediate tie-ins to the personal databases Mac users want access to the most: their eMail Address Book, their iCal Events, and their iCal Task lists. A whole set of useful apps spring from these resources. And more are promised.
Now I can think of at least two dozen DOS and then Windows databases that were to have these same features including Rbase, Paradox, dBase, Access and an early version of Filemaker Pro. But all of these systems fell on the sword of exponential database programming complexity. And none delivered the templates nor the connectivity to Outlook, Office , Excel on a consistent basis other than Access and that was constantly changing. The key is that Apple is returning to ease of use, dozens of customizable templates, pre-wired connections to valuable user data – all of the things that may make databases palatable to end users. But Bento may fall to SQLite, a free personal database of no small value.

SQLite

PCWorld lists Google Gears as the number one of the top 25 PC innovations of 2007. I think that is somewhat exaggerated; but the database that is distributed with and powers every copy of Google Gears, Skype, McAfee Anti-virus, iPhones, Adobe Air, Firefox, and dozens of other applications is SQLite.

And SQLite is that less than 800KB for a complete SQL Database with all the language features and the robust security including Rollback and Commit to ensure referential integity. And the price of SQLite is free and Open Source. If you are doing database work, especially on a PC or small workgroup in a network, you simply cannot afford to not know and take advanatge of SQLite.

But there comes two trade-offs with SQLite. First, because SQLite is a “real” SQL database that makes its programming and usage beyond the reach of most PC users when interacting directly with SQLite. But do not despair. Just google “SQLite Tools” and you will get a list of dozens of SQLite tools aviable either for free(and they are very good) or for a small fee ($25-80). Many of the tools are in the mold of PHPMyAdmin – a web based interface into SQLite that is developer oriented. A number are in the style of Oracles SQLManager – geared for the power user. But a few have taken a novel approach to making SQLite approachable and I shall review them back at the main theOpenSourcery.com website.

The second trade-off with SQLite is that it really is not geared for multiple users. Two to five and the response time stays pretty fast, but as you add users that response time falls off. Now this can be a problem with developers who use SQLite and find its blazingly fast single user response time intoxicating. They then program SQLite in tasks requiring dozens of users, blissfully unaware that the response time falls off catastrophically in these situations. So the second SQLite trade-off is that it is in danger of overzealous application. If someone is selling workgroup software with over say 10 users, and SQLite will be the central server – caution is urged.

But after these two cautions, the sky is the limit on what you will be able to use SQLite for. I daresay it will give XML a run for the money as local database and configuration file. I fully expect some OS to use it as like the Microsoft registry to manage applications and the OS configurations. Ditto for app programs that have several workspaces like Photoshop or Eclipse. And as for games …

So take a look at Bento for another interesting crack at “make-databases-easy-to-use” while expect a Bento-ized version of SQLite (which runs for free on most versions of Windows, Mac, Linux and other OS) as it starts to appear just about everywhere, from mainframe to PC to all sorts of mobile devices.