Bookshelf v7.5: Relationships

Now you need to set up the relationships between these components. Table 11 summarizes various fields that you need to set up for this given example. Individual steps are described in detail in the following sections.

Table 11. Fields
Name	Target Key	User Key	Foreign Key	Parent Integration Object
Department	Dept_Id	Dept_Id	None	None
Employee	None	Emp_Id	Foreign key field: Dept_Id Target Key: Dept_Id	Department

Setting up the Parent Integration Component

Hierarchy among the integration components is defined by using the Parent Integration Component field. When the SQL Database Wizard has finished with making the integration object and its integration components, this field will be empty for all the integration components. In the given example, the Department is parent to an Employee. Also, in this case, Department has no parent; therefore, the Parent Integration Component field will be empty.

In Figure 7, Department has no parent, and the parent of Employee is Department.

Figure 7. Parent Relationships

Defining Relationships for the Parent

For the parent integration component, you need to define a Target Key and a User Key. Target Key is what the child integration component refers to (through its foreign key) and a User Key uniquely identifies a row in a component. So, both parent and child integration components will have User Keys.

Figure 8. Key Definitions

Define two keys (using Integration Component Key) for the Department integration component. Key Type for one of these keys is Target Key and for the other, it is User Key.

For this example, Dept_Id is used as both Target Key and User Key, since it forms the basis for the parent-child relationship with Employee and also uniquely identifies the records in the Department table.

In Figure 9, Dept_Id is the Target Key. And, Dept_Id is also the User Key for the Department integration component.

Figure 9. Dept_Id Target Key

Setting up Foreign Key and User Key for the Child

In setting up the hierarchical relationship between the parent (Department) and child (Employee), you need to define which keys in these two tables are related. When you define a foreign key for a child, you need to perform two steps - setting up the target key (that is, the appropriate key in the parent to which this foreign key points to), and the actual field in the foreign key.

In Figure 10, the key type is Foreign Key and the target key name is Target_Key_Dept_Id, which was set up in the parent key structure.

Figure 10. Key Type of Foreign Key and Target Key Name of Target_key_dept_id

For the foreign key, setup the appropriate field from the child. In this case, you select the Dept_Id field from the Employee table.

Figure 11. Target and Foreign Keys as Parent and the Child

Now that the relationship between the parent and the child is fully set up (through the Primary Integration Component field and the Target and Foreign Keys as parent and the child, respectively), the last step is to set up keys including a User Key for the child.

In Figure 12, the Employee integration component has its own user key. The field here is Emp_Id from the underlying Employee table. This user key could have contained as many fields as necessary. For example, if the Emp_Id is not unique across the company, but only within a department, then use Emp_Id and Dept_Id as the fields in this user key.

Figure 12. Employee User Key