To explain how we create the various relationships when there is a possibility of linking many records in one table to many records in another table, we will use the example of joining the Controls List to the Roles List. The join table for joining Controls to Roles is found here:

http://unifiedcompliance.com/NFI/XSDs/UCF_Roles_To_Controls_Join_v2_1.xsd

The Basics

Roles are joined to Controls via the join table that stores (in this case) two ID's: The Role ID and the Control ID. A join table is necessary when multiple records on one side (i.e. Roles) can be related to multiple records on the other side (i.e. Controls). Below is a diagram that illustrates this:

Roles to Controls Diagram

The sample data in these tables shows that Role ID 0000001 is joined to Controls 00636 and 00508, while Role 0000004 is joined to Control 00636. Perspective is everything in terms of data. Let's take this same diagram and add a bit of color to clarify this concept:

Highlighted relationships in the Roles to Controls join

Now we can more clearly see that Role ID 0000001 has two join records that connect it to two separate controls (00508 and 00636). So if your perspective is Role ID 0000001, and you want a list of controls related to it, you would get 00508 and 00636 from the join table.

Gleaning additional information

Getting the names of those controls requires you to "reach through" the join over to the Controls table where 00508 resolves to "Technical Security" and 00636 resolves to "Monitoring and Measurement".

If we were to look at the data from the perspective of Control ID 00636 and wanted a list of related Roles, the join table would connect to Role ID's 0000001 and 0000004. Reaching through the join table to Roles to get the Role Names would return "Marketing" and "Product/Solution Planning" respectively.

Integrity checking

With most join tables, it's a violation to have two or more records with the same ID's in both rows. For example, it would be a violation to have two join records both connecting Role ID 0000001 to Control ID 00508.