Documenting complex 3-table relationships

Applies to: Dataedo 23.x versions, Article available also for: 24.x (current), 10.x
You are looking at documentation for an older release.
Switch to the documentation for Dataedo 24.x (current).

Sometimes, relationships get tricky - there is a composite relationship where one column requires a lookup table, such as on the diagram below:

Those relationships on a diagram represent one join, not a 3 distinct joins, i.e. you need to do all three at once to link salaries table with employees table.

There is no direct way in Dataedo you can model this (or other typical data modeling tools). You can use a workaround hover. You can create a virtual column that represents a lookup of the foreign key from the intermediary lookup table. You can use following format to explain required join:

column_name->lookup_table.foreign_key

or if the join to the lookup table is not obvious then the following:

(column_name->lookup_table.primary_key).foreign_key

Then, you can use the new virtual column to document composite foreign key, directly to the primary table.