Documenting complex 3-table relationships

Piotr Kononow - Dataedo Team Piotr Kononow 22nd April, 2021

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
0
There are no comments. Click here to write the first comment.