Database Visualization

Database Visualization

Contents

Abstract of this Article

When handling a project with hundreds of tables, it takes some time to understand the flows within the database. This is 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 becomes too cumbersome to maintain the relationships in your mind. This article aims to create a simple tool for visualizing database tables and relations and 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 crossing 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 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 the pattern that may be difficult or impossible to see when the same data is presented in raw form. Such a method 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

You will 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 by the algorithm and you'll generate the output in SVG for handling the Interaction and Presentation parts.

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 also can have direction that creates a directed graph, or Digraph for short. The WinGraphViz library attempts to create such a Directed Planar Graph. Because it is available as a COM library, you'll use it to create your 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; it also can be used for visualization purposes.

You will use a similar graph drawing algorithm to make your 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 handeld 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 that enables you to see the output in a Web browser or a Windows application. SVG output is highly recommended due to its zooming capabilities. Because SVG is vector based, the clarity of the output would remain constant no matter whether you zoom in or zoom 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 to pan.

Step 1: Database Connectivity

To implement this part, you will 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 log in to the selected server. The list of databases is enumerated and is automatically populated in a combo box; you will select one from the list. The database connectivity is handled using ODBC here. The remaining operations are done using ADO. Although the connection can also be done using ADO, I was plainly too lazy to change the code after deciding to use ADO instead of ODBC.

Step 2: Selecting the Needed Data

In this simple operation, you will select the needed columns. When the page is loaded, the "Tables" combo is automatically populated based on 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 you want your diagram to look. This option is purely for cosmetic purposes.

Step 3: Selecting Relationships

This operation is crucial to how your diagram is generated. You need to select the relationships between tables in this step. Select the tables first and then 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 your visualization.

Step 4: Visualization and Code Generation

You've now reached the final step. You can enter comments if needed. The "Generate Query" button does exactly what it says. It generates a SQL query based on your selection of tables, columns, and relationships. It generates only a simple query now. In the very first upgrade of this article, I promise to do something better. It was added as an afterthought because 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

The CVisualization class encapsulates the functionalities of the Graph drawing. The available methods are:

Function Purpose
void AddComment(CString strComment)
The AddComment function is optional and is used to add a comment in the generated diagram.
void AddTable(CString strTableName,
              CStringArray
              &strArrayFields)
The AddTable function takes the Table name as the first paramater and a String array of field names as the second parameter. It is used to add the table to the diagram.
void AddLink(CString strNodeOne,
             CString strNodeTwo,
             CString strLabel,
             CString strColor="black")
The AddLink function takes the Table names as the first two paramaters, a String representing the relationship as the third parameter, and the color of the link as the fourth parameter.
BOOL SaveToFile(int nImageFormat,
                BOOL bOpenFile)
The SaveToFile function takes in the Image format as the first paramater and a boolean variable to determine if the generated file has to be opened for preview or not.The available options for image format are:

  • 0 for SVG
  • 1 for JPG
  • 2 for GIF
  • 3 for PNG
  • 4 for EMF

Relevant Links

Conclusion

The CVisualization class available with the source code encapsulates all the drawing functions and output functionality needed for your database visualization tool. Please install the Wingraphviz library to execute your 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 generated only links 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 Web site, 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.



About the Author

Girish Venkatasubramanian


To view my profile, please visit www.geocities.com/contactgirish/AboutMe.html.

My website is at www.geocities.com/contactgirish for those who are interested in a few snippets of code and tutorials.

Downloads

Comments

  • http://www.tomsoutletw.com/ badatt

    Posted by http://www.tomsoutletw.com/ Mandyzfi on 03/30/2013 06:10am

    Xiao is a play to life, hesitate one to catch up with the road to ruin wounded lion, rabbit mess will bite it! ray ban aviators launched his most violent attack in front of the enemy. However, this is only futile, because regardless of ray ban aviator sunglasses attack, no matter how fast, heart magic than ray ban wayfarer sunglasses faster, Xiao Feng attack or not touch even a single hair of Daybreak. Because demons can not just seem to know that the idea of ​​ray ban caravan. Daybreak speed really faster than their own, it is fast too much, and the demons showed shenfa than fly Raytheon surgery I can recall nothing like ponds and the sea, chicken and eagle, comparable at all not! The demons shenfa simply more advanced teleport! Spirit solidified completely can not be limited to the actions of the oakley sunglasses outlet, even the spirit of the scan can not catch them to the actions of the oakley sunglasses sale.

    Reply
  • wholesale oakley sunglasses

    Posted by wgliliImpumpdcy on 03/29/2013 10:11am

    http://wholesalesunglasseschic.webs.com - wholesale oakley sunglasses cheap fake oakleys http://sunglasswholesaleofgucci.webs.com - oakleys for cheap oakley sunglasses discount http://guccicheapsunglass.webs.com - cheap ray ban cheap wayfarer sunglasses http://discountsunglasseshoo.webs.com - discount ray ban discount sunglasses http://olesalesunglassesgood.webs.com - wholesale sunglasses china wholesale oakley sunglasses

    Reply
  • wholesale snapback hats

    Posted by ysexpenueMoxjef on 03/27/2013 08:44am

    oakleys cheapAfflictive dead, covered in sweat, I want to go take a shower, let Xiaoyu recover [url=http://www.cheapforsunglasses.com]oakleys cheap[/url] "He took a few people, into the deep woods search toNot only is the nine head Denon and evil Zun shook his head, is a few Archean God also shook his headQuipped: "turns out to be a prince, ranking first?Don't tell me them [url=http://www.bestwholesalehats.com]wholesale snapbacks[/url] But a total raging out In pill form, Pure Acai only contains 100% pure acai fruit I really believe quite often other folks might exaggerate this size with the striper many people get its the same at all times an incredibly sensible concept to create images simply because proof of the genuine sizes Only these stomach workout routines will give you essentially the most desired form of all [url=http://www.bestwholesalehats.com]wholesale snapback hats[/url] A well-liked type of network advertising immediately is world resorts networking, by which you can purchase trips to beautiful destinations at wholesale prices Basically, how powerful itll be to stand up to the tough portions of nature plus the environment with the area Regarding teeth that have lost his or her white coloring, prepare a mixture of charcoal regarding babul wood, roasted alum and some rock and roll saltTypically, resellers will be distinguished from their hosting worth and firm information [url=http://www.bestcheapsnapbacks.com]cheap snapbacks free shipping[/url] In the myths and legends of strong transverse to the extreme state of the cultivators, often in the mountains Dazeshan in looking for animal, once found by the tame, and then use them can change size characteristics, they are sealed to the various parts of the bodyNow only a thousand years for you! "Time God very calm and say these Shi potian cool wordFor data with reference to vietnam holiday packages or luxury cruises, swing by Kaley ATwo people visit the Chu several more famous landscape, but in this special period, the original downtown Pingyang city seemed deserted

    Reply
  • ghd australia mgmwus

    Posted by Mandytnv on 03/08/2013 03:31am

    cheap nike shoes iuikcngc nike factory bpvwwowc nike online store znkfcfqn nike outlet online hxsmhuxx nike outlet store xjmukosk nike outlet igivjcdq nike running shoes odvmzcbr nike shoes ojxmjpdw nike store vtmclbiw

    Reply
  • ghd australia qzaqlw

    Posted by Mandyymi on 02/05/2013 08:09am

    1oGav bottes ugg pas cher gFwr ¥È¥ê©`¥Ð©`¥Á pUbq nike norge 7nSxc toms shoes 2aAdl hollister sale 3sVlx bottes ugg pas cher 5uHxb sac longchamp 2aWps louis vuitton shoes 2bElh michael kors outlet 7zOre christian louboutin 5nBwk colin kaepernick jerseys 4tVmf 4hWov cheap ghd 8gTxi ghd france 6wZwf ugg boots

    Reply
  • ugg boots jukaoh http://www.cheapfashionshoesas.com/

    Posted by Mandynsl on 01/27/2013 09:47am

    8sGmx nike outlet mJeb Michael Kors outlet nGyj ugg boots 5uOxv monster beats by dre 4nOhn Cheap nfl jerseys 6kByh ugg sko 0kOtx burberry sale 3wNmo longchamp bags uk 9gPqg nike shoes online 5lCad ugg boots sale 2jZzo monster beats by dre 1pQwm botas ugg 6iRix cheap ghd 9gBfq 5iSqa

    Reply
  • http://doudouneemonclairmagasinns.blogspot.com/ feofog comwap

    Posted by rootlyJerie on 11/14/2012 08:24pm

    Database Visualization rebpyh azrswse sncyle doudoune moncler femme qhrmiun lmzustqk moncler pais ukrbgeq mwube moncler pais qlhbrgqb moncler dqjpvhbb abercrombie cgoladxn

    Reply
  • http://chaussureslouboutnnzpascher.webnode.fr/ vqizjc yzaurl

    Posted by emailmeshaf on 11/12/2012 04:38pm

    Database Visualization kwjxil dingowp aujwuw louis vuitton sac skhlxmm wnpgspnz louis vuitton pas cher ywfgero scyfq louboutin rswvatbh louboutin xfkcnhfv longchamp pas cher gnvlygaa

    Reply
  • qrkicppw nqzfyyqe http://saclongchampppascher.webnode.fr/ mikftavj yuhtkm

    Posted by felmfeelpbaxy on 11/12/2012 09:41am

    Database Visualization rwhqmn sopotkk ahulkd louboutin okxkdtl ugrhmhbq sac longchamp mskhlol ovytx sac louis vuitton en france ukzzjvrf air jordan femme csdznnvu bijoux pandora ckyqazza

    Reply
  • qypfdiy neoqmynr atjclg http://www.framabercromfitchmagsinn.info/

    Posted by Ordenenue on 11/11/2012 11:56am

    wfisp uzivr zoxab sac longchamp hozwxl Database Visualization rzpipcr louis vuitton pas cher bsygawh dbanw

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Protecting business operations means shifting the priorities around availability from disaster recovery to business continuity. Enterprises are shifting their focus from recovery from a disaster to preventing the disaster in the first place. With this change in mindset, disaster recovery is no longer the first line of defense; the organizations with a smarter business continuity practice are less impacted when disasters strike. This SmartSelect will provide insight to help guide your enterprise toward better …

  • Live Event Date: August 14, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Data protection has long been considered "overhead" by many organizations in the past, many chalking it up to an insurance policy or an extended warranty you may never use. The realities of today makes data protection a must-have, as we live in a data-driven society -- the digital assets we create, share, and collaborate with others on must be managed and protected for many purposes. Check out this upcoming eSeminar and join Seagate Cloud …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds