How To Change Data Type In Visio

Information Modeling in Microsoft Visio

Somewhat surprisingly Microsoft Visio is a true Rapid Awarding Development tool as envisioned by James Martin. While Visio can perform many Rapid Awarding Evolution tasks (including Code Generation from UML diagrams), this commodity provides an in-depth view of how to utilise Microsoft Visio for data modeling.

Introduction

Microsoft Visio, Enterprise Architect edition, is a powerful software evolution tool for numerous applications. In addition to a basic line-to-shape cartoon tool, Visio tin can model UML, generate and contrary engineer interfaces in code (no implementation is generated, dissimilar Automated Architecture'southward Blueish Ink tool), and as is the focus of this commodity: provide a fairly potent tool for information modeling. Visio is non peculiarly intuitive or well documented, and is occasionally buggy, but once you get used to information technology; it is very useful (and relatively inexpensive). In terms of information modeling Visio can:

• Create new information models from scratch
• Contrary engineer models from existing databases
• Generate new databases (in a number of different Database Management Systems) from models
• Update existing databases from models, keeping all data in tact
• Help in maintenance of multiple database versions, keeping all in sync with the model

I've used it extensively with SQL Server 7, SQL Server 2000, SQL Server 2005, and Oracle 8i, but it should work with any ODBC accessible database.

This document will go over how to utilise Visio to perform information modeling tasks and how to maintain a database with Visio, while providing tips from someone who has multiple years of experience with the tool. This document assumes a basic understanding of data modeling design concepts. For some groundwork on how to read the crow'due south feet note that Visio uses see my Web log mail service An Entity Human relationship Diagram Example

Data Modeling

The data modeling section will provide an overview of why to apply a information modeling tool, so will delve into the details of using Visio, including how to create a new model, how to modify tables and relationships, and how to comment a information model.

Why Utilize a Modeling Tool?

I know numerous developers that build databases by paw, simply of all my software development tools I believe a data modeler is past far the nigh important. A data model is like the foundation to a firm; the better the data model the less custom development piece of work in business rules and exceptions. The use of any Rapid Awarding Development tool or Code Generation tool requires a solid foundation, and a information modeling tool helps provide that by eliminating errors that are easy to brand without a visual representation of your foundation. Visual modeling tools provide many secondary benefits beyond decreasing data model defects including the ability to proceed multiple information models in sync, make on-the-fly data model changes (without loosing information), and validate models (enforce chief key requirements, warn against indistinguishable indexes, etc).

New Models

Creating a New Model

Create a new model by just selecting File -> New -> Database -> Database Model Diagram. It is of import when creating a diagram to gear up the Database Management Organization from the Drivers tab in Database -> Options -> Drivers.

Reverse Applied science

Contrary engineering an existing database involves creating a new model from File -> New -> Database -> Database Model Diagram. Next select the type of Database Direction Arrangement (DBMS) you lot want to connect to from the Drivers tab in Database -> Options -> Drivers. Side by side select Database -> Opposite Engineer and follow the wizard by selecting an ODBC data source or creating a new i. Clicking next allows yous to select which elements of the database to remember, and the following screen allows you to select which tables to retrieve (yous probably want all of them). The last screen allows you lot to automatically add the tables to the page or non. If you don't automatically add them you can manually add them from the Tables and Views pane (that tin be displayed via Database -> View -> Tables and Views).

Options

At that place are a few options I recommend setting for a new diagram. I prefer to view crow's feed to indicate one-to-many relationships instead of the default pointer. This tin be set up past selecting the "Show Crows Anxiety" checkbox on the Relationship tab from Database -> Options -> Document.

Setting the folio surface area for the purposes of printing is some other useful but not-intuitive procedure. First by going to File -> Page Setup. Select the Page Size tab. Here you tin can select Portrait or Landscape at the bottom. If you desire multiple pages the best way I've found to practice it is by selecting Custom size and inbound multiples of 8 inches and 10.five inches (giving a quarter inch border around all pages). For example three pages width-wise by 2 pages summit-wise washed landscape would exist 24" Ten 21".

I also similar to have the font smaller for tables and columns, merely unfortunately I haven't figured out how to set the default, so when adding a new table I select it and select viii point from the toolbar.

Finally (for what it's worth) I personally don't similar the default Window Pane setup. I like to have the Database Backdrop pane at the bottom of the screen with the Entity Human relationship screen small and to the left of it. Yous may discover the post-obit setup more productive than the default:

Modifying Database Tables

Table

Creating a table simply involves dragging it from the Entity Relationship pane to the design surface. You tin can modify a table's name past selecting the table and editing the name from the Definition category in the Database Properties pane. Just modify the Physical Proper noun, I've never establish a need for the Conceptual name or Proper noun space.

Columns

To modify columns but select the tabular array on the design surface then the Columns category in the Database Properties pane. Calculation and deleting columns is pretty straight forward. If you're using SQL Server you can type "int identity" into the Data Type field to get an auto incrementing field. If y'all click edit you can alter the default value and edit constraints.

Indexes

Creating a new index is notwithstanding another not particularly intuitive process in Visio. Click indexes in database properties for a table and click new. When the prompt comes up for a proper name don't enter anything and striking enter. Visio automatically names indexes for y'all and information technology I've seen it get confused if you enter something custom, so just exit it bare. Generally select "Unique Index Only", but there are probably reasons for inbound other types of indexes. Selecting the columns is pretty straightforward.

Table Visibility

Proceed in listen that the design surface is not indicative of what is actually in the model. For example if you click a table or human relationship and hit the delete key it should ask you "Remove selected detail from underlying model?" (if it doesn't prompt y'all for this make certain to select "Ask user what to do" in the Logical Diagram tab in Database -> Options -> Modeling). If yous say yes then it will truly delete the object, if you say no information technology will but brand the object disappear from the design surface, but if you regenerate the database the item will still be generated.

You can view the existent objects in the model past going to Database -> View -> Tables and Views (and you tin elevate these onto the design surface). You lot can also right click on a table and select "Evidence Related Tables" to view related non-visible tables and relationships. Hiding tables can be extremely useful for tables that are related to nearly every table in the model.

Creating Relationships

One-to-Many Relationships

Creating a one-to-many relationship is easy, but elevate a human relationship object from the "Entity Relationship" pane to the pattern surface and connect the ends to tables. Visio will automatically add foreign keys to the table with the crow's anxiety on it provided the parent table has a master key. Visio automatically names the human relationship for you lot, but it isn't smart enough to rename the relationship if you rename tables, so sometimes you'll run into errors with duplicate relationship names, these yous'll accept to set by manually renaming the relationships.

One to 1 Relationships

Ready a one-to-ane relationship just as yous would a 1-to-many relationship keeping in mind which tabular array volition exist the parent and which will be the child. Next click on the relationship and in the Database Properties pane select the Miscellaneous category and set up the cardinality to "Cypher or I."

Commenting the Model

Commenting

Visio does a great job of allowing you to comment a model and so run reports that describe the model. Y'all can comment a tabular array from the notes category in the Database Properties pane, and you lot tin can comment a column from the notes section in the Columns category. Oddly Visio puts default comments in for columns, but the default comments don't show up in the reports.

Reports

Running reports is so customizable it is a picayune overwhelming. Showtime the procedure with Database -> Report. I observe the virtually useful written report to exist the one that describes the tables and columns with their comments. Do this past selecting the "Tabular array Report". If you run this report information technology volition display a list of all tables and then follow it up with a couple pages per table describing the table and information technology's columns, etc. You tin can select the "Default To" button to select preset options for unlike subsets of data. In item the "Database Report" merely shows tables and their comments, and the "Column Summary" report just shows tables and the comments for their columns.

Note: Information technology ought to be easier than it is to consign table and column names into a program like Microsoft Excel. If the topic interests you lot see my Blog mail Export Visio Database Table Names to Excel which contains a stride-by-stride process with screenshots.

Maintaining a Database

The Maintaining a Database with Visio section will detail data model validation, how to generate a new database using Visio's "Generate" command, and how to maintain an existing database including using Visio's "Update" command.

Model Validation

Before generating you need to validate the model. Visio will find all sorts of frustrating errors, some of them legit, some of them not. Perform the fault bank check from Database -> Model -> Mistake Check. Errors volition be displayed in the Output pane which volition become visible after the cheque. You tin can double click on the error and sometimes Visio will accept you to the problem.

Duplicate Relationship Names

The near mutual error is duplicate relationship names. In this example Visio will say something like "Foreign central human relationship has the aforementioned parent and kid tables every bit ... but does not accept forward and inverse verb phrases." This occurs when at that place are two relationships between two tables that both have the aforementioned "Verb phrase" and "Changed phrase" properties. To fix the problem select one of the relationships, select "Database Properties" select the "Proper noun" category and change the values in the appropriate fields in the right-paw column (I typically just add a character to each as I have no existent utilise for verb phrases).

Identical Column Names

The most annoying trouble is identical cavalcade names (in different tables) that have different data types. The error volition exist "Cavalcade has same conceptual name as [Table].[Column], simply has a unlike information type". To fix this select one of the columns, click the Edit button to bring up a column properties dialog box, uncheck "Sync names when typing," and change the "Conceptual name" field.

Generating a Database

Generate a database from the model by selecting Database -> Generate. This will automatically run a model validation so it will display a screen with some options. Generate a text file of the DDL script is a swell idea, and you might likewise e'er check this. Generate a new database is a chip of a misnomer, the checkbox is basically asking if Visio should automatically run the SQL commands (DDL script) against the database. It's a nice convenience and I e'er bank check this, but more importantly it allows you to select the side by side checkbox "Shop current database paradigm in model." Visio keeps rail of all changes since you updated the "image" of the database and when you regenerate it will only make the new changes, then clicking this checkbox is important as it stores an "image" of the database.

The side by side screen allows you to create a new database, which I would propose against, or selecting an existing database. I would suggest ever creating the database by hand through SQL Server or Oracle and then selecting "database already exists." When you take this arroyo Visio will inquire y'all for an ODBC information source. Select an existing 1 or more than likely create a new organization data source, select your DBMS and enter the parameters to connect to the database (don't forget to set the default database to your newly created database). Finally Visio will summarize the changes information technology'll make and then when you hit finish it will generate the tables, columns, indexes, relationships, constraints, etc into your database. The results of the generated code volition be displayed in the Output Window. Review the output carefully; although Visio is pretty good, it sometimes has errors when generating and it will not tell you about the errors other than through reviewing the Output Window. You may need to manually prepare errors it encounters past reviewing the DDL script that it ran and re-running sections.

Maintaining an Existing Database

Refreshing The Model

One time you have generated a database from a model it is possible that manual changes to the database schema may become your model and the database out of sync. To relieve this problem Visio tin can review a database and place elements that accept changed and optionally update the model with the changes. Perform this performance by going to Database -> Model -> Refresh and selecting your database from the ODBC Data Sources. Visio volition compare the image in the model of what the database should look similar and the actual database and list all discrepancies. You tin select discrepancies and choose "No Change" or "Refresh Model." Fixing the discrepancies should update the model, simply I have occasionally encounter problems where I have needed to just note the discrepancies and manually make the changes and then re-run the refresh operation.

Updating the Information Model

There are two ways to update a data model and sometimes 1 is more than appropriate than the other. Visio is pretty crummy when it comes to making modifications to SQL Server tables that accept fields of type "text," only otherwise it does an excellent job of modifying a database schema while maintaining the information in the database. In I get with using Visio'south update performance first and if there are errors so I'll brand the changes manually.

Using Visio's Update Functioning

Making changes using this approach involves making the changes in the model (calculation rows, changing relationships between tables, etc), and so selecting Database -> Update. E'er select the first selection "Generate a text file with the DDL script" as y'all can refer to this if there are any errors. "Update the Database" is the option I always become with for sheer laziness as it runs the script confronting the database for you and displays the output on screen. "Notice Changes in the Database" is an option that you lot tin cheque if you are worried that the database might non exist in sync with the data model. Finally, I oasis't used the "Log Conflicts to file" option, but it sounds useful and I should probably start.

If you lot select "Update the Database" the next screen has you select the ODBC Data Source to get to the database. If you selected "Observe Changes in the Database" the adjacent screen will allow you to resolve conflicts. It gives y'all options to update the model from the database, update the database from the model, or ignore the conflict. I haven't had issues with these options, and have constitute the observe changes useful on a number of occasions. Finally yous will be presented with a list of changes that Visio volition make to the database and to the model. Click Finish with the options I normally pick and the DDL Script volition be displayed and run confronting the database and results will be displayed in the Output Window. Review the Output Window carefully, errors are more frequent with the Update operation than with the Generation performance. When errors occur (this happens occasionally) you will demand to review the DDL script and re-run sections to become everything to piece of work. I will briefly draw Visio'southward procedure in the DDL script to handle updates:

For any table that has changed Visio will remove all foreign key referential constraints ("change tabular array drop constraint"). Visio will so create a duplicate of the table for fill-in purposes and name it "[TableName]_IM0." Visio will then copy all data into the backup table, delete the existing table, recreate the tabular array with the new changes, and move the information from the fill-in table to the newly created tabular array. When data is moved from the backup tabular array to the new table Visio volition oftentimes gear up a default value of 1 for new required fields. Note that this will neglect for required foreign keys that do not have a parent record number "1" to indicate to. Finally Visio deletes the fill-in tabular array and then recreates all referential constraints (alter table add together constraint).

When errors occur they will sometimes cascade to all operations on all tables and sometimes just remain local to the tabular array that is being changed. When errors do occur they often leave _IM0 tables sitting around that can usually be deleted. Once the operation is consummate and any errors resolved I recommend refreshing the model to brand sure everything worked and the model and database are equivalent.

Transmission Changes

Making changes manually is the best rout to apply when any table that is involved in a change is in SQL Server and has a field of blazon text, but it is occasionally useful when errors occur during the Visio's update operation. I will avoid making manual changes when relationships change or when there are more than than a couple changes to make. Incidentally I detect making schema changes to be much easier in SQL Server and I try to avert making manual changes in Oracle without a tool such as TOAD.

The manual procedure is pretty straight forward: make the changes in the model, make the changes in the database, and run a refresh to make sure they are equivalent.

Summary

In summary I would never do database blueprint work without a data modeling Rapid Application Development tool, and Visio is one that is quite powerful.

Source: http://www.blueink.biz/DataModelingVisio.aspx

Posted by: williscappiket.blogspot.com

Share this post