4 online databases structure, share data

There are many popular, low-cost ways to toss text onto the Web, from blogs and Google Docs to social networking sites. Likewise, you can find a lot of sites where you can post photos and videos. But until recently, less attention has been paid to online databases -- and that's a pity, because lots of people besides database geeks would benefit from a bit more structure to their data.

Now, though, there are several options on the Web for people who want to put their information into a database structure without having to deal with database installation, hosting and front-end coding with a language like PHP, Perl or Ruby.

Admittedly, a Web database service will never be able to offer all the flexibility of custom-coding your own application. However, these services can be very useful for creating small personal and business apps like contact lists, project tracking or charitable donations tagged by type. And the ability to share and collaborate on structured data via a Web interface adds another useful facet to these databases.

In other words, if you've got structured data -- or information that would be more useful if structured -- it's worth giving an online database service a whirl.

In order to get an idea of what kind of database services are currently available and how effective they are, I decided to put a group of Web-based database services through their paces. In choosing which services to test, I focused on four offerings that are free or low-cost, reasonably robust, and offer administrative control over who can view and edit your data: Caspio Bridge, CogHead, Dabble DB and Zoho Creator.

I left out longtime player Intuit QuickBase due to its minimum US$249/month price tag, and Lazybase due to its lack of even basic access control (there's no log-in; only a "secret URL" to edit your data).

How we tested

A good online database must do more than simply host your data on its site. It should also have an easy interface for designing a data structure; make it simple to upload, download and edit existing data; offer robust ways of viewing and interacting with the information; and provide granular administrative control over who can view and alter information.

To test each online database offering, then, I created a simple table; designed more complex, inter-related tables; uploaded existing data; and embedded results in an existing Web site.

For the multi-table effort, I included many-to-many relationships, where, say, a category includes many products, while a product belongs to many categories. For example, the iPhone can be listed under both "mobile devices" and "personal technology," and there are many more entries in "mobile devices" than just the iPhone (despite what some iPhonatics may think). Such many-to-many relationships are precisely where structuring data can prove most useful, but they're also often the most difficult to implement.

I also imported existing data from Computerworld 's Reviews database. I started with a table of all our product categories -- browsers, desktop apps, desktop systems, laptops and so on. Next, I imported a table of products reviewed: Asus EeePC, BlackBerry Curve, iPhone, etc. The product table already included categories for each product.

A key issue: how to make the database "know" that the products in one table map to the product names in another table -- especially when the field contains more than one entry. This was a challenging feature for several of these services.

It turned out that the database services have different strengths, and thus are best suited to different needs. Read on to find out just how easy these affordable database services actually are, what (if anything) you sacrifice in return for a flashy user interface, and whether one of these services will fit your data needs.

Caspio Bridge

Caspio Bridge is among the most enterprise-focused of the online database services I tested, and its pricing reflects that: At $8 per "data page" (a Web entry form or search page, for example), costs can add up for frequent users. Some of Caspio's more robust sample applications run five pages, which amounts to $40 per month.

As a result, Caspio Bridge isn't the best option for creating quick, personal applications, since you can use up data pages each time you want a form for adding or updating a table of data. (If you just want to test Caspio Bridge, you can try a free personal account, which lets you use two data pages.)

In addition, more than the other entries in this space, Caspio requires its users to know something about database structure. There's no pretty drag-and-drop user interface for creating tables and fields -- it's all text-based. This is not a service for users who don't like to dive into data.

If you're simply creating a basic, single-table database, Caspio is simple enough. For example, importing data into Caspio Bridge is straightforward when pulling in Access, XML or delimited text files. You can append data to an existing table as well as create a new one.

Adding and naming fields is also fairly easy, including the process of selecting an autonumbering field to generate a unique numerical primary key for each record.

The real Caspio value-add comes after your data tables are set up. A wizard-based system makes it relatively easy to interact with the data. Do you want to collect more information in a Web form? Let users search and view? This part is painless. You just answer a few questions and make a couple of selections, and Caspio immediately generates your forms.

For example, I wanted to create pages where I could search my data and show results. I used a wizard to select a data source table, choose a style (you can add your own), and name the page. I then picked the fields I wanted in my form, configured each for the search form and chose a results page layout and sort order (there's an option to add interactive sorting).

If I wanted each result on the search page to link to a page with more details about that listing, the wizard let me do that, too. In a few steps, I was able to turn existing data into a Web-based application I could embed within an existing Web site or use at Caspio's site.

I used Caspio last year to set up a query form for Computerworld 's America's Techiest Cities feature, and it was relatively easy to do, especially compared with coding my own with something like PHP and MySQL. However, creating a true relational database -- such as a product-review database with tables for products, product categories and articles -- turned out to be dicier.

You'd expect a text-based, enterprise-focused database host to have a fairly straightforward way to create such a structure. Caspio does not, and that's not just because I missed it in the documentation -- in a live-chat support session, one of the support staffers said he'd need to see diagrams of my data structure before he could help me. Luckily, phone support was more helpful -- a staffer was able to successfully walk me through the process.

I especially had problems figuring out how to put together the "many-to-many" relationships. After I called tech support, I realized that there was no way I would have figured out that multistep process, which involved configuring the results page with a custom HTML block using field variables. And I was never able to create a data-entry form for internal use that allows selecting multiple options from another, existing table. (Tech support finally created one for me, which I could then copy.)

In addition, if you're used to a desktop database app, be warned that Caspio's response can be a bit sluggish as you wait for the servers to respond to your commands.

Caspio Bridge is a handy way to set up a simple single-table database. It's most useful for putting large amounts of data online for public viewing, especially if the data doesn't have too many complicated relations. But expect to invest a lot more time -- and money -- if you're creating a complex, multi-table relational database.

Coghead

Coghead is a decent application for putting simple tables of information on the Web, especially if you want to share information with a private group and not the general public. However, it's certainly not the easiest of the group, either to learn or to use.

The application's drag and drop interface wasn't intuitive enough (at least for me) to begin using it without reading the documentation or watching the Webcasts. However, the on-site tutorials are quite good, and by investing a little time following along I was able to create some simple tables fairly quickly.

Creating a Coghead database involves creating a "collection" for each table of data. Each collection automatically generates a form for data entry, which is where I could define my data structure. By dragging and dropping widgets, I could then add elements like text boxes, drop-down selection lists, radio buttons and "action buttons" to my data-entry form.

Going beyond simple tables to define robust data relationships required a more significant investment of time and concentration. (It didn't help that one of the tutorials I printed out must have applied to an older version -- for example, I kept looking for the indicated "i" icon to customize my widget, and it never appeared. I eventually found other instructions on the Web site explaining that I needed to look elsewhere.)

The ease of dragging and dropping a couple of widgets for a simple, one-table data entry form gave way to more complex, multistep instructions for tasks that should have been relatively simple -- for example, which collection my widget would reference. In addition, it wasn't immediately clear to me how to create a widget where users can select multiple options from a drop-down list during data entry (in Zoho, the choice is a rather obvious "multiselect"). Coghead support advised me to use a widget that added "grids," but that seemed to require deciding on a maximum number of selections based on how many grids I chose.

As a result, this was the only service among the four where I felt the need to print out the user guide and follow along as I did even fairly simple tasks.

Designing a form's look and feel, though, is one area where Coghead shines. It was fairly simple to create various sections on a form, change the sizes of my widgets and decide how many columns to display. The application also offers a great number of options for adding filters and sorting when using a widget.

The experience of using forms for data entry could be better. Changing from editing to using an application first required unchecking an "author" box, but then once the form appeared, I couldn't simply type into it to add a record -- I also needed to click the "new" button. That may sound minor, but when you're developing and testing an application, those clicks add up. Also, after I entered and saved one record, it sat there in my entry form until I clicked "new" again, instead of disappearing and allowing an immediate new entry.

To embed a Coghead application in an existing Web site requires a "Coglet." Although free now, the company says each Coglet will cost $20 per month once the beta period is over.

Overall, I see Coghead as a potentially useful service for a technically sophisticated audience looking to get interactive data applications built while not worrying about hosting, security and servers. However, this is not a service that will bring databases to the masses.

Dabble DB

Smallthought Systems Inc. obviously understood a few basics when it developed Dabble DB: first, that databases are not spreadsheets, and second, that if it's too difficult for a user to create multiple tables, then the users might as well just code their own from the outset. In fact -- and I say this as a longtime MySQL fan -- using Dabble can be cooler than coding your own.

Many database applications can import an Excel worksheet or CSV file into a database table. Where Dabble DB shines is in importing multiple tables and then setting up relations among fields in different tables after the import. After less than half an hour (including watching an 8-minute demo video), I had imported my data and automatically set up relationships between what were separate Excel sheets.

I can't imagine the process being simpler. All I had to do was go into one record, select the configuration option for the field I wanted to link, and choose "list of entries." This created many-to-many mapping. (There's another option, "link to entry," if each record only needs to map to a one entry in another table.) That change in one record redesigned my entire database structure and automatically mapped all my existing data.

Cleanup on 250 records was minimal. In fact, Dabble DB did some cleanup for me, since (in a fit of laziness) I'd made some entries single product records in the product spreadsheet, even though they contained multiple products -- for example, one entry read: Ajax13, Google Docs & Spreadsheets, ThinkFree Office Online, Zoho Office Suite. By telling Dabble to split the list by comma, it automatically created four separate entries.

Adding and editing single forms was truly elegant. For example, if you create a "selection" field that has too many choices for check boxes or a drop-down list, Dabble automatically creates an AJAX-based search box. The user just has to start typing in the search term, and matches will start to appear

1 2 Page 1
Page 1 of 2
SD-WAN buyers guide: Key questions to ask vendors (and yourself)