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 establish the relationships between the various tables. In our case, not all the tables need to be related. The tables we "termed" covenience tables in the first article of this series, do not need to be related by an official process, as they will be used primarily for combo boxes and/or list boxes. The essential tables do need to be related to each other.

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.

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 the Sightings
4. close the floating window
5. click on the the ID field in the Field Trips sub-window; drag from there to the Field Trips 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 hard 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 in 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 Sightings tables (LocationName relates to 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 Sightings tables.

Creating a Unique Index

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

1. In the main databse 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 English Name 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. English Name)
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

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. While the database open to 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.

1. If the Sightings Table does not appear in the relationship "map," add it as well.
2. close the floating window
3. click on the the EnglishName field in the {{Window|AOU Birdlist} sub-window; drag from there to the BirdName field in the Sightings sub-window. A line appears connecting these two fields.
4. click on the hard 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