Best Practice - Dependency Analysis with yEd

Do you know this? You come to a new project group, try to get familiar with the project and first you analyze the database schema.

Today I once again had this glorious task and had to realize that Mysql Workbench can create a diagram (Ctrl+R, Reverse Engineer), but you can't see the dependencies of the tables in the slightest. In my case there were 90 tables with many extraneous relationships:

You can't change anything about the number of tables, but from this layout you have no chance to capture the dependency structure of the tables.

My favorite tool for a nice representation of such dependencies is yEd. Among other things, yEd supports the graph format tgf. Here is a small example of a tgf file:

1 First node
2 Second node
#
1 2 Edge between the two

In the first section (up to '#'), nodes are listed with a unique name and separated by spaces in a description. The second section contains edges in the format <ID node1> <ID node2> <description>.

This format works well for me because the information in tgf is sufficient to represent dependencies, yet it uses the simplest format imaginable to do so.

In MySQL I create the tgf line by line with the following select:

SELECT concat(table_name, ' ', table_name) 
FROM information_schema.tables 
WHERE table_schema='<dbtable>'

UNION

SELECT '#' from dual

UNION

SELECT concat(TABLE_NAME, ' ', REFERENCED_TABLE_NAME, ' ', COLUMN_NAME, ' -> ', REFERENCED_COLUMN_NAME)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = '<dbtable>';

The result looks very sobering at first:

First, the borders on the node text should be adjusted. This is achieved with "Tools/Node like label":

After that you can use any layouter you can think of. I personally like to use "Layout/Hierarchical":

But also "Layout/Radial" shows some structure in the dependencies:

There is also a dependency visualization tool integrated in Intellij (using yEd) for database schemes (Intellij Ultimate), class diagrams and package dependencies.

Personally, I am now relaxed about the next DB analysis - and hope you are too!