Understanding Calc Database Design

Your Calc databases will not look much different from your other spreadsheet files. In fact, Calc spreadsheet databases are called flat file databases because they consist of a single, large table of data. Relational databases like StarOffice Base allow you to place the data in smaller related tables (hence the name relational), which actually allows for greater manipulation of the data.

An important prerequisite for designing your Calc databases is to understand the terminology that is used to refer to the different parts of a database:

• Record— Each row of information is called a record and will hold the information pertaining to a particular person, place, or thing. For instance, if you are using the database to keep track of employees, each row will be a record for one particular employee.

• Field— The columns in a worksheet are referred to as fields. A field consists of one particular piece of information such as an employee's home phone number or the department in which they work. Each column in the database worksheet will have a heading called a field name. The field name identifies the particular information that is to be placed in the field such as phone number, department, or salary.

• Primary key— The key field, which is often referred to as the primary key, uniquely identifies a record and is usually placed in the very first column of the database. An example of a key field would be an employee number or your Social Security number. It must consist of some type of designation that is unique for each record in the database.

Figure 8.1 provides a look at a simple database table and its various parts.

To Define Database Areas

When you create your database, it will consist of information in rows and columns just like any other spreadsheet. However, to actually query your database (ask the database questions and filter the data according to the query asked), you will also have to include an area on the sheet where you specify the question you want to ask the database (for example, you may want to filter the database so that you can view all customers in a particular ZIP code) and an area where Calc can put the answer to your question.

It's important that you understand the three different areas that you will need to create if you want to apply advanced filters (or queries) to the database and actually extract the results of the advanced filters. These areas are:

• Data area— This is the database itself, and it consists of all the records that you enter on the sheet. The data area includes the field headings (column headings) that you create for the database table.

• Criteria range— This is the area on the sheet where you enter the criteria that are to be used by an advanced filter. This is basically a query input area. It includes the same headings that are used as field headings in the data area table.

• Extract range— This is the area on the sheet where Calc will place the records that are extracted when you query the database using the criteria range.

Figure 8.2 provides a look at a Calc database and the different database areas. While it is convenient to place the criteria range on the same sheet as the database, you can actually place the extract range on another sheet in the current database.

To Plan a Database

Now that you understand the various parts of a Calc database table (fields and records), you are ready to sit down and plan your database. Here are some things to think about as you plan your database:

• Sit down and plan out the fields that you will use in your database table. While you may feel that you need an exhaustive list of details related to each record (for example, you want not only the client's phone number and their recent purchases, but also the number of children they have, the type of dog they own, etc.), you may want to list all the possible fields and then cut the list down to fields that will actually provide the core information that you need. You also only really need to include the fields that will be used when you set criteria to filter the data.

• While Calc is very forgiving and makes it easy for you to rearrange columns of information on the sheet, try to set up the field columns so that they provide you with records that list their data in a logical fashion. Also keep in mind that you will use fields to sort and filter data in the sheet. If you are constantly sorting by last name and then first name, you will want to place the last name field to the left of the first name field, since sorting by multiple fields progresses from left to right in the table.

• Create each database on its own sheet. Make sure you leave some room for growth. For instance, you may want to add new records to the database. You will also need room on the sheet for your criteria and extract ranges if you plan on performing complex filters. And, you can also include a chart on the sheet if you choose to chart any information in the database table.

• Use some type of formatting (even just bold will do) to differentiate the field names (the first row of the table) from the data records in the database.

• Make sure that you set up the different database areas (data area, criteria range, and extract range) so that you have plenty of room to add additional records to the data area.

• When you enter the data, it is imperative that you do not leave completely blank records or empty rows between the records in the database table. This will throw off your sorting and filtering of the database area.