Joined Columns

A joined column is a column whose values a database object "borrows" from its parent object. Such a column is borrowed in the sense that the column is not part of the child object proper. "Joined columns" are therefore also referred to as "virtual columns" (as opposed to "database columns").

Understanding joined columns

Because database objects may be related in more than one way, you must define the joined column using the specific relationship involved, i.e. the role defined for the parent-child relationship between the two database objects.

You can also have an object borrow values from a parent, which itself has borrowed the values from a grandparent. In this way it is possible to create joined columns over parents, grandparents, great-grandparents, etc. Performance may be adversely affected, however, because of the time needed to query the database.

NOTE 1: Joined columns MUST be based on the same domain as the parent column they take their values from.

NOTE 2: Joined columns, whether specified in the Definer or inserted via the Windows Designer, cannot be accessed by means of ODBC.

NOTE 3: Joined columns cannot retrieve values from the supertype of the parent in a relationship. The workaround is to define Extra Query columns, as described in the USoft Windows Designer Help.

When to use joined columns ?

Joined columns are especially useful when foreign keys are not meaningful to the end-user.

In the Travel Agency system, for example, this is the case where numbers are used to identify the person who made the reservation. These numbers do not mean much to an end-user, but he or she would know which person is meant if the ID is accompanied by the family name. To achieve this, you include the family name column from the Person table as a joined column in the Reservation table.

How to define a joined column

When you define a joined (or virtual) column for a particular parent-child relationship, first define the virtual column at column definition level.

To define a virtual column:

1.	In the Tables window, select the child table to which you want to add the virtual column.

2.	Define the column, specifying Virtual (not Database) in the Type field.

3.	With the virtual column as the current record, click the Related List button on the toolbar, and choose Column that Gives a Value to This Joined Column.

The Column that Gives a Value to This Joined Column related window appears.

4.	Complete the window as follows:

•Click the Child Table lookup button to select the parent object, role and child object that is needed to 'join' the joined column with the parent column.

•Click the Parent Column lookup button to select the column that will give its value to the joined column.

5.	Save your changes in the database.

NOTE: You can also access the related window by means of the Related option on the Box menu, using either the Column box of the Tables window or the Relationships window as your starting point.