When most people think OpenOffice, they think of word processing or spreadsheets. What many people do not realize is that OpenOffice also includes Base, a database system roughly equivalent to MS Access. Many businesses and individuals use these systems to allow even non-technical people to enter, store, retrieve and organize their data. Using Base, you can follow simple steps to create an easy, user-friendly way for people to store and retrieve information using custom-designed forms and reports.
Getting Base
Base is part of the OpenOffice package, and packages for all platforms can be downloaded here. Linux users likely won’t need the manual download, as the full OpenOffice suite is typically available in the standard repositories of most distros. Ubuntu users, for example, could install with
sudo apt-get install openoffice.org-base
or simply click the link here
Creating Your Database
When you launch Base, you’ll be taken to a “wizard” which can guide you through the first few steps. While many such wizards often get in the way, the wizard for Base is well constructed and can make the initial steps much simpler than going alone, especially if you don’t have much familiarity with database software.
For the purposes of this guide, we’ll be using the wizard to create a database containing sample employee information, then building a form to manipulate that database.
On the next step, you may be asked to register your database with OpenOffice. This is not a requirement, it’s only relevant if you plan to integrate this database with other OO products.
Before proceeding, it’s recommended that you check the box for “Create Tables Using the Table Wizard“, especially if you’re new to database design.
Creating Tables
The previous step should have finished by saving your new database to a file. If you checked the box for the Table wizard, you should now be brought to a screen where you can begin laying out the information that your database will contain.
As stated earlier, we’re creating a sample database containing employee information for a fictional company. The radio buttons and dropdown menus on the wizard screen will allow you to choose from some pre-existing example fields. Here we’ve chosen a small subset of the possible employee-related fields offered by Base.
Database design is far too large a subject to cover here in detail, so we’ll just mention a few basics and leave some links at the bottom of this page for more detail. For starters, each table should represent only one idea – ie the Employee table should not contain shipping information. If you find yourself putting anything into a table that does not match with the title of that table (like Employee or Billing), it probably belongs in a different table.
Secondly, it’s important for tables to have a primary key. In short, this means that tables should generally have a column where each row in that column is unique on that table. That may sound confusing, but it typically just means that you need a column like EmployeeID, and each entry has a different, unique number. There’s (probably) no chance of two employees having the same ID number, so it makes a perfect primary key.
On the second page of the Table Design wizard, you specify what type of data will go into that field. A number like EmployeeID makes sense as an integer, and text fields like first and last name should be text (CHAR or VARCHAR). If you chose the fields from the dropdown menus in the wizard, these types should be automatically applied to the right fields.
If it’s essential that a record contain a certain field (in our example, and employee MUST have an ID number) make sure to set Entry Required to True for that field, as in the screenshot above. This can make sure that all new entries, regardless of who did it, must follow the rules of your database.
On the next page, you’ll be asked to set a primary key. As mentioned above, it’s generally a good idea to have one column in your table set as the primary key. Remember that for it to work as the primary key, each field in that column must be unique to that table. If you’re following the examples, you’d use EmployeeID as the key, since you can reliably assume that every user will have a different one.
Once you’ve named your table, you’re done setting up your database and tables. Before continuing, you have the option of remaining in the wizard to create your first form, which is what we’ll be doing for this guide.
Creating Forms
Any database can store information, but tools like Base and Access go a step further and allow you to create the interface for viewing and manipulating your data. If you’ve continued with the wizard, you are now at a place where you can build those forms.
A form in Base terms is somewhat like a web page – it’s a visual place to view, enter, and manage the information in your tables. Your form can be laid out however you choose and display only the information you select. This example builds a form from the ID, Name, and Title fields.
Most of the Form wizard options are not critical and can be safely ignored, however make sure to check the layout section and Step 6 in the wizard to decide if you want the form to be read-only.
Conclusion
Now that you know how to create a database, create and edit tables, and the basics of forms, you’re in a position to use Base as a foundation for some amazingly useful applications. Software like Base is in use by countless organizations, large and small, because it allows you to gain many of the benefits of a custom-written database application without having to actually custom write a database application. If you’ve had experiences with Base, or similar software, let us know in the comments.
Our latest tutorials delivered straight to your inbox
