When databases became available for the personal computer in the mid-1980s, they quickly gained a mystique as the ultimate productivity applications. Despite their widespread use, in some ways they have never lost that mystique -- so much so that many desktop users will stretch the use of spreadsheets to cumbersome lengths rather than consider setting up a database. Fortunately, OpenOffice.org Base makes setting up a database easy, giving you a more efficient way of handling data than a spreadsheet.

Setting up a basic database in Base consists of two parts: configuring the database, and adding at least one table to it.

Choosing and registering the database

To begin customizing a database, select Database from the initial screen or File -> New -> Database from the menu. Either way, the Database Wizard opens.

Your first step is to choose a database with which to work. You have three choices, but what they are is obscured slightly by the wording and order of choices in the wizard:

• Create a new database: Make a new database in Base's native HSQL format
• Open an existing database file: Edit a Base database that is already created.
• Connect to an existing database: Make a new database in one of the other formats supported by Base. Besides formats such as MySQL and Oracle, Base also supports several other formats on the free desktop, including Mozilla, Evolution, and KDE address books. Another option is to choose an OpenOffice.org or MS Excel spreadsheet, which is useful for when you want to explore Base quickly or when you want to convert a growing spreadsheet into a database.

What happens on the next page of the wizard depends on your choice. If you are creating a new database, you are asked if you want to register it so that you can use data from it in other OpenOffice applications (a subject for another article). Since this option has no drawbacks and is convenient, usually there is no reason not to register the new database.

By contrast, if you open an existing database, you move directly to Base, while if you choose another database format, you need to enter the path to it and other information that will allow you to use the database -- anything from a simple password to the porter, server URL, and database driver.

These variations are too numerous and too different from each other to discuss here in any more detail, but if you click the Help button on the second page of the wizard, you will find detailed instructions on how to deal with each type of supported format.

Once the connection to the database is entered, in some cases, such as Evolution address books, you will be unable to continue in the wizard, and will have to edit directly from Base, regardless of your preference -- although why this limitation exists is unclear.

When you have registered a new database or connected to an existing one, you have the option of editing the database. In some cases, you only have the choice of editing in Base, but with new databases and some others, such as spreadsheets, you also have the option of editing tables in another wizard. Whenever possible, you should consider using the wizard, especially if you are unfamiliar with databases.

Setting up tables

Tables are the basic structure of a database. Although they resemble spreadsheets, they are organized somewhat differently. Instead of being organized into cells that can either be part of a larger record or individual containers of data, tables are always organized by rows. Each row is a single record, and each column is a separate field within each record. Since each record is unique, setting up a table consists of adding columns to it.

When you create a new database in the wizard, you may have the option of creating tables in a wizard, depending on the type of database you are using. If you choose the wizard, then you start with selecting the fields (that is, columns) for the database. The wizard provides dozens of default fields, dividing them first into Business and Personal categories, and then into secondary categories, such as Customers, Deliveries, or Addresses.

Each secondary has a list of commonly used fields. To add one of these defaults to your database, all you need to do is select it in the Available Fields pane, and use the arrow keys to move it to the Selected Fields pane.

The first entry in the Selected Fields pane is the first column on the left of the table. You can use the up and down arrows on the right of the Selected Fields pane to change the position of fields; for instance, you might want to ensure that the LastName field in a table listing customer addresses was the first field, because most people will be using it to find a specific record.

You can also use the arrow keys on the left of Selected Fields pane to move a field back to the Available Fields pane. A single arrow button moves the selected record, while a double arrow moves all records.

Pressing the Next button takes you to a page on which you define what sort of information goes into each field. After selecting a field on the left side of the page, you can edit:

• The name of the field: Usually, you won't want to change the name, but you might want to edit the default name. For instance, if you are doing an address book for only the United, you might want to alter the default PostalCode to the more colloquial Zip.
• Type: The format of the data in the field. This is analogous to the number format of a spreadsheet cell, and includes many of the same choices, such as Text, Number, and Date/Time. Another useful type is Yes/No.
• Entry required: Whether the field is compulsory, or can be left blank.
• Length: The number of characters allowed in the field. This characteristic can be restricted to control the size of the database, or as a guide to correctness. For example, in a field for North American phone numbers, you might want to restrict entries to ten characters.

On the next page you set the primary key, or unique identifier for each record. The easiest choice is Auto-value, in which case each record is identified by a number. However, you may prefer to generate a primary key from a single field, or a combination of fields.

Whether using fields is a practical choice depends, of course, on choosing a combination that will be both unique for each field, and meaningful to the database's users. For example, the address field might be unique, but is probably not the handiest way to refer to a record. By contrast, a combination of LastName and FirstName would be handy and reasonably unique, but not as much as using LastName, FirstName, and Address.

Finally, on the wizard's last page, you can give the entire table a unique name, and choose your next step: Adding records into the database, editing the table, or creating a form (another topic for another day).

If the table wizard is not available because of your database format, you can create tables from within Base by selecting Create Table in Design View. Here, you can work within an actual table-like format, which may be easier for you to conceptualize what you are doing. The orientation, though, is different in the wizard, in that the top field will become the first field on the left of the table.

Once you enter the field name, you can choose its format type from a drop-down list in the second column. Below the table, the current field's properties are listed for editing. Unlike in the wizard, editing from design view gives you the option of adding a default value and a format example. In the table itself, you can add a description, a sort of mouse-over help similar to that you can add using the Validity tool in a spreadsheet.

Another difference between the design view and the wizard is that, in the design view, you only create the primary key when you go to save the table -- when it appears as the first field in the table.

Next Steps

At this point, you have a basic database with one or more tables. Should you want to add another table or to edit you can choose whether to work in design view or the tables wizard from the Tasks pane in the top middle of Base.

Alternatively, you can select a table from the Tables pane in the lower middle of the window, and select Open from the context menu to edit it in design view.

However, creating a database and tables are only the beginning of working with Base. You can easily copy and paste information from an open Base table to other OpenOffice.org applications, but to gain the full efficiency of working with databases, you should also know how to set up forms, queries, and reports. In upcoming articles, I'll cover all three, explaining what they are and how to use them.