Use domain or type dictionary or UDT for an attribute's "logical type"

Toad Data Modeler
1

(newbie question)

To learn TDM, I’m creating a logical model from an existing model published as a Javadoc-like set of HTML pages, to better see its structure in different Workspaces.

Many tables have strings which are in fact keys with a given structure, so I’d like to model those with a “logical” type of StringKey instead of as plain varchar(x) attributes.

But it looks like there are several ways to do this:

  1. As a Domain
  2. As a Type Dictionary (not with Oracle 11g?)
  3. As a User-defined Data Type

The doc is a little light on what these are individually (or I missed it), and doesn’t explain in which situation one would use a Domain vs. a UDT.

What are the pros and cons of each?
Influence on the Physical Model generation?

Thanks for any help on this. --DD

2

Hi,

in Logical Model you can only use Domain. When you do a conversion from Logical Model to Physical Model, Domains will be converted too (Domains will be available also in the Physical Model).

in Physical Model, you can use Domains, Dictionary Types and User-defined Data Types, however, every database supports different items. That’s why you cannot use Dictionary Types for Oracle.

  1. Domains: Domains have only a logical meaning and are not generated. If a domain is used in attribute, only values of the domain are transferred to attribute during the DDL script generation process.
    Example: create domain “Price” and set its definition to Decimal (5,2). In genereated script (MS SQL Server 2005) you will see:

CREATE TABLE [Entity1]
(
[Attribute1] Decimal(5,2) NOT NULL
)
go

  1. Dictionary type: Dictionary type is an alias of data type (must be supported by database).

Example: create dictionary type “Price” and set its definition to Decimal (5,2). Generated script for MS SQL Server 2005:

CREATE TYPE [Price] FROM Decimal(5,2)
go

CREATE TABLE [Entity1]
(
[Attribute1] [Price] NOT NULL
)
go

  1. User Data Types: In Toad Data Modeler, you can define your own data types (only provided that they are supported by the database you use).
    Please see the help file, section Modeling | Physical modeling | ERD | Data types and Disctionary | User Data Types.

I am not able to list Pros and Cons, it depends on your requirements and the solution is on you :slight_smile:

Regards,

Vaclav