Chat now with support
Chat with Support

Toad Data Modeler 7.0 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Create New Project Reports Reverse Engineering Scripting and Customization About Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Amazon Redshift 1.0 IBM DB2 LUW 9.5 IBM DB2 LUW 9.7 IBM DB2 LUW 10.1 IBM DB2 LUW 10.5 IBM DB2 LUW 11.1 IBM DB2 z/OS 10 IBM DB2 z/OS 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 EDB Postgres Advanced Server 10 Microsoft Access 2007/2010 Microsoft Azure SQL Database V12 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 Microsoft SQL Server 2017 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 Oracle 18c Specifics - Oracle 12c Release 2 PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 PostgreSQL 10 SQLite 3.7 Sybase ASE 15.5 Sybase ASE 15.7 SAP ASE 16.0 Sybase IQ 15.2 Sybase SQL Anywhere 11 SAP SQL Anywhere 17 Teradata 13 Vertica Database 8.0
Copyright Legal Notices

User Data Types

In Toad Data Modeler, you can define your own data types (in case the database platform you use supports this feature).

To add a user data type

Select Model Menu | Model Items | User Data Types and click Add in the User Data Types dialog.

Example: User Data Types listed in User Data Types dialog

To edit a user data type

Double-click the selected data type or click Edit in the User Data Types dialog.

Example: The User Data Type Properties dialog (Oracle 10g)

General Tab

Description

Caption

Logical user data type name

Name

Physical user data type name

Schema

Schema selection box

Type

This option is available in Oracle models only. Contains specific types of user defined types.

Generate

Select this option to generate the user data type in final SQL script.

Generate SQL Only

This option is available only for Oracle user data types.

If this checkbox is selected, only the SQL code that you manually entered in appropriate tab (Object Type or Varray Type or Nested Table Type) will be generated in final DDL script.

Note: All changes in your selected user data type will be automatically applied to all attributes of this data type.

To Do Tab

On this tab, you can write some tasks related to the user data type.   

TIP: To see all To Do tasks in your entire model, select Model Menu | To Do.

Used In Tab

All objects where the user data type is used are listed here.

Permissions Tab

On this tab, you can assign a User or User Group permissions for selected user data type.

Notes Tab

Write notes related to the user data type.

Object Type Tab

See example of specification and definition in the following screenshot.

TIP: Click OK+Add to create another user data type.

Example: User Data Type Properties dialog | Object Type tab (Oracle 10g)

To assign a User Data Type to an attribute

  1. Open the Attribute Properties dialog of the selected attribute | General tab.
  2. From the Data Type box, select the required user data type.

TIP: You can reach the User Data Types dialog from the Attribute Properties dialog - see the small icons next to the Data Type box.

Note:

  1. To copy user data types, use CTRL + Drag&Drop techniques.
  2. To move user data types, use Drag&Drop techniques.
    You can copy and move your user data types:
  • In User Data Types dialog (Model Menu | Model Items)
  • Between Model Explorer and User Data Types dialog
  1. To delete user data types:
  • Go to Model Menu | User Data Types, select a user data type and click Delete.
  • In Model Explorer | User Data Types folderRight-click and select Delete Item.

Note: When you delete an user data type, the changes will reflect in all attributes that had this type assigned. There, the user data type will be replaced with data type that it was originally created from. To find out which attributes use a specific user data type, go to User Data Type Properties dialog | Used in tab.

Dictionary Types

In Toad Data Modeler, you can define dictionary types. Dictionary is an alias of data type (must be supported by database).

Note: Oracle databases support user data types only, Microsoft SQL Server supports both user data types and dictionary types.

To add a dictionary type

Select Model Menu | Model Items | Dictionary Types and click Add in the dialog.

To edit a dictionary type

Select Model | Model Items | Dictionary  Types and double-click the selected dictionary type or click Edit.

General Tab

Description

Caption

Logical dictionary type name

Name

Physical dictionary type name

Data Type

Data Type selection box

Note:

  • Other options on the General tab vary according to the current database platform. Options specific for your database can be found in the "Databases" chapter.
  • All changes in your selected dictionary type will be automatically applied to all attributes of the dictionary type.
  • To Do Tab

    You can enter To Do tasks related to the object here.

    Note: To display all To Do tasks, select To Do from Model Menu.

    Used In Tab

    All objects using this dictionary type are listed here.

    Notes Tab

    Tab for notes related to the dictionary type.

    To select a dictionary type for attributes

    1. Open the Attribute Properties dialog of a selected attribute.
    2. In General tab, select the dictionary type from the Data Type dropdown menu.
     

    Note:

    1. You can reach the Dictionary Types dialog also from the Attribute Properties dialog - see the small icons next to the Data Type box.
    2. Click OK+Add in the Dictionary Type Properties dialog to apply changes and create another dictionary type at once.
    1. To copy dictionary types, use CTRL + Drag&Drop techniques.
    2. To move dictionary types, use Drag&Drop techniques.
      You can copy and move your dictionary types within a model and between models of the same and different databases:
    • In the  Dictionary Types dialog (ModelMenu)
    • Between Model Explorer and the Dictionary Types dialog
    1. To delete dictionary types, select:
    • Model | Dictionary Types | Select a dictionary type and click Delete.
    • Model Explorer | Dictionary Types folder | Right-click and select Delete Item.

    Note: When you delete a dictionary type, it will be also removed from all attributes in which it was used and replaced with the data type that it was based on (e.g. deleting dictionary type based on char data type will cause all attributes to revert back to the char data type). To find out which attributes will be changed when you delete a dictionary type, open the Dictionary Type Properties dialog | Used In tab.

    Domains

    Domain is another dictionary item in Toad Data Modeler. Domains only have logical meaning and are not generated. If a domain is used in a attribute, only the values of the domain are transferred to the attribute and are generated.

    To add a domain

    Select Model Menu | Model Items | Domains and click Add in the Domains dialog.

    To edit a domain

    Select Model Menu | Model Items | Domains and double-click the selected domain or click Edit in the Domains dialog.

    Example: The Domain Properties dialog (Oracle 10g)

    General Tab

    Description

    Caption

    Logical domain name

    Name

    Physical domain name

    Data Type

    Data Type selection box

    Note:

    1. Other options on the General tab vary according to the current database platform. Options specific for your database can be found in the "Databases" chapter.
    2. All changes made to your domain will be automatically applied to every attribute which uses the domain.

    Check Constraints Tab

    On this tab, you can add, edit and delete check constraints of a domain.

    To Do Tab

    On this tab, you can write some tasks related to the domain.

    Used In Tab

    All attributes with this domain are listed on this tab.

    Double-clicking any attribute will open its Attribute Properties.

    Notes Tab

    Tab for notes on the domain.

    To select domains for attributes

    1. Open the Attribute Properties dialog of the selected attribute | General tab.
    2. From the Domains box, select a domain.

    Note:

    1. To copy domains, use CTRL + Drag&Drop techniques.
    2. To move domains, use Drag&Drop techniques.
      You can copy and move your domains within a model and between models of the same and different databases:
    • In  Domains dialog (Model menu)
    • In Model Explorer | Domains folder
    • Between Model Explorer and Domains dialog
    1. To delete domains, select:
    • Model | Domains | Select a domain and click Delete.
    • Model Explorer | Domains folder | Right-click and select Delete Item.

    Note: Deleting a domain will remove it from all attributes that are using it. To find out which attributes they are, open the Domain Properties dialog | Used In tab.

    Export/Import Dictionary

    Toad Data Modeler allows you to use dictionary items also in other models. You can simply export all of them to the .TXI file, and then import them to any model at any time. You can save the .TXI file where you want, no default path is defined.

    Dictionary items are:

    • User Data Types
    • Dictionary Types
    • Domains

    Domains

    They have only a logical meaning. They are not generated in DDL/SQL script. If a domain is used in attribute, only values of the domain are transferred to the attribute during the DDL script generation process.

    User Data Types

    They are data types defined by users and can be generated in final DDL script.  User data types are not derived from data types.

    Dictionary Types

    They are data types that are derived from other data types.  They can be generated in final DDL script.

     

    How to Export/Import Dictionary

    You want to use dictionary items of Model A in Model B:

    1. Open Model A.
    2. Select Model | Export Dictionary.
    3. Save the .txi file.
    4. Open Model B.
    5. Select Model | Import Dictionary.
    6. Select the .txi file and click Open.

    Note:

    • Domain Check Constraints are imported/exported too.
    • It's not possible to make selection of the dictionary items for the import/export. All the dictionary items are always imported/exported at one jump.
    Related Documents