Ensuring Consistent Domain Definitions Using User Datatypes
Go Up to Working with the Data Dictionary
User-defined datatypes are a powerful mechanism for ensuring the consistent definition of domain properties throughout a data model. You can build a user-defined datatype from base datatypes, specifying width, precision and scale, as applicable. In addition, you can bind rules and defaults to the user-defined datatype to enforce domain integrity. After creating a user-defined datatype, you can use it in attributes, table columns, and domains without needing to define its underlying definition each time. User-defined datatypes are particularly useful with commonly-referenced table columns in a database, such as PhoneNo, ZipCode or PartNo.
The central maintenance of user datatypes eliminates the tedium and potential for errors users can encounter when manually editing each referenced table column. For example, you can define a surrogate key in the Part table, PartNo, as an integer for efficiency. You have defined a user datatype, partnumber, to represent every use of PartNo throughout the database design. Subsequently, you learn that the data to be converted from a legacy system contains some part numbers in character format. To accommodate the change, you only need to edit the definition of partnumber from an integer to a character-based user datatype. ER/Studio Data Architect automatically converts all table columns using partnumber to the new definition.
Although only a few database platforms currently support user-defined datatypes, ER/Studio Data Architect extends their utility to all database platforms in the physical model. ER/Studio Data Architect automatically converts user-defined datatypes to their base definitions when generating SQL code for any database platforms that do not provide native support.
- For SQL Server, Sybase, and IBM DB2 only, the CREATE statements of the user datatype can be included in the DDL when generating the database.
Create and Edit User Datatypes
- On the Data Dictionary tab, right-click the User Datatypes node and then select New UserDatatype.
- Define the user-defined datatype as required and then click OK to complete the editor.
- Once created, you can edit the naming standards template by double-clicking its object on the Data Dictionary tab to launch the editor.
The following describe options that require additional explanation:
UDT tab
- Apply nullability to all bound columns: If you indicate Yes Allow Nulls, this check box is available. When you are editing the user datatype and you change this selection from what it was previously, selecting this check box broadcasts the change to all objects bound to this user datatype.
- Default Binding: Optional If you want to bind the user datatype to a Default, select it from the list.
- Rule Binding: Optional If you want to bind the user datatype to a Rule, select it from the list.
Attachment Bindings tab
Bind an external piece of information, or attachment to the user datatype. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary and must be applied to the user datatype before they will display on this tab. For more information, see Attaching External Documents to the Data Model.
Binding Information tab
Select which object classes and/or specific objects can use this user-defined datatype. You can override this setting using the Attributes or Columns tab of the entity or table editor, respectively, or on the Datatype tab of the domain editor.
- a user datatype and change its definition, ER/Studio Data Architect automatically updates your entire model so that all affected objects use the new definition.