Documenting complex 3-table relationships

Applies to: Dataedo 23.x (current) versions, Article available also for: 10.x

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.

Found issue with this article? Comment below
Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.