Establishing Relationships

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 18:05, 17 September 2007 (edit)
Lorinda (Talk | contribs)
(Creating Relationships When Neither Field is a Primary Key - correct table reference)
← Previous diff
Revision as of 21:47, 7 October 2007 (edit) (undo)
Lorinda (Talk | contribs)
(remove convenience table references; correct field names; some fomratting changes)
Next diff →
Line 1: Line 1:
{{TBBA Header}} {{TBBA Header}}
-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" [[Designing the Database "On Paper"#Planning_Convenience_Tables|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 [[Designing the Database "On Paper"#Planning Essential Tables|essential tables]] do need to be related to each other.+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.
==Types of Relationships== ==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.+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== ==Creating Relationships When One Field is a Primary Key==
#With the database open, go to the {{menu|Tools}} menu and choose {{menu|Relationships}} #With the database open, go to the {{menu|Tools}} menu and choose {{menu|Relationships}}
-#In the floating window, click on the '''Field Trips''' and then click the {{button|Add}} button. A small window named {{window|Field Trips}} appears in the larger {{window|Relation Design}} window.+#In the floating window, click on the '''Field_Trips''' and then click the {{button|Add}} button. A small window named {{window|Field_Trips}} appears in the larger {{window|Relation Design}} window.
#repeat with the '''Sightings''' #repeat with the '''Sightings'''
#close the floating window #close the floating window
-#click on the the {{prefName|ID}} field in the {{Window|Field Trips}} sub-window; drag from there to the {{prefName|Field Trips ID}} field in the {{Window|Sightings}} sub-window. A line appears connecting these two fields, with a '''1''' the {{window|Field Trips}} end and an '''n''' next to the {{Window|Sightings}} window. This identifies a one-to-many relationship.+#click on the the {{prefName|FieldTrip_ID}} field in the {{Window|Field_Trips}} sub-window; drag from there to the {{prefName|FieldTrip_ID}} field in the {{Window|Sightings}} sub-window. A line appears connecting these two fields, with a '''1''' the {{window|Field_Trips}} end and an '''n''' next to the {{Window|Sightings}} window. This identifies a one-to-many relationship.
#click on the hard drive symbol in the toolbar to save the changes you have made, and close the window. #click on the hard drive symbol in the toolbar to save the changes you have made, and close the window.
Line 18: Line 18:
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: 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 '''AOU_Birdlist''' and '''Sightings''' tables ('''EnglishName''' relates to '''Bird_Name''')
-:The '''Locations''' and '''Field Trips''' tables ('''LocationName''' relates to '''Location''')+:The '''Locations''' and '''Field_Trips''' tables ('''LocationName''' 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.+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=== ===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.+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.
#In the main databse window, click on the {{prefName|Tables}} icon in the left hand column #In the main databse window, click on the {{prefName|Tables}} icon in the left hand column
-#Right-click or control-click on the {{prefName|AOU Birdlist}} icon and choose {{Menu|Edit}}+#Right-click or control-click on the {{prefName|AOU_Birdlist}} icon and choose {{Menu|Edit}}
#Click in the cell with the '''English Name''' field name #Click in the cell with the '''English Name''' field name
#Click on the {{Button|Index Design}} button on the toolbar. (It looks like a sheet of paper with a blue drafting triangle laying ontop of it.) The {{Window|Indexes}} window opens. #Click on the {{Button|Index Design}} button on the toolbar. (It looks like a sheet of paper with a blue drafting triangle laying ontop of it.) The {{Window|Indexes}} window opens.
#Click the {{Button|New Index}} icon (a sheet of paper with a yellow sunburst.) #Click the {{Button|New Index}} icon (a sheet of paper with a yellow sunburst.)
-#'''New Index''' Appears in the box on the left. Type an appropriate name (e.g. English Name)+#'''New Index''' Appears in the box on the left. Type an appropriate name (e.g. EnglishName)
#Check the {{prefName|unique}} box in the {{Section|Index Details}} section #Check the {{prefName|unique}} box in the {{Section|Index Details}} section
#Set the {{prefName|Index Field}} to '''EnglishName''' and the order to '''Ascending''' #Set the {{prefName|Index Field}} to '''EnglishName''' and the order to '''Ascending'''
Line 39: Line 39:
===Creating a Unique Constraint=== ===Creating a Unique Constraint===
-#Make a note of the exact table and field names involved. In this case '''AOU Birdlist''' and '''EnglishName'''+#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.'' #:''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.''
#While the database open to the main window (the one with Tables, Queries, Forms, and Reports in the left hand column), go to the {{Menu|Tools}} menu and choose {{Menu|SQL...}}. #While the database open to the main window (the one with Tables, Queries, Forms, and Reports in the left hand column), go to the {{Menu|Tools}} menu and choose {{Menu|SQL...}}.
#Enter the following statement into the top portion of the {{Window|Execute SQL Statement}} window: #Enter the following statement into the top portion of the {{Window|Execute SQL Statement}} window:
-#:{{preBox|<nowiki>alter table "AOU Birdlist" add constraint "uq_EngName" unique ("EnglishName" )</nowiki>}}+#:{{preBox|<nowiki>alter table "AOU_Birdlist" add constraint "uq_EngName" unique ("EnglishName" )</nowiki>}}
#::uq_EngName is the name of the constraint that is created by this command. #::uq_EngName is the name of the constraint that is created by this command.
#Click {{Button|Execute}} #Click {{Button|Execute}}
Line 52: Line 52:
#With the database open, go to the {{menu|Tools}} menu and choose {{menu|Relationships}} #With the database open, go to the {{menu|Tools}} menu and choose {{menu|Relationships}}
#Click on the icon that looks like a block of blue cells with a green plus in the corner. #Click on the icon that looks like a block of blue cells with a green plus in the corner.
-#In the {{Window|Add Tables}} window that appears, click on '''AOU Birdlist'''+#In the {{Window|Add Tables}} window that appears, click on '''AOU_Birdlist'''
:'''Note: If you have not created any relationships yet, the {{Window|Add Tables}} window will open automatically. :'''Note: If you have not created any relationships yet, the {{Window|Add Tables}} window will open automatically.
#If the '''Sightings''' Table does not appear in the relationship "map," add it as well. #If the '''Sightings''' Table does not appear in the relationship "map," add it as well.
#close the floating window #close the floating window
-#click on the the {{prefName|EnglishName}} field in the {{Window|AOU Birdlist} sub-window; drag from there to the {{prefName|BirdName}} field in the {{Window|Sightings}} sub-window. A line appears connecting these two fields.+#click on the the {{prefName|EnglishName}} field in the {{Window|AOU_Birdlist} sub-window; drag from there to the {{prefName|Bird_Name}} field in the {{Window|Sightings}} sub-window. A line appears connecting these two fields.
#click on the hard drive symbol in the toolbar to save the changes you have made, and close the window. #click on the hard drive symbol in the toolbar to save the changes you have made, and close the window.

Revision as of 21:47, 7 October 2007

This article is part of the To Base and Back Again tutorial series.

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.

Contents

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 the 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 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 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 Field_Trips tables (LocationName 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 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. 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


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 Bird_Name 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


This article in other languages: Français
Personal tools