Sample output with colors
Sample output with relationships
Sample output without relationships
Contents
- Let's begin . . .
- Flow of the tool
- Almost done . . .
Abstract of this article
When handling a project with hundreds of tables, it takes some time to understand the flow within the database. Especially so if there is no document available. A possible scenario is legacy code up for maintenance or migration. After going through just a few tables, it starts to become too cumbersome to maintain the relationships in your mind. This article aims to create a simple tool for visualizing database tables and relations, a database map to refer to.
The main aim was to generate the documentation automatically for the database relationships as and when required. The reason for using the WinGraphViz library was to make sure that the database relationships did not have much crossings and that the end result looked clutter-free and presentable.
Visualization concepts
"A picture is worth a thousand words". As the saying goes, it is better to have a picture of any complex entity to understand it easily. Especially in the areas where a lot of operations are done on a logical level rather than handling physical objects. Entity relationship diagrams, Use Case diagrams, database diagrams, organization hierarchies, and even a family tree are candidates for a visualization method that takes care of laying out the relationships so that they are easily distinguishable.
A good visualization algorithm allows the user to see a pattern that may be difficult or impossible to see when the same data is presented in a raw form. Such a visualization is known as a "Planar Graph". There are three main points to note when developing a visualization tool. They are:
- Graph layout
- Interaction with the user, and
- Presentation
We are going to use the WinGraphViz library to solve all the above mentioned problems. WinGraphviz is a free software based on the Graphviz project by AT&T. The graph layout is taken care of by the algorithm, and we'll be generating the output in SVG for handling the interaction and presentation part.
In graph theory, a planar graph is a graph that can be drawn so that no edges intersect. For those interested in the details, please download this file. A graph consists of two components: vertices and edges. Vertices are simply labels such as numbers, and edges are the connections between the vertices. Edges can also have a direction which creates a directed graph, or Digraph for short. The WinGraphViz library attempts to create such a Directed Planar Graph. As it is available as a COM library, we'll be using it to create our database visualization tool. For a sample of what graph optimization can do, check out the following diagram:
The image was taken from the paper: Automatic Graph Drawing. The site also mentions the Automatic Graph Library, or AGD Library for short, which can also be used for visualization purposes.
We are going to use a similar graph drawing algorithm to make our output diagram clutter-free and neat.
A note about SVG
SVG is a language for describing two-dimensional graphics and graphical applications in XML. SVG files are compact, and provide high-quality graphics on the Web, in print, and on resource-limited hand-held devices. In addition, SVG supports scripting and animation, so is ideal for interactive graphics. The Adobe SVG Viewer is free, and is available as an ActiveX control which enables us to see the output in a web browser or in a Windows application too. SVG output is highly recommended due to its zooming capabilities. As SVG is vector based, the clarity of the output would remain constant no matter if you are zooming in or zooming out.
Hints about using the Adobe SVG Viewer:
- Use the Ctrl key to zoom in.
- Use the Shift + Ctrl keys to zoom out.
- Use the Alt key for panning.
Step 1: Database connectivity
To implement this part, we are going to use the "SQLSrvEnumerator" class available here. On selection of the server combo box, the list of servers available on the network is enumerated and displayed. The user credentials are obtained to login to the selected server. The list of databases is enumerated and is automatically populated in a combo box, and we are going to select one from the list. The database connectivity is handled using ODBC here. The remaining operations are done using ADO. Though the connection can also be done using ADO, I was plain lazy to change the code after deciding to use ADO instead of ODBC.
Step 2: Selecting the needed data
In this simple operation, we are going to select the needed columns. On loading the page, the "Tables" combo is automatically populated based on the selection of the database in the previous step. On selecting a table from the list, the "Columns" combo box is populated with all the columns in the selected table. Selecting the "All" value from the combo selects all the columns in the table. You can move the column names up or down based on how we want our diagram to look like. This option is purely for cosmetic purposes.
This operation is crucial to how our diagram is generated. We need to select the relationships between tables in this step. Select the tables first, and select the columns by which they are actually related to each other. For example, Table1->PK and Table2->FK. The relations entered here are used to generate our visualization.
Step 4: Visualization and code generation
We've reached our final step now. You can enter any comments if needed. The "Generate Query" button does exactly what it says. It generates a SQL query based on our selection of tables, columns, and relationships. It generates only a simple query now, and in the very first upgrade of this article, I promise to do something better. It was added as an afterthought as this article only tries to focus on the visualizing part. The "Image Type" combo supports various image formats such as JPG, SVG, PNG, GIF, and EMF. As per your selection, the output diagram is generated. The "Show Relationship Labels" check box displays the selected relationships in the diagram.
The CVisualization class encapsulates all the functionalities of the graph drawing. The available methods are:
void AddComment(CString strComment);
void AddTable(CString strTableName,CStringArray &strArrayFields);
void AddLink(CString strNodeOne, CString strNodeTwo, CString strLabel,CString strColor = "black");
BOOL SaveToFile(int nImageFormat,BOOL bOpenFile);
The CVisualization class available with the source code encapsulates all the drawing functions and output functionality needed for our database visualization tool. Please install the Wingraphviz library in order to execute our tool properly.
Current limitations
This tool can only handle simple relations. It cannot handle nested Select statements. The method used to select the tables and the columns is also quite crude. The visualization generates only links to the tables and not the actual keys.
This tool has only been tested with SQL Server 2000, and has not been tested with other databases.
- Handling nested
Select statements.
- Handling complex relationships.
- More visual representation of the table and column selection.
- Code generation (client and server) based on the selected relationships.
- Complex SQL query generation.
You can find the latest copy of this tool at my website too.
This tool is not meant to replace the SQL Server Database Diagram Designer. It is only complementary. In situations where the relationships are not maintained properly in the database and when a better visual representation is needed, this tool can be used.
