Setting the Default Datatype in ERwin Data Modeler

Nov 15, 2010 // by Karen Lopez // Blog, Data, Data Modeling // 1 Comment

Out of the box, CA ERwin Data Modeler has a default datatype of char(18). This default is used when you create an attribute or column in a data model and don’t specifically assign a datatype to that attribute.

I don’t know this for a fact, but I’m guessing the developers of ERwin chose this odd length so it would stand out as being a bit odd, reminding modelers to choose a “real” datatype and length at some point. You can see in the figure below that I’ve added an attribute named test to the MOVIE entity, but did not assign a datatype, so ERwin gave it the default datatype of char(18).

However, one the problems with this is that it is still not odd enough. One of the tips I picked up while speaking at the BeNeLux Modeling User Group was that you can set the default datatype in ERwin to something especially funky, char(-1). That datatype is certain to:

Stand out in both the data model diagram and any DDL
Not generate valid DDL
Fail any DDL testing
Make a DBA really mad
Motivate database designers to set real datatypes

Sounds to me like the perfect datatype for being a default.

Defaults in ERwin are model-specific, meaning that you set them on a .erwin file basis. To do this in ERwin 7.3, you must first open your model, then go to MODEL, MODEL PROPERTIES:

This will bring up the properties dialog. Choose the DEFAULTS tab.

In the Default datatype section above, you can set the default datatype to char(-1) or whatever makes sense for you project. I’ve seen some people make a good guess at the most generic datatype, something like varchar(50), but I prefer to assign an oddball datatype so that I can find these “unassigned” attributes and columns easily. Note that you can also set separate defaults for the logical and physical models. Since I’m choosing to set the default to an invalid default, I set them the same.

Having said this, I rarely create attributes manually. I usually create attributes via the domain approach, where domains have a “best guess” standard datatype that makes sense for the type of data contained in them. For instance, a domain for email address would normally be varchar(254) in a model, since that is the Internet standard for email addresses. However, attributes do get created without a domain all the time, so it makes sense to have a default datatype that meets your needs.

Having set my default datatype in my model to char(-1), when I create a new attribute and don’t assign a datatype it will be set to the default as shown in the MOVIE entity below:

The key to using an invalid datatype is to ensure that you’ve circled back and set them all to a real datatypes and lengths before you derive a new model from them, generated DDL, or compared to a database.

You can use a report or the ERwin Query Tool to find all the attributes that have this datatype:

More on the ERwin Query Tool in another blog post soon.

Tags: datatype, ERwin, Logical, Physical, Query Tool

1 Comment

Tweets that mention Setting the Default Datatype in ERwin Data Modeler « InfoAdvisors' Blog -- Topsy.com 15 November 2010 at 7:34 PM

[...] This post was mentioned on Twitter by Karen Lopez, Frank McCafferty. Frank McCafferty said: RT @datachick: Setting the Default Datatype in ERwin Data Modeler http://goo.gl/fb/bkctz < good tactic for all undecided/WIP datatypes [...]

Reply