November 24, 2009

Create a Database and Data Entry Form in Base

For many people the mere thought of having to create or use a database sends shivers down the spines of many users. But it doesn't have to. Creating a database and adding data to that database is actually a simple task in Base.

But why do you need databases? There are many reasons for this. On the user end you can keep track of collections, keep track of employee information, create sales reports, the possibilities are limitless. But basically the database can be thought of as much more powerful spreadsheet than can not only handle larger amounts of data, but can also work with that data in many more ways. Also, when keeping your records in a database, you make it possible for other applications to access that data.

Many users also assume creating a database in Linux can only be done using the command line. That is also not true. With Base, a database can be created using  a very user-friendly wizard. Even data entry is made simple with the help of form creation wizards. That's correct, you can even use Base to create a simple form that makes the entry of data simple.

In this tutorial you will learn how to create a database and then create a form so you can then enter data into your database. All of this will be done within Base in version 3.1.1.

Install Base

Most likely you will not have Base installed on your machine. Although most every Linux distribution now ships with installed, they generally only include the word processor, spreadsheet, and presentation packages. In order to install the database application you will need to follow these steps:

  1. Open up your Add/Remove Software tool (if you are using Ubuntu 9.10 you can use the Ubuntu Software Center).
  2. Search for "openoffice" (no qutoes).
  3. Select and for installation.
  4. Click Apply to install.

Once installed you will find the Base application in your Office menu listed as Base. 

Creating the Database

Step 1 of the Database Wizard

Step 1: Before you enter data you must obviously create the database that will hold the data. With Base there is a very simple wizard that will walk you through this process.  When you fire up Base the Database Wizard will start up (see Figure 1). In this first window you will need to make sure "Create New Database" is selected and then click the Next button.

From this same window you can also open up an existing database or connect to an existing database that was created in a different application. For new users, most likely, you are going to be creating a new database or opening an existing database. 

Database Wizard Step 2Step 2:  In the next window (see Figure 2) here are three options you will want to select:

  • Yes, register the database for me (this will make your database available for other OpenOffice applications).
  • Open database for editing (this will automatically open the database up so you can add data when you have completed all the wizards.
  • Create tables using table wizard (this will open up an user-friendly wizard to create all the necessary tables for your database).

Once you have these options select, click the Finish button. When you click the Finish button another window will open requiring you to give your database a name and a location for saving. Make sure you remember the name and directory for this, for easier access later.

Tables Wizard

Tables Wizard makes creating tables easyWhen you finally save the file the Tables Wizard will open. In this window you are going to create all of the tables that will be included in your database. The easiest method for creating tables is to use the Wizard. Start the Table Wizard by clicking the "Use Wizard to Create Tables" entry in the main Tables Wizard window. When you click that entry the Tables Wizard will start up (see Figure 3). 

The steps to create your tables are simple:

Step 1: Select a category for these tables (Personal or Business) which will define the types of tables and fields available. For our sample we will create a Business category using the Employees entry under Sample Tables drop down. 

Step 2: Select the fields you want to include in your tables. To add a field to your table select the field and then click the > button to add. Go through the entire list of fields and make sure you include all of the employee data you want to include in your database.

Step 3: Move the various fields up or down in the right pane by selecting the field and clicking either the up arrow or the down arrow. You will want these field in the proper order (such as First Name, Last Name, Phone, Address, City, State, etc). 

Step 4: Click the Next button.

Set specifications for each field in your tableStep 5: In the next window (see Figure 4) you need to to each field you have added a Type, Length, and whether an entry is required or not.  When you click the drop down for the Type you will notice there are quite a few types to choose from. You will want to make sure you choose the type correctly or, when you go to enter data for that field, you will have trouble. Most often you will be using Text [VARCHAR] or Number [NUMERIC]. You can also add Boolean fields which are yes/no fields. If you see a type you do not understand, your best bet is to Google that type.

You will need to go through each of your fields and make sure all field information is correct. Once you have done that, click the Next button to continue on.

Define primary keyStep 6: This step is very important in that it creates the Primary Key for each entry. A primary key is the key that will uniquely identify each entry also making it easier to link information within your table. You HAVE to set a primary key and your best bet (unless you have specific needs) is to select the following: 

  • Create a primary key
  • Automatically add a primary key
  • Auto value

The three options above will make for the most successful database creation. If you are more familiar with database primary keys, you can configure yours to better match your needs, otherwise, stick with the above formula.

Final step

Step 6: The final step (see Figure 6) is to name your table and select "Create a form based on this table". By selecting "Create a form..." you will then open up yet another Wizard that will walk you through the creation of a form. A form is crucial to this process because it allows you to create a user-friendly form that will be used to enter data into your spreadsheet. 

From this same final window you could also modify the table design, if you feel like you have made a mistake in your tables. 

Creating Your Form

The next step in the process is to create the form which will allow you to easily enter your data. When you click the Finish button in  the last window of the Database Wizard you will be taken to the Form Wizard. There are eight steps to the Form Wizard:

Step 1: Field selection. Select which fields will be added to your form.

Step 2: Setup a subform (if needed).

Step 3: Add subform fields (if needed).

Step 4: Get joined fields.

Step 5: Arrange controls.

Step 6: Set data entry.

Step 7: Apply styles.

Step 8: Set name.

When you first read the above steps you might think this process is difficult. It's not. Let me further explain the steps.

Step 1: You have already created the fields for your table. More than likely you will add them all for your form. You don't HAVE to add them all however. Once you add all the fields you want for your form, click Next.

Step 2-4: Subforms are forms that are inserted into primary forms. You can use subforms to show data from tables or queries. These are a bit more advanced and will require you to either create a relationship or manually select field within the subform. For the purpose of this tutorial, we will skip subform creation.

Arrange controlsStep 5: In this step (see Figure 7) you will configure how you want your form to be laid out. This is where you decide how to arrange your field labels and data entry areas. Your choices of layouts are: Columnar with labels to the left, Columnar with labels on top, As a data sheet, or In blocks with labels above.

Step 6: This allows you to dictate how much data will be shown on your form. You can either set this to show all data (will be shown one page at a time) or the form will be used only to enter new data. If you choose the later, no data will be displayed. In this same step you can configure your form so that data can not be modified, deleted, or added.

Step 7: In this step you choose the color scheme of your form. As well as the look of your forms border.

Step 8: In this final step you name your form and choose if you want to immediately start entering data or not. When you have finished this last step click the Finish button.

Time to enter dataIf you opted to immediately start entering data your form will open (see Figure 8).  You are now ready to begin entering data into your database. 

From this form you can also (if you've allowed it during setup) view, search, edit, and/or delete, print, export to PDF, sort, filter, form-based filter, and more to the data you have added. 

Final Thoughts

You probably never thought creating a database could be so user-friendly. With Base, database work does not have to be relegated only to the database admins and power-users. 


Click Here!