How To Use Microsoft Visio To Reverse Engineer A Database

It is extremely important to document a database in order for other people to fully understand it. Sometimes you will be off work for a number of reasons and someone else may need to perform a task and require documentation for help. When you come to train someone else or hand over the database to another party then documentation is a vital resource in the learning curve and will prevent misunderstanding later.

It is however, an extremely tedious task to manually build a database diagram having gone through the individual tables and views etc. Visio has the functionality to reverse engineer a database for you with minimal effort. This means that it will automatically take the database you give it and create a database diagram of all the tables and fields. This is done via a wizard, allowing the user to select individual tables or all tables if required. The database diagram can then be altered, rearranged and formatted as required for inclusion in documentation or presentation. Freedom of design is therefore not compromised, but enhanced by using this feature.

To begin the process, open Visio and choose File and New. In the template categories, select the Software and Database section and then Database Model Diagram. On the ribbon the Database section will automatically appear containing all options relating to this type of diagram. Go to this section and choose Reverse Engineer to access the wizard. Select the appropriate database option, in this example we are importing the schema from an Access database, but there are several other types you can use. Click Next and enter any User name and Password required or leave the fields blank if this is not necessary.

On the next pop up locate the database within the correct drive. Click OK and on the next screen select the options required such as tables, views, primary Keys etc. in order to tell Visio what to include on the final diagram. If for example there are no stored procedures in the database then this option will be greyed out. Click Next and a list of all tables should appear. You can choose to select everything or choose individual tables required for the diagram. Click Next and then choose whether Visio will add the shapes or if you would like to do it yourself. You will then be given a chance to review the information that will be placed on the diagram before you create the finished piece. If everything is as required then click Finish.

A diagram and a summary of what was reversed engineered, which can be closed, are then presented on the screen. This final diagram can be formatted and rearranged quite easily. Click on individual tables and drag them to where you want them to be. Double click on a table to show the table information which can be altered such as the table name. Right click and choose Format in order to format the Lines, colours or font.

On the left side of the screen are lists of symbols that can be added to the diagram as required by the diagram designer. These are organised into logical categories such as Entity Relationship, Object Relationship and Quick Shapes. When adding any symbol, click it and drag it onto the page. You will then be able to move it to the correct position, adjust its size and change its formatting to match the diagram styles. Using this functionality it is possible to quickly and easily create professional database diagrams which are good enough to be used in presentations and external documentation.