Defining relationships

Getting Started with OpenOffice.org 2.x

Chapter 10: Getting Started with Base

< Previous Section

Next Section >

• Planning a database
• Creating a new database
• Creating database tables
• Creating a View
• Defining relationships
• Creating a database form
• Using data sources in OOo
• Creating queries and reports

< Previous Page

Next Page >

Now that the tables have been created, what are the relationships between our tables? This is the time to define them based upon the questions we asked and answered in the beginning.

When on vacation, we want to enter all of our expenses all at one time each day. Most of these expenses are in the Vacations table, but the fuel we buy is not. So, we will relate these two tables using the Date fields. Since the Fuel table may have more than one entry per date, this relationship between the Vacations and Fuel tables is one to many. (It is designated 1:n.)

The Vacations tables also contains several fields for the type of payment used. For each field listing the payment type, there is only one entry from the Payment Type table. This is a one to one relationship: one field in one table to one entry from the other table. (It is designated 1:1.) Other tables also contain fields for the type of payment. The relationship between these fields of those tables and the Payment Type table are also 1:1.

Since the Payment Type table only provides a static list, we will not be defining a relationship between the Payment Type table and the fields of the other tables which use the entries of the Payment Type table. That will be done when the forms are created.

The Fuel and Maintenance tables do not really have a relationship even though they share similar fields: Date, and Odometer. Unless a person is in a habit of regularly getting fuel and having their vehicle serviced, the entries in these tables do not share anything in common.

As you create your own databases, you need to also determine where tables are related and how.

We begin defining relationships by Tools > Relationships. The Automobile - OpenOffice.org Base: Relation design window opens (Figure 13). The icons we will use are Add Tables and New Relation.

File:RelationDesign.png

Figure 13: Relation design window.

Click the Add Tables icon. The Add Tables window opens.

1. There are two ways to add a table to the Relation design window.
• Double-click the name of the table. In our case, do this for both Vacations and Fuel.
• Or, click the name of the table and then click Add.
2. Click Close when you have added the tables you want (Figure 14).

File:AddedTable.png

Figure 14: Added table lists.

Defining the relationship between the Vacations and Fuel tables

Two ways exist to do this:

• Click and drag the Date field in the Fuel table to the Date field in the Vacations table. When you release the left mouse button, a connecting line forms between the two date fields (Figure 15).



Figure 15: Designation for a 1:n relationship.

• Or, click the New Relation icon. This opens the Relations window (Figure 16). Our two tables are listed in the Tables involved section.
1. In the Fields involved section, click the dropdown list under the Fuel label.

File:SetRelation.png

Figure 16: Setting the relationship between tables.

2. Select Date from the Fuel table list.
3. Click in the cell to the right of this dropdown list. This opens a dropdown list for the Vacations table.
4. Select Date from the Vacations table list. It should now look like Figure 17.
5. Click OK.

File:TablesInvolved.png

Figure 17: Selected fields in a relationship.

Modifying the Update options and Delete options section of the Relation window

1. Right-click the line connecting the Date fields in the two table lists to open a context menu.
2. Select Edit to open the Relation window (Figure 18).
3. Select Update cascade.
4. Select Delete cascade.

File:BaseUpdateOptions.png

Figure 18: Update options and Delete options section.

While these options are not absolutely necessary, they do help. Having these options selected permits you to update a table that has a relationship defined with another table. It also permits you to delete a field from the table.

Top of page