4.6. Referenced Collection Joins

This section shows you how you can map a Referenced Collection attribute (i.e. an attribute whose type is REFERENCED COLLECTION and whose Owned option is not checked) to a join table. For details on join tables see section 2.5. Modeling with Join Tables. When you map a join table to a Referenced Collection attribute, VBSF automatically and transparently takes care of updating the join table behind the scenes. Please note that this only happens when the Referenced Collection attribute is defined as the join table manager in the Relationship panel of the Attribute Mapping Form.

To map a join table to a Referenced Collection attribute you must first create a Collection Join schema object. To do this select the "Collection Joins" folder in the tree for the class containing the Referenced Collection attribute(s) to be mapped, and chose the Object->New menu option. The following dialog box is then displayed:

Select from the combo box drop down list the name of the Referenced Collection attribute to be mapped (Note: Only existing Referenced Collection attributes appear in the list. The combo box is not editable, so you are not be allowed to map a join table to an non-existing attribute), and click OK. A new Collection Join object is then created with the same name as the Referenced Collection attribute, and added to the tree under the currently selected "Collection Joins" folder.

To edit the newly created Collection Join object select it in the tree so its corresponding form will be displayed on the right hand side of the split pane as shown below:

The form allows you to enter the name of the join table and the name of each of the foreign key columns in the join table. VBSF is case sensitive when it comes to all table and column names, so when referring to the same table or column name in multiple forms the same case must be used. The Data Type fields, Indexed, Primary Key and Lead Key settings are only used when generating a DDL script from the schema. Each is explained in more detail below:

Data Type. Enter the column data type. For floating point types you may directly specify the precision. You may leave the Data Type fields blank as the script generator will automatically obtain the correct column data types from the object ID attributes types.

Indexed. Check this option if the column is indexed.

Primary Key. Check this option if the column is a primary key.

Lead Key. When a composite primary key is specified (i.e. the Primary Key option is checked for both columns), then you can specify which column is the lead key (i.e. the key that appears first) by checking this option.

The join table defined in the above form is mapped to the parts Referenced Collection attribute of the Assembly class in the transparentjoin sample application. This application describes a scenario in which three tables: Assembly, Part, and BOM (a bill of materials join table) are modeled using two classes: Assembly and Part. The Assembly and Part classes are related via the BOM join table which does not appear in the object model. The Referenced Collection attribute parts is used by the Assembly class to references its component parts. Below is the structure of the three tables:

In the above structure the BOM join table has a separate primary key column named ID. Note however that this is not necessary, as you could specify instead both the AssemblyID and PartID columns as primary keys, resulting in a composite primary key. Using a composite primary key in the join table has several advantages, as outlined below, over a single sequential primary key approach:

1. Duplicate join relations are impossible as uniqueness is based on the join values and enforced at the database level.
2. The mapping tool can generate the full DDL for join tables.
3. Retrieval is faster because related joins are stored together physically based on the lead key. An index can also be specified on the non-lead key to ensure fast retrieval when navigating both sides of the relation.

Next Section 

Return to Table of Contents