Reflexive and recursive relationships

Reflexive and recursive relationships imply two or more levels of granularity within a table with a fixed depth.

For example, the Sales staff table has a recursive relationship between Sales_Staff_Code and Manager_Code.

The following example shows how the data might look like in a table:

Jane Smith has a Sales_Staff_Code of 1, but no Manager_Code because she is the manager. Martin Doe and Stephanie Sharaki are both managed by Jane Smith.

To create a functioning reflexive relationship in your model, you can either create an alias shortcut (in Framework Manager only) or a copy of the table. The second option is preferred because you can name the columns accordingly, and create a relationship between the original table and the new one.

For example, create a table named Manager containing Sales_Staff_Code and Sales_Staff_Name that is based on the Sales staff table. Rename Sales_Staff_Name to Manager name in the Manager table in the model. Create a relationship with 1 to n cardinality between Manager and Sales staff that is joined on Sales_Staff_Code and Manager Code.

For a simple two-level structure, using a model table for Manager that is based on Sales staff, the model looks as follows:

For a reflexive, balanced hierarchy, repeat this structure for each additional level in the hierarchy. For example, Table 1 might also include the Director_Code, VP_Code, and so on.

You can go one step further and combine each level of the related hierarchy tables into a final Sales staff table that presents columns for all the levels in the hierarchy. This new, final table would be joined to your fact tables.

For large data volumes with many reflexive levels, performance can be impacted. Thoroughly test your work for performance to ensure that your needs and expectations are met. For performance reasons, it's recommended to flatten the hierarchy in the database into a single table that includes all the required levels in their own columns. An example is shown in the following table:

The same technique could be used for an unbalanced hierarchy with branches that terminate at different levels. The hierarchy would need to be flattened and balanced by padding the data for branches that terminate at higher levels, as shown in Table 2.