The Data Warrior

Quick Tip: Adding a Custom Design Rule to Oracle Data Modeler

As most of my readers know, I use Oracle’s SQL Developer Data Modeler to do all my data modeling.

It has a lot of great features that are documented online in various places. One of those is Design Rules.

Design Rules (Tools -> Design Rules -> Design Rules) include a host of predefined quality checks on many of the objects created in a data model. This includes entities, attributes, relationships, tables, columns, constraints, indexes, etc.

You select the rules, or group of rules, and hit “apply”. The rules then check your model and tell you, object by object, if there are any issues.

Some issues are warnings. Others show up as errors. A error generally means that if you try to create DDL, that DDL will fail when you try to execute it an Oracle database.

One nice feature is that you can double click on a highlighted issue and go directly to the object with the issue so you can fix it.

An example of a design rule check is the length of the table name. Oracle still has a limit of 30 characters (Why????)  on object names, so there are design rules to check for that.

Pretty useful really.

For the Data Vault model I am currently building, we are trying to generate lots of stuff based on the table name (i.e., surrogate key sequence and some PL/SQL load procedures, but that is a much longer story). As a result we discovered we need to limit the table names to 26 characters because we need to use the table name as a root that has prefixes and suffixes added in certain cases.

Too bad the built in design rule is set to 30.

And there is no way to modify that built in rule (verified on the OTN Forum).

So the solution is to create a Custom Rule (Tools -> Design Rules -> Design Rules). The intrepid Philip from the Oracle development team kindly provided me with the base code to create the rule I needed. I was able to take his code, and use the one custom rule that comes delivered as a template, to make a new rule that did the check I wanted.

Here is the code:

var ruleMessage;
var errType;
var table;
//define
function checkName(table){
result = true;
ruleMessage=””;
if(table.getName().length()>26){
ruleMessage=”Table name over 26 characters”;
errType=”Error”;
return false;
}else{
return true;
}
}
//invoke it
checkName(table);

I won’t explain the code (you can figure that out if you like), but it does work as I wanted, so I am a happy camper. 🙂

Now after I add new objects to the model (e.g., hubs, links, satellites), I just run this rule to find any that are too long. Then I fix the table name and reapply my constraint naming standards (another very useful and simple utility in the tool). After that I can generate the DDL and build the objects in the db, then re-run our code generator.

If you have not looked at the features of SDDM, time to look.

Happy Modeling!

– Kent

P.S. To see more article about SDDM, check out Jeff Smith’s blog (in my blog roll).

P.P.S. Don’t forget to follow me on twitter @KentGraziano. I retweet a lot of Jeff’s article there. 😉