Are you tired of people finding errors in your data models once they go to build an application on top of that fine database you designed for them?  Would you like to be in on a secret that others have to spend thousands of hours in the gym to accomplish?  Well I have the solution for you.  All you have to do is call in the next 7:00 minutes with your credit card number handy.

 

Hold on.

 

I first heard about this trick during one of my EDW sessions on database design errors.  I've used it ever since to stop myself from making a really silly error in my data models. This trick is about solving the problem of defaults. No, not database defaults.  ER/Studio defaults. 

 

Defaults are great.  They allow you to add entities and attributes quickly.  So quickly it's easy to forget that they get set whether you realize they've been set or not.

 

Let's look at one default that causes a lot of pain for data architects, modelers and DBAs:  the default datatype for your model.

Model Option: Default Datatype

Did you even realize there was a default datatype for each of your data models?  Let's take a look at what your default is set to.  Open a model and go to MODEL, MODEL OPTIONS.

 

 

 From there you'll see a collection of settings that apply to your model:

 

 

In the upper middle, you see a default datatype of CHAR(10).  That's a perfectly fine datatype.  For data that is character and exactly 10 characters wide.  How many attributes/columns do you think fit that pattern?  Do you even use CHAR() as a type anymore? So it's a fine datatype.  But it's easy, too easy, to miss that it was never set correctly.  That's "da fault" with defaults.  They are easy to just "set it and forget it".

 

The One Weird Trick

I used to suggest you set the default datatype to CHAR(-1).  Yes, negative.  That's because even DDL generation would fail on that.  So your mistakes never made it outside your cubicle.  But someone at Embarcadero got smart and now you can't enter a negative number:

 

 

(I will be calling them to let them know that -1 is a perfectly fine integer, but that's for another post)

Since we can no longer enter a negative number, we need to get more creative.  You could enter a weird datatype like XML or PICTURE (depending on your target DBMS), but I still think it's better to stick with CHAR and a very unique number that would not likely be chosen for a real data type length. By sticking with CHAR, your DBAs and Developers will be knocking down your imaginary cubicle door to let you know you've got a lousy datatype, no matter what the number is set to.  Trust me on this.

 

One good value is 7734 which is calculator spelling for "HELL", certainly what you are trying to avoid by using this one weird trick.

 

 

My favourite, though, is 50538, which spells "BESOS", Spanish for "kisses".

 

 

 

At this point you have set your model default data type.  You haven't yet created any attributes that use it.  If you create a new attribute, you'll see that it has automatically been set to whatever you set in the MODEL OPTIONS. 

 

Now when you see that one weird datatype on an actual entity, you'll know that someone forgot to choose an actual datatype.  And once you generate DDL, you could search for those numbers. You could even write a macro searching for that use.

 

 

One Weird Trick in Your Data Model Diagram

One of the reasons like 50538 is that when I am reviewing a diagram, I see "kisses" where I've made an error.  Who doesn't need kisses when they make a mistake?

 

 

So now you have this one weird trick.  It's up to you whether you see hell or kisses.