Set and edit data types in Database Model diagrams

Set data types for the columns in a database model table

1. Double-click the shape for the table that has the columns that you want to set data types for.

2. In the Database Properties window, under Categories, click Columns.

3. Click the cell in the Data Type column that you want to change.

4. Click the down arrow next to the current data type and choose a different data type from the list.

Choose between portable and physical data types

Portable data types are generically defined types that map to similar, compatible physical data types in different database systems. Physical data types are the data types the target database supports. For example, if you have the driver set as Access, any data types available in Microsoft Office Access are available as physical data types in your model.

1. Double-click the shape for the table that has the columns that you want to set data types for.

2. In the Database Properties window, under Categories, click Columns.

3. Below the list of columns, click either Portable data types or Physical data types.

   • Portable data types are generically defined types that map to similar, compatible physical data types in different database systems.

   • Physical data types are dictated by the database driver set for the model. To understand more about the data types for your driver, see the documentation for its target database management system (DBMS).

Change which set of physical data types is available

Different database management systems support different physical data types. For example, if you have the driver set as Access, any data types available in Access are available as physical data types in your model. If you don't see the data types that you expect, you may need to change the driver that you have set in the Driver Options dialog box.

To use the set of data types for the DBMS in your model, set the drivers to that system in the Driver Options dialog box.

1. Determine which database driver is set for the diagram.

   • Double-click a table, and in the Database Properties window under Categories click Columns.

   • Look below the list of columns, beside the choices for portable and physical data types.

   • Note the target database management system that is shown in parentheses.

2. Change the target database driver.

   • On the Database menu, point to Options, and then click Drivers.

   • On the Drivers tab, select the Visio driver for your target database management system.

   • Click Setup and select the check box for the appropriate ODBC driver.

Create a user-defined data type

User-defined data types are custom data types that you can create and reuse in the database model they are created in. This might occur, for example, if your table has a column with a number data type for asset tracking. But you know that you may be changing from a number to character based asset tracking system. If you use a user-defined data type you can change its properties from number to character at any time. This changes all affected columns with that type, but doesn't change any other columns that have a number data type.

1. On the Database menu click User Defined Types.

2. In the User-Defined Types dialog box, click Add.

3. In the Add New User-Defined Type dialog box, enter a name.

4. To base your new data type on an existing data type, select the Copy From check box, and then select the name of an existing user-defined data type.

5. Click OK. Specify portable data type characteristics for the data type.

6. Click OK.

Set a collection type for a column

Collection types allow multiple values to be stored in a field. For example if you have a table with information about music, you may want a collection type for genre with Classical, Popular, and Folk. Collection types can optimize the performance of a database by storing data in a single entity instead of using foreign keys and secondary tables.

1. Double-click the table with the column you want to specify as a collection type.

2. In the Database Properties window, under Categories, click the Columns.

3. Click the column you want to set as a collection type, and then click Edit.

4. In the Column Properties dialog box, click the Collection tab, and then choose one of the following collection types:

   • Unordered, Non-Repeating Group Of Values (Set) Allows population instances of a field in a column to have multiple values, but each value must be unique. The values do not have any associated sorting criteria; therefore they are unordered.

   • Ordered Group Of Values (List) Creates an ordered collection of elements that allows duplicate elements. Each element in the collection has an ordinal position. An ordinal position is a position by which a value can be accessed. Because two values can be the same, they are differentiated by their ordinal positions.

   • Unordered, Repeating Group Of Values (Multiset) Creates a collection of elements that can have duplicate values. The elements do not have ordinal positions.

5. Click OK to close the Column Properties dialog box.

Create a composite data type with the Type shape

The Database Model Diagram template supports both relational and object-relational database models, so you can work with both simple and composite data types. Simple data types, used by both relational and object-relational databases, support one data value per column. Object-relational databases also support composite data types, in which a column can contain multiple values or fields; each field can have a different data type. For example, you might define an address as a composite data type that includes columns for street, city, state, and postal code.

1. Drag a Type shape from the Object Relational stencil into your database model diagram.

2. Double-click the Type shape to open the Database Properties window and then, under Categories, click Fields.

3. Click in a blank line and begin typing a new Physical Name to add a field.

4. Specify a data type by typing or by selecting one from the list for that field.

5. To prevent null values, select the check box in the Req’d column.

6. To define the Type as Named, Distinct, or Domain, under Categories, click Definition.

7. Select the options you want:

   • Named row type Select to specify that the type is not an alias of any other type.

   • Distinct type Select to specify that the type is based on another type and has the same representation as the type on which it is based, but is an entirely distinct type of its own.

     If you select this option, you will not be able to select options on the Fields category.

   • Domain Select to specify that the type is an alias of another type; it is an indirect representation of the same type.

     If you select this option the Alias Collection Type option becomes available and you will not be able to select options in the Fields category.

   • Alias collection type (visible only if Domain is selected) Select an option to specify whether an attribute's value is a collection of a single value, set, list, or multi-set. In relational databases, all attribute collection types are single values. Object relational databases allow you to specify the additional collection types.

Assign a composite data type to a column

If you create a composite type with the Type shape you can assign it to a column, thereby defining the column as having all the columns of the type. For example, you may have a table with columns ID, Name, Address, where Address is a type composed of the columns Street, City, State, and Postal Code.

1. Double-click the table in which you want to use the composite data type.

2. In the Database Properties window, under Categories, click Columns.

3. Click the down arrow next to the current data type and choose another from the list.

Create a typed table

If you create a composite type with the Type shape you can assign it to a table, thereby defining all the columns of the table based on the type. For example, if you have a type with columns One, Two, and Three, you can assign that type to the table and the table automatically contains those columns.

1. Drag an Entity shape onto the model diagram.

2. Double-click the table and in the Database Properties window, under Categories, click Definition.

3. Click the down arrow next to the current data type and choose another from the list.

Can't find the database modeling features?

It is most likely that your edition of Visio doesn't include the features you are looking for. To find out which edition of Visio you have, click About Microsoft Office Visio on the Help menu. The name of the edition is in the top line of text in the dialog box.

Visio Standard does not include the Database Model Diagram template.

Visio Professional and Premium editions support the reverse engineering features for the Database Model Diagram template (that is, using an existing database to create a model in Visio) but it does not support forward engineering (that is, using a Visio database model to generate SQL code).

If you are using Visio Plan 2 and want to learn about how to engineer an existing database into a database model, see the topic, Reverse engineer an existing database.