Discover and Document Database Table Relationships (Foreign Keys)

When you work with databases it is critical you understand how tables are linked to one another. Standard development tools (like SQL Server Management Studio or Oracle SQL Developer) don't really help in this matter. They just show you a list of table columns and somewhere else a list of foreign keys in not so usable manner. And if there are no FK constraints then finding out that relationships is way more difficult and you basically rely on what's in your (and your colleagues) head and any documentation you have.

Screen from SQL Server Management Studio showing table FKs

I want to show you how you can easily discover and document table (and view) relations with Dataedo. Even if they span across different databases.

Prepare

Get and install Dataedo
Create new repository
Connect to your database and read schema with Add documentation button

Discover table relationships

Once you connected to your database and imported schema foreign keys are displayed next to each column.

You can use a shortcut to the linked table:

You can see all the table relationships in a Relations tab including join condition. Nice feature is that you see tables related in both directions, so you also see child tables to our current table.

Document table relationships

OK, but what we saw right now was coming from a database schema. It only shows keys implemented by DBA/developer. But what about all those relationships that don't have physical implementation? Don't worry, there's a solution. You can define and document them in Dataedo repository without interfering with your database.

Let's browse through columns and see if any are potential foreign keys. We found two. Color is a text field that takes values from a lookup. Let's define this relationship.

There's a lookup table called dictionary.

Please note that this key doesn't need to be a primary or unique key like it has to be in case of physical keys.

Second column, manager_no links to a table in another database - HR. Implementing this in SQL Server or MySQL is in fact impossible. But using Dataedo, you can define relationships that link to tables in other servers or even platforms (e.g., you can define the link between SQL Server and Oracle).

View table relationships

Now that we enhanced information about data model we can use it for future reference. As you can see, definitions we added to the repository are now visible just as metadata imported from the database.

Show table relationships

We can do one more thing to better explain table relationships - create diagrams. You can do it simply by creating new "module" (basically, it is a group of objects and a description) and create a diagram by dropping tables to a drawing pane to show table relationships visually.