Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server
Article

Database Visualization

Rate me:
Please Sign up or sign in to vote.
4.60/5 (11 votes)
31 May 20067 min read 81.4K   2.8K   77   5
This article aims to create a simple tool for visualizing database tables and relations, a database map to refer to.

Image 1

Sample output with colors

Image 2

Sample output with relationships

Image 3

Sample output without relationships

Contents

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.

Planar graph theory

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:

Image 4

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.

Image 5

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.

Image 6

Step 3: Selecting relationships

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.

Image 7

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.

Image 8

The CVisualization class

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);

Relevant links

Conclusion

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.

Proposed features

  1. Handling nested Select statements.
  2. Handling complex relationships.
  3. More visual representation of the table and column selection.
  4. Code generation (client and server) based on the selected relationships.
  5. Complex SQL query generation.

You can find the latest copy of this tool at my website too.

Disclaimer

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Founder
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionUpdates? Pin
ii_noname_ii29-Jun-12 3:04
ii_noname_ii29-Jun-12 3:04 
GeneralInteresting Reading Pin
Paul Conrad29-Dec-07 10:01
professionalPaul Conrad29-Dec-07 10:01 
QuestionHow to Run the application? Pin
kdujoshua30-Aug-07 14:35
kdujoshua30-Aug-07 14:35 
GeneralDownload Pin
g_p_l31-May-06 23:37
g_p_l31-May-06 23:37 
JokeRe: Download [modified] Pin
VGirish1-Jun-06 0:06
VGirish1-Jun-06 0:06 
Hi,

The installer is for the Wingraphviz library. I'll create an installer for
the application and upload it today.

Thank you for your comments. Smile | :) Have a nice day.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.