Thursday, June 26, 2008

Why Can't I Be An Ordinary Consumer: Bento Review

For many years I was the database expert in our department at Motorola. I fell in love with SQL (Structured Query Language), and although I never had the home budget for my own copy of Oracle, I made do, first with shql which made a valiant effort at making an SQL compatible database out of shell scripts and text files. Of course I later moved everything over to the free Mysql. I still have my writing and submission database running there, with a home-brew perl cgi interface. But as I began actively becoming a publisher, there was new data I had to track, like inventory and bookstore visits and such. When Apple came out with their new consumer-level database, Bento, I wanted to give it a whirl. The advertising seemed to hint at relational abilities, maybe I could move to a turn-key consumer product and not have to spend my vanishing time coding up a new web front-end to yet another mysql database.

Still, I put it off until Mary Ann complained that she really needed a database to keep track of her photo submissions. She's a whiz at spreadsheets and has an absolute phobia about databases after so many of my failed attempts to get her to use them. So last March, I bought her a copy and turned her loose. After all, all the advertisements and reviews said it was easy to use and learn.

Here in late June, she asked for help. Everything she'd tried had failed and will all the photos she was sending out to the magazines and other markets, she had to have a tracking database.

So, a couple of days ago, I downloaded myself the 30-day trial copy and set out to make myself a database to track books I'd left at bookstores on consignment. It took me about four hours, and most of that was because I just couldn't believe Bento's database. It ain't SQL. It's sorta relational, I guess. But it takes a completely different mental model to get good results.

Okay, starting simple. It's dead easy to put together a plain table with a handful of nice data types like text and currency and date and even media. You can easily make spreadsheet-like tables and nice forms with any subset of the data you want. This was particularly nice for Mary Ann's table of photos since it let her put thumbnails of the photo right there on the page so she didn't have to remember just which owl picture was which. (Believe me, she has many owl pictures!) However, there is something I should warn you about. I was thinking the images were converted to thumbnails when you drag the photo file onto the form. No. It stores the whole image file. That's great for some usages, but some of Mary Ann's tiff files are 100+ megabytes. Her Bento database was quickly 3 GB and I had to go back and convert them all to thumbnails manually (with a little help from Automator).

Searching for data in Bento is very simple, using the search box method, or dropping you into an advanced search mode where you can search by multiple fields if you wish. The table view also has Sum and Count modes so you can get summary data of the records selected. It's simple and easily understandable, and nothing at all like composing a SQL query.

It's when you want to connect two or more tables that things get wonky. There are no table joins, or the like. As far as I can see, the only thing you can do is put a little baby table image of the related table into a form for the other. Click on the image to see it full size. In this example, I have a bookstore list, which is actually from my address book, as a "Related Records List" field type. It's like the form does the linking. I'm sure there is actual database table somewhere invisible in the background that handles all the grunt work, but the consumer isn't supposed to see them. Also, you can easily do "many-to-many" linkages through these forms, and the little related record views are fully editable. That makes it easy to Mary Ann to add all the data for a photo submission from just one form view, rather than switching back and forth between forms for different tables.

However, the spreadsheet view is prohibited from viewing the linkage. Take a look at the "Table" view of the same library as the form above. Notice that the "Bookstores" field is grayed out. You can't view it in table form, no matter what you want.
I can actually understand the design choice there. In SQL join, if I had two bookstores connected to two book titles, it would return four records in the table. That might just freak out the consumer-level database user. The problem is my mind has been grown into one database model, and I have trouble accepting this alternate way of doing things.

As a database designer back in the day, you sketched out the tables and joins and keys on paper and built one or more applications around it. With Bento, the database is nothing without the application. It's a valid way of solving the problem, and yes, I'll spend the $50 to buy it before the 30-day trial runs out.

But it still ain't SQL.

No comments: