If you've spent much time in a business environment, chances are you can build a spreadsheet, run simple cost analyses, and expertly manage unwieldy contact lists. It's also likely that some of these tasks take more time than you'd like. But you keep working this way because creating a database where all your information is in one place and is linked according to relationship just seems like too much work, and might even be over your head.
That's what I thought, too. But then I challenged myself to try it anyway using OpenOffice.org's Base. It was a lot easier than I thought it would bealthough it did take some work at the front end to plan my database. So the next few articles in this series will walk you through the steps of building a functional database that can improve your business's productivity and increase efficiency. I'll also show you how to tackle some of the trickier elements of OpenOffice.org Base that may intimidate you on the first look.
Because tables make up the bulk of every database, that's where we'll start. Lets set up the scenario, and then well get into how to create the database.
> The Scenario
Lets suppose you're the IT director for a company with several hundred employees. Hence, you have a lot of hardware to manage. Therefore, youre going to build an inventory database to help you manage and track that hardware, including its location, current condition, purchase date, user status, cost to upgrade, and so on.
> The Planning Phase
I know; it sounds a little daunting. But even a little good planning can make a big difference. In fact, a well-planned database can almost manage itself once its built.
As you probably know, databases store data in tables. For example, your e-mail address book is just a table of the address book database. Each address is a data record, presented as a row in that table. The data records consist of data fields, for example, the first and the last name fields and the e-mail field. (See Figure 1.)
Its helpful to write out exactly what data you want each table to contain and then to look for relationships between each table. Here are three tricks to remember:
- Keep in mind that each table in a database should include information about a specific topic; however, if common information exists between tables, you can link a field in one table to the same field in another table in the database that includes the same information. Linking like fields can give the database more functionality and make it more useful when digging in the database for information.
- You can also list tables if you will be entering the same information in several fields. List tables contain two fields: the information (yes/no; true/false; discount/no discount, etc.) and the ID for that information.
- If your database stores multiple categories of information, for example, product type, you can create one table, then copy it to make the rest.
For our scenario, lets make the following tables:
- Status table, with fields for the Inventory ID, User, Purchase Date, Condition (New, Fair, Poor) and Cost to Upgrade
- Inventory Details table, with fields for the Serial Number, Inventory Type (Desktop, Laptop, Printer, Scanner, Fax) and Manufacturer
- User table, with fields for the User's Name, Employee ID Number, Department (Marketing, Sales, Accounting, IT, Product Development, President's Office) and Phone Extension
- Tables for each type of inventory, with fields for Serial Number, Manufacturer, Condition, User, Purchase Date and Cost to Upgrade
- Manufacturer table, with fields for Name, Web Site and Discount Available (Yes, No)
- List tables for Discount Available, Department and Condition fields
After planning each table, take a second look at the fields that will go in each table and determine the type of field they will be. This will make creating the table easier because you will have already decided the type of each field, e.g., numerical, include decimal points, text and so on.
> Creating the Database
With that finished, it's time to actually build this thing. If you listed all your tables, fields and field types on paper or in a text document before creating your database, this step will be a breeze, which is great, because we have a lot of tables to create!
To start, open the OpenOffice.org Base application by double clicking the icon on your desktop or by selecting File>New>Database from within Writer or Calc or Impress. Both ways will bring up the Database Wizard. Our scenario is pretty specific with unique table fields you won't find in the Wizard, so lets create our own database and skip the Wizard. So click option 2 (Save and Proceed) on the left part of the dialog.
Youll still want to register the database with OpenOffice.org so you can access the information in the database with other OpenOffice products, such as Writer and Calc, so select the option to have your new database registered for you. Also, mark the box to open the database for editing and click Finish. Give your new database a name and save it in your desired folder. Youre now in the main screen Base.
> Creating a Table
As mentioned earlier, tables are the bulk of all databases, so you should carefully plan each field of every table in your database before even starting to create a database.
To create your tables, select Tables from the Database column at the left of the screen. Choose the option to Create Table in Design View and it will bring up an empty table in which you will enter the field names and types.
First, enter the name of each field in your first table under the Field Name column just using your down arrow to move to the next field name entry box. Then move to the Field Type column, click the drop-down arrow and choose the field type for that field. In the bottom third of the screen, you'll find options for setting the field's properties. (See What Exactly Are Field Types and Properties, Anyway?). If you want, tab over to the Description field to provide details about a specified entry. (See Figure 2.) These descriptions will appear as a tip on the column headings in the table view.
After entering all the fields and setting the properties for each of those fields, you must give the table a Primary key. The Primary key identifies each record in the table. So, for example, if a person you have listed in your User table shares a desk with someone else and thus has the same phone extension, the two separate entries will never be mistaken for each other because they are identified by a specific key.
To set a Primary Key, highlight the record you want to identify as the Primary Key, and then click the green arrow to the right of the selected entry. Right-click the field and select Primary Key.
Once you've set a Primary Key, you can save the table. To do so, simply click the Save button on the menu bar. Enter a name for your table, then click OK. In Base, saving is imperative. If you don't save each table, both upon initial creation and later edits before closing the Table Design view, your table or all your edits will be lost! You should also save the database every time you add a new table, form, report or other object. The good thing is, if youve made any edits since the last Save command, it will prompt you to save the table again.
Close the Table Design View window and create each table in your database using the above procedure.
> Creating a Table by Copying an Existing Table
Our scenario works best when a table is created for each category of hardware we're tracking. This way, we can see details for the same fields for each category of hardware: laptops, desktops, printers, faxes and scanners. To simplify the creation of all these tables, you can copy the first such table. So, let's copy the Laptops table and make one for each of the remaining categories. First, select Tables from the column at the left of the screen. Right-click the Laptops table and select Copy. Move the mouse to the bottom of the table list and right-click. Then select Paste. (You can also just drag and drop your table from the Tables list to the Table icon in the left Database column.)
You'll see the Copy Table window. (See Figure 3.) Change the name to Desktops, then click Next. Click the double arrows to move all the fields to the column on the right, and then click Next. The next screen allows you to change the field properties, but dont change anything because each of our category tables contains the same type of data. Click Create. Do this for each of the remaining hardware categories.
> Creating a Table for the List Box
Later, youll use Forms to enter data in your database tables. If you create a List Box table now, youll be able to click a box on a form and select the data for that field from a list of predetermined values. For example, each of our category tables, for example, laptops and desktops, has a field for Condition. If we create a Condition List Box Table, we can later select three available options from our form: New, Fair, Poor.
> Here's how it's done:
Create a table as explained above, but use only two fields. The first field will contain the information you want to appear as data on the table (New, Fair or Poor). The second field will be the ID associated with that information. So, let's call the first field Status and the Second field ConditionID. (See Figure 4.) The ConditionID field should be set as the Primary key and the AutoValue set to Yes. (See What Exactly Are Field Types and Properties Anyway?)
Save the table and name it Condition.
> Entering Data in a List Box Table
A list box table differs from regular tables in a couple of ways: it always contains only 2 fields and it is populated by entering the data directly in the table rather than in a form.
So, let's finish our Condition table and make things a little easier for ourselves when were entering our data. First, open the Condition table by right-clicking and selecting Open. Enter New in the first row. Tab down to the second row and enter Fair. Tab down to the third row and enter Poor. Save and close the Table window.
The IDs in the ConditionID field will appear automatically as you enter the condition status in each row. (See Figure 5.)
> Creating Relationships Between Tables
Now that all of our tables are created, named and saved, the final step is to define any relationships that exist between tables. In our scenario, I did a lot of planning. This included looking at information that would be common between tables. I found several relationships. One such relationship exists between the Inventory ID field in the Status table and the Serial Number field in the Inventory Details table. If I link the Serial Number field to the Inventory ID field, I can quickly access additional information about a product (such as the type of hardware it is or who the manufacturer is) even though that information is not in the Status table.
So, let's link these two fields together: Start from the main Base window and select Tables from the column on the left. Now click Tools | Relationships. Select Insert | Add Tables. A window will open displaying all the tables you've created. Select each table you'd like to add and click Add. When all tables have been added, click Close.
Now you can see small windows representing each table. The fields in each table will be listed. Now, click and drag the Serial Number field in the Inventory Details to the Inventory ID field in the Status table. (See Figure 6.) A line will appear between the two windows showing the relationship. (See Figure 7.) In this case, the relationship is one to one. In other cases, you may have a one to many relationship.
Create as many relationships as exist between the tables in your database, then click Save. Close the relationships window to return to the main screen.
> Is the Table Finally Set?
Depending on the type of database you are building, creating tables can take anywhere from an hour to a few days. You might even wonder if you'll ever finish. Eventually you will. And although you haven't entered any data, you're more than halfway through the process of building a database. If you plan well, your tables will be specific, informational and easily accessible through a variety of relationships. The real power of Base is in the tables. So, take a break and gear up for the next stepfilling those tables with data. We'll tackle that in the next article.
> What Exactly Are Field Types and Properties Anyway?
Setting a field type allows you to make the most of the data in each field. In our scenario, for example, the Status table includes fields for the User ID (a numeric value), the Purchase Date (a date value), the Condition (a text value), and the Cost to Upgrade (a numeric value in the form of currency with a decimal point).
Selecting the proper corresponding field type allows you to set other important values for the field.
For example, in the Status table, I created a Cost to Upgrade field. I then tabbed over to the Field Type column and clicked the drop-down arrow for a list like the one shown in (See Figure 8.).
I selected Decimal from the list, at which point, the options in the Field Properties pane changed. I selected the option to require an entry in this field. (After all, what's the point of running a cost analysis later if the costs have never been entered?) I set a length for the field data. The length I chose was 50, though I would never use that many characters for this field. I then indicated how many decimal spaces I wanted and clicked the box to the right of the Format example field. This allowed me to specify that this field would contain currency data. (See Figure 9.)
I also specified how I wanted the figure to look and unchecked the option to display negative numbers in red. When I was finished, I clicked OK, saved the table, and closed the Design view.