Establishing Relationships

From NeoWiki

Part of the power of Base is that it is a relational database, allowing you to relate different tables of data. In order to make use of this power, we need to establish the relationships between the various tables.

Types of Relationships

Relationships can be one-to-one or one-to-many. In a one-to-one relationship, one record in one table relates to one and only one record in another table. In a one-to-many relationship, one record in one table relates to more than one record in another table. In our database, the relationship between Field_Trips and Sightings is one to many; on any given field trip, the birder would (hopefully!) see many birds. Note that at least one of the fields you are relating must be a primary key field. One-to-one relationships are most frequently used when some data (e.g. salary) is confidential and access to it must be more limited than access to other data (e.g. Name and Address). For our birding database we will not need to use One-to-one relationships.

Creating Relationships When One Field is a Primary Key

1. With the database open, go to the Tools menu and choose Relationships
2. In the floating window, click on the Field_Trips and then click the Add button. A small window named Field_Trips appears in the larger Relation Design window.
3. repeat with Sightings
4. close the floating window
5. click on the the FieldTrip_ID field in the Field_Trips sub-window; drag from there to the Sightings_FieldTrip_ID field in the Sightings sub-window. A line appears connecting these two fields, with a 1 the Field_Trips end and an n next to the Sightings window. This identifies a one-to-many relationship.
6. click on the disk drive symbol in the toolbar to save the changes you have made, and close the window.

Creating Relationships When Neither Field is a Primary Key

In Base, whenever you create a relationship between tables, at least one of the fields being used must be unique. That is, in at least one of the tables, no two records can have identical entries in the field being used to create the relationship. This is automatically the case if one of the fields is a Primary Key. But if you want to relate tables using something other than the primary key, you must first induce a unique constraint on one of the fields you want to use.

In the case of our birding database, data entry will be much easier if we do this with two of our relationships, specifically for the relationships between:

The AOU_Birdlist and Sightings tables (EnglishName relates to Bird_Name)

The Locations and Field_Trips tables (Location_Name relates to Field_Trip_Location)

The example below follows the procedure for relating the AOU_Birdlist and Sightings tables. The same procedure will need to be used to link the Locations and Field_Trips tables.

Creating a Unique Index

While a Unique Index is not absolutely necessary for this process, it will help insure that we don't accidently enter duplicate information into the fields in question.

1. In the main database window, click on the Tables icon in the left hand column
2. Right-click or control-click on the AOU_Birdlist icon and choose Edit
3. Click in the cell with the EnglishName field name
4. Click on the Index Design button on the toolbar. (It looks like a sheet of paper with a blue drafting triangle laying ontop of it.) The Indexes window opens.
5. Click the New Index icon (a sheet of paper with a yellow sunburst.)
6. New Index appears in the box on the left. Type an appropriate name (e.g. EnglishName)
7. Check the unique box in the Index Details section
8. Set the Index Field to EnglishName and the order to Ascending
9. Click the Save Index icon (a sheet of paper with a "floppy" disk on it).
10. Click Close
11. Close the Table window by clicking on the red button at the top left of the window.

Creating a Unique Constraint

1. Make a note of the exact table and field names involved. In this case AOU_Birdlist and EnglishName

   Note: There must not be any spaces in the field name. While Base will not allow you to create field names with spaces in them when using the wizard or Design view, Base will retain spaces in field names if you import a spreadsheet into Base as a table. When importing the AOU Birdlist, the field name was English Name (with a space). I had to edit the table and delete the space before the SQL command would work.

2. With the database open in the main window (the one with Tables, Queries, Forms, and Reports in the left hand column), go to the Tools menu and choose SQL....
3. Enter the following statement into the top portion of the Execute SQL Statement window:

   alter table "AOU_Birdlist" add constraint "uq_EngName" unique ("EnglishName" )

   uq_EngName is the name of the constraint that is created by this command.

4. Click Execute

   Note: The Status section of the window will tell you if the command was succesfully executed or if there was a problem.

5. Close the Execute SQL Statement window

Creating the Relationship

1. With the database open, go to the Tools menu and choose Relationships
2. Click on the icon that looks like a block of blue cells with a green plus in the corner.
3. In the Add Tables window that appears, click on AOU_Birdlist

   Note: If you have not created any relationships yet, the Add Tables window will open automatically.

4. If the Sightings Table does not appear in the relationship "map," add it as well.
5. close the floating window
6. click on the the EnglishName field in the AOU_Birdlist sub-window; drag from there to the Bird_Name field in the Sightings sub-window. A line appears connecting these two fields.
7. click on the disk drive symbol in the toolbar to save the changes you have made, and close the window.

Related External Links

OOo forum thread on unique constraints