When researching how to design a relational database almost every article or book talks about starting with a diagram or drawing to figure out how the information should be organized. This process can be long and involved, which does not mean it should not be done, just that if there is an easier way it should be investigated. There are a number of tools on the market now to facilitate the design of a database.
An extremely useful tool to reverse engineer a database is Visio 2000. It also has the ability to generate a database. This generation takes place after Visio has been used to draw the tables and relationships. This process can be a huge time saver. If a database needs to be tweaked and reworked it can be done in Visio and then regenerated. This article is intended to provide a nice introduction to using Visio 2000 to generate a database.
To get started, open Visio and select new and then database model diagram. The process of generating a database is straightforward, although there are some options one should set before starting the design process. First, make sure the default driver is set to the type of database being created. Visio will allow one to create a variety of Databases (FoxPro, Access, SQL to name a few). When the driver is set incorrectly the data types displayed are not necessarily the data types available for a given database. To set the default database drivers, first select the database menu item, options and then drivers. Pick the desired driver and click the set as default button.
An error that can be difficult to detect is duplicate object names. Depending on the Visio database options when a table is deleted from the diagram it may not be deleted from the model. Although the tables' windows allow one to see all the listed tables, relationships can persist when deleted from a drawing. Visio saves the objects for a drawing regardless of if they are displayed or not. There is an option to prompt whether an object should be deleted from a model or not. To set these options go to the database menu, options, modeling. On the logical diagram tab check the ask the user what to do. Also, check the ask the user what to do option on the ORM diagram window. If an object is deleted from a drawing Visio will prompt on deletion and ask whether it should be removed from the model.
Adding a Table
To add tables to a drawing drag an entity from the entity relationship toolbar to the drawing page. Once it is on the page the database properties window will appear. In this window the table can be named, commented and fields/columns can be added. To change the column names double click on the column name and edit the name. The data type can be selected from the drop down list. In most cases you will need to create a data type if it does not yet exist in the database model. This is where the default database driver is important. When a new data type is selected you are given choices based on the selected database driver.
Once the columns have been entered or as you enter each column they can be edited. From the column tab on the database properties window click edit. The column properties window allows you to update additional information about a column. This is a good place to add descriptions to the notes section of the columns.
Adding a Relationship
One of the main reasons to plan the design of a database is to see the relationships. Once the relationships are planned it makes importing data and populating the database easier as foreign key checks will be in place. To add a relationship, select the primary key for each table. In this case the primary keys would be the intTestID field in each table.
Next drag a relationship object from the entity relationship window on the left hand side of the Visio work area to the drawing area. Each end of the relationship arrow has a handle, which can be dropped on a table. Drag the end with the arrow to the table which has the required value and then drag the end without the arrow to the table that will contain the Foreign Key reference to the primary key.
At this point there are two tables with a relationship to one another. The database properties window displays relationship information pertinent to the two tables. In this window the referential integrity, the type of relationship (one to many, etc.) and notes can be added for each relationship.
Error Checking the Model
As the database is being developed it is useful to error check. Running error check will analyze the variable types, tables and relationships to determine if there are any problems with the current design. The error check looks at variable types based on the database driver. To error check select the database menu item highlight model and then error check. An output window will display any of the error messages. Once any error messages have been corrected the database can be generated.
Generating the Database
After error checking it is time to create the database. Clicking on the database menu item and highlighting generate will start the process. The screen of the wizard asks whether a script should be created, a database should be created, and if a copy should be stored with the model. In most cases creating the database and storing a copy of the model will suffice. The copy of the model is useful for updating the model or database. The next screen connects Visio to an existing database or allows the database to be created using an interface resembling the Data Sources (ODBC) control panel. Clicking next will show the physical tables that will be created clicking next again will create the database.
Updating the Database
If the "save changes in database" box is checked, the database model and the database diagram can be synched using the update command. The update command is under the database menu item. This can be useful if a number of changes have been made to the model or the database. Also, the database structure can be updated even if the database contains data. It is good practice to backup both the model and the database at regular intervals.
Maintaining documentation on projects can be difficult. Tools like Visio that allow one to build documentation into the intial creation of the database facilitate the documentation process. After the database is built and updated, maintaining the documentation using Visio is much easier than trying to use a spreadsheet and word processor document. Also, the ability to sych between the database and the documentation program saves a lot of time, especially after changes are made to a database and the documentation needs to be updated.