Tools Guide
Chapter 6 Relationship Designer
In order to use data from more than one table when defining the data used in a form, view, or page, you must first establish relationships between the tables that contain the data you want to use. You can use the Relationship Designer to:
-
View the relationships between tables in a database
-
Create soft relationships between tables (see
Soft relationships)
-
Create hard relationships between tables (see
Hard relationships)
This page describes the following:
Concepts you need to know 
You should understand the following concepts before you use the Relationship Designer.
Relational databases
A relational database is a collection of tables. Each table has the following properties:
-
Each entry in a table occurs only once.
-
Each column is named.
-
All values of a given column are of the same type.
-
Column order is not important.
-
Row order is not important.
SilverStream does the hard work for you when designing how tables and columns relate to one another.
Tables and relationships
When two tables are connected, they are said to have a relationship. SilverStream makes establishing relationships between tables easy through the use of the Relationship Designer. There are two main types of relationships between tables:
One-to-one
In a one-to-one relationship, one row in one table is related to only one row in the second table, and vice versa. You can query data from both tables by joining or linking them.
One-to-many
In a one-to-many relationship, a row in the first table may match many rows in the second table, but each row in the second table matches only one row in the first table.
Joining tables
A join combines data from more than one table. SilverStream supports the following kinds of joins:
Inner joins
The data that results from an inner join combines only those table values that match your search criteria. In the following example, you've created a relationship between two tables: Students and Honors. You want the result set to include only those students in the class who graduated with honors.
STUDENT NAME
|
HONORS
|
Sarah Morache
|
Magna cum laude
|
Jessica Jacobs
|
Summa cum laude
|
Abigail Stark
|
cum laude
|
James Hardcastle
|
Summa cum laude
|
Inner joins are also known as equijoins.
Outer joins
The difference between an inner join and an outer join is that the result set from an outer join combines the columns from both tables in the relationship, regardless of whether the column values match. Those columns that do not have matching values are presented in the result set as having null values. In the following example you have once again created a relationship between the Student and Honors tables. This time you want the result set to include all the students in the class. The result set would resemble the following:
STUDENT NAME
|
HONORS
|
Sarah Morache
|
Magna cum laude
|
Andrew Scott
|
Null
|
Jessica Jacobs
|
Summa cum laude
|
Richard Perry
|
Null
|
Abigail Stark
|
cum laude
|
James Hardcastle
|
Summa cum laude
|
Soft relationships
You can create soft relationships between tables using the Relationship Designer. A soft relationship is one that does not involve a primary-to-foreign-key relationship. It is stored in metadata in the native database.
Hard relationships
A hard relationship is a primary-to-foreign-key relationship that is stored in the database.
Relationship Designer keyboard shortcuts
Use these keyboard shortcuts.
Keystroke
|
Description
|
|---|
Ctrl+S
|
Save
|
Ctrl+Z
|
Undo
|
Ctrl+Y
|
Redo
|
Ctrl+X
|
Cut
|
Ctrl+C
|
Copy
|
Ctrl+V
|
Paste
|
F1
|
Open Help
|
F4
|
Open Property Inspector
|
Accessing the Relationship Designer
To access the Relationship Designer:
-
Select the Table icon from the left pane of the window. A list of tables that exist in the database you specified displays. The Relationship Designer icon (
) appears in the Action Gallery located at the bottom of the right pane.
-
Select the Relationship Designer icon. If no tables are displayed, a list box appears prompting you to select tables to create or view relationships for.
Viewing relationships
You can view relationships for specific tables or for all the tables located in your current database.
To view relationships:
-
Select the tables that you want to view either from the list box that appears when you first access the Relationship Designer, or use the Add Table button in the toolbar.
-
Select Add for each table. The table appears in the Relationship Designer window.
-
Select Close when you finish selecting tables. The tables appear in the Relationship Designer window. Hard relationships are represented by black lines. Blue lines indicate soft relationships. These lines turn red when you select them.
-
Select the Show All Relationships for All Tables button from the toolbar to view all of the relationships between all the tables that reside on the current database.
-
To remove a table from the display,select it and press the Hide Table button. Hiding tables does not remove or otherwise affect the tables or their relationships.
Creating relationships
To create relationships between tables:
-
Select the Add Table button. A list box appears.
-
Select the tables you want to include in the relationship.
-
Select Close to return to the Relationship Designer window where the tables appear.
-
Select a column in one table and hold down the left mouse button. Drag your cursor across to the column in the second table. When you release the mouse button, a line appears linking the two tables. The endpoints of the line are marked with l or m to indicate the type of relationship. Use the Property Inspector to change relationships if necessary.
-
Select the line you just created. It turns red when you select it. Blue lines indicate a soft relationship. Black lines indicate a hard relationship.
-
Select the Property Inspector button from the toolbar. The property tab for the relationship you are creating appears. The name of the relationship appears in the name: field. The relationship name is composed of the two table names. The link clause created when you selected the table columns appears under the Database Columns section.
-
Select Add New Columns to create more link clauses between columns in the two tables. The Choose Columns dialog box appears. This dialog box contains two panes, one for each table. The columns that make up each table are listed
-
Create soft relationships between other table columns by selecting a column from each pane and choosing OK. The new link clause appears in the Property Inspector. Once you have created a link clause, you cannot change it, you can only delete it.
For more information about soft relationships, see
Soft relationships.
-
Select the down arrow in the Join Type field to specify the type of link you are creating. Choose either Inner or Outer.
-
Check the Hard relationship (create a foreign key) check box to create a hard relationship (this check box displays only if one of the columns you have picked is a table's primary key). The DBMS makes sure that the relationship between the tables you selected is valid, and that the tables are synchronized whenever data is updated, inserted, or deleted.
-
If you are creating a hard relationship, select the down arrow in the Update Rule and/or Delete Rule fields to define the rules for updating table data. Update rules define what happens when you update a column in a table that is linked to another table in a many-to-one relationship. Delete Rules define what happens when you delete a column in a table that is linked to another in a many-to-one relationship. Choose one of the following:
-
Cascade indicates that all foriegn key column(s) that reference the primary key column(s) are updated or deleted when the primary key is updated or deleted.
-
Restrict indicates that the referenced primary key column cannot be modified.
-
SetNull indicates that all foreign key column(s) that reference a modified primary key column are set to Null.
Copyright © 2000, SilverStream Software, Inc. All rights
reserved.
