Even with all of the high quality software available, sometimes you just have to break down and write a custom application, and usually you don't have a lot of time to devote to it. This is the situation I found myself in when my wife started a new business and needed a simple means of tracking sales leads. To her credit, she realized that she couldn't simply use a spreadsheet and needed a database application. So I decided that I'd see just how involved it would be to write an application with Open Office's database program, known as Base.

When I started, I'd never used Base before and even had to install the program on both my machine and my wife's before I could get started. I had used Microsoft Access many years ago and I do have a lot of experience with PostgreSQL and MySQL, so I figured I'd be able to pick up Base in short order and be able to build a customized application for my wife. Now there have been many articles written about Base. (See References, below) This article isn't a How-to, or a product review. Instead this article is more of a crash course that covers the point in time right after initial installation to the point where you have a working application. I came in with zero experience with Base and not a lot of time to climb a steep learning curve. Two evenings later, I had a basic application that my wife could at least get started with. As I learn more, I expect to improve the program, but in the mean time, she's able to get some use out of it.

So let's get started.

As with any database application, and even ones as simple as this one, it helps to draw a diagram of the objects that you intend to manage and the interrelationships between them. Then I like to simply list the fields, or attributes, that each object requires. Once this is done, you can start creating tables in Base.

Once I started the Base program and decided to create a new database or connect to an existing database, I was presented with the main screen, as shown in Figure 1. As you can see, I was given the choice to use a wizard to create my tables, or I could use the “design view” and create them manually. I tend to hate wizards, but I did take a quick look at the wizard. When I did, I was given the chance to select from quite a variety of objects such as “products,” “customers,” or “assets.” Once an object was selected, I was presented with a selection of fields relevant to that object. I can see where this would be quite a quick-starter for many people. I decided to create most of my tables manually. You can see the definition of one of the tables in Figure 2. This process is fairly straightforward. You simply list the names of the fields, or attributes, that your table needs and you assign data types to them. The only problem I had here was that I wasn't sure what the difference was between a “real,” a “float,” and a “double.” The option of selecting “other” as a data type was also... unexpected. Then, at the bottom of the window, I was able to tweak each file type by setting the length, default value, etc. This part of the process was pretty straightforward and worked exactly as I expected.

Figure 1

Figure 2

For an application as simple as the one I'm building, using the relationships tool to configure referential integrity is a bit of overkill, but it has an added benefit, in that it allows you to see at a glance what the data model looks like, and how the tables are interrelated. See Figure 3. Here you can see that my wife wants to track sales contacts, as well as which organizations they belong to. She also has several shows that she participates in and needs to track them, as well as a point of contact for each. Finally, she wants to be able to schedule events such as phone calls, or registrations, etc., for each organization, contact, and show.

Figure 3

You'll notice that I have a “Relations” table in my model. This will eventually be used to store “many to many” relationships. It turns out that a given contact might be a member of more than one organization, and my wife wants to track that fact. However, creating the forms, which we discuss next, to create these relationships has proven to be more difficult than expected.

So, once I created the database tables, and a reasonable diagram of the tables, fields, and relationships, it was time to start creating application forms. Once again, I was offered the choice of creating the form manually, or using a wizard. Initially, I tried the wizard, at least until I was comfortable with the process. The wizard asked me to select a table to draw from, then it allowed me to select the fields from that table to include in the form. Finally, the wizard created the data-entry form for me. However, I just really didn't like the resulting form and decided to create the form manually.

The process of creating the forms needed for my application proved to be the most difficult part of the entire process. It turns out that Base actually uses the word processing application, Writer, as it's form engine. This was quite unexpected, but in hindsight, I see it as a very elegant solution. Writer is already able to create HTML forms, so why not use it to create database forms? I'll admit that I had a bit of difficulty figuring out how to bind a form to a given table, but once I found the “form” widget, which is next to the cursor in Figure 4, the process was quite intuitive. Then it was simply a matter of dragging text labels and data fields onto the canvas. The form shown in Figure 5 is pretty simple and plain, but I expect to improve it over time. Along the bottom of the screen, you see record navigation icons as well as sorting and filtering icons. Eventually, I'd like to create buttons for these functions, but I'll discuss that later. So, I created a data-entry form for each of the objects that my application needed to track. At this point, my wife could start entering her data.

Figure 4

Figure 5

Entering data is one thing; getting it back out is another thing, and for that we use the Query tool in Base. Here once again, we are offered the convenience of using a wizard to help us with our query, or we can create our query manually. Additionally, we can use the SQL mode and enter our query in raw SQL; finally, something I was familiar with! I have to admit though, that the wizard really wasn't that bad. It allowed me to determine how the data would be sorted, and gave me a very intuitive means of selecting the data, much like the conditional formatting functionality of the Open Office spreadsheet application that I was already used to using. See Figure 6. Still, I decided to try out the manual query generator, which you can see in Figure 7. Both the wizard and manual method are extremely powerful, and quite intuitive. If I had to chose between them, I don't know which way I'd go. But over all, I preferred the SQL method because that is what I'm used to using.

Figure 6

Figure 7

My wife doesn't have extensive reporting requirements, so I only took a cursory glance at the reporting tool. Here we're only offered the option of using a report generating wizard. I actually can't imagine any other way to generate a report, and the wizard seemed to do what I expected it should do.

So after an evening or two of work, what do I have to show? Well, I have a means of storing my wife's business data, and she can enter it into a (reasonably?) attractive data-entry form. She can search and sort her data. The program is easy enough to use that she won't have any difficulty learning how get her work done with it. It's also extensible enough that I can make periodic improvements to it as my skills grow. Think of it as a living document.

On the other hand, what am I still lacking? The application isn't as polished as I'd like it to be; she still has to manually select a form before she can do any data input, or she can input directly into the table in tabular format. It would be nice if the program presented her with an opening menu and hid the underlying guts from her. I had difficulty implementing the many-to-many relationships mentioned earlier. However, I believe that both of these issues can be resolved by using a little bit of programing. Open Office provides a dialect of visual basic that I'll bet could be used to handle the problems I've mentioned as well as adding some polish to the whole application. If there is any interest, I might try to write a primer to Open Office Basic in a future article.

So as you can see, Open Office Base is a quite capable database program with a fairly shallow learning curve. I don't perceive Base to be as mature as the word processing or spreadsheet applications, but that is setting the bar pretty high. The fact that I can link Base to my MySQL or PostgreSQL databases makes it particularly interesting to me as a database front-end, though the linking process isn't as easy as it should be. Overall, Base is an extremely powerful tool and reasonably easy to learn.