Browsing articles tagged with " Query Tool"

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).

MOVIE Entity

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:

 

MODEL, MODEL Properties

 

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

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:

Movie Entity 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:

ERwin Query Tool

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

Subscribe via E-mail

Use the link below to receive posts via e-mail. Unsubscribe at any time. Subscribe to www.datamodel.com by Email


Categories

Archive

UA-52726617-1