I think ane of the dirty secrets in the software business concern is how LITTLE feedback we get from our end-users. Yous know they are out there though, gritting their teeth, cursing the 24-hour interval someone trusted you with a keyboard and mouse. Some of this software is very expensive to boot, so ane would wait these users would be very vocal with their feedback.

In authenticity, I reckon nosotros only simply hear from a very small percentage of users. This sometimes can be masked with the volume and fervor that the vocal users bring to the table. For this reason, whenever I exercise hear from an end-user, I try to accept a few minutes to go over their wish lists before I turn them down (for those of you new to this blog, that last part was a joke.)

Ane of the products I work with is Oracle's data modeling tool, Oracle SQL Developer Information Modeler. It'due south free, and it's also available within of our database IDE, SQL Programmer. Yesterday we got in a asking for some improvements to the tool. Per usual, the developer replied that all that stuff is already 'in in that location.' Plainly the user just needs some gentle guidance on how to get the results they desire.

The user defenseless my attention though, as they shared the aforementioned disdain that I have – mixed instance object names in the database. This is a necessary evil in the Oracle world due to the ANSI SQL standard. I'd quote it directly, but patently you have to pay money to read this matter.

So Why are Mixed Case Object Names Evil?

By default, all commands and object names in Oracle is case-insensitive – kind of like filenames in Windows. The only time case really matters is when you're dealing with data. This has led to many software products to ASSUME that your object names are besides case-insensitive. BUT, since the ANSI standard requires support for mixed-instance object names, e.g. CREATE or Supervene upon TABLE 'TaBlE_NaMe'…, then the Oracle engine must of course also support this. The trouble surfaces when y'all try to collaborate with a table or cavalcade that is case sensitive and the program doesn't realize or support this. Assumptions ever lead to bug. Every bit the years take gone by, most developer take go more savvy to this and are careful to quote object names when necessary.

You of course take the students and developers that read that Oracle CAN support mixed example object names and and then decide that they MUST endeavor this out. Ugh++

Just because Oracle supports mixed case object names, doesn't make information technology any more fun. And you lot desire to talk well-nigh hard to read DDL scripts? Calculation quotes to everything is akin to trying to read XML – all that actress text simply makes my brain bleed. Now I realize that many of you might think I'chiliad crazy, and I respect that. Merely I am guessing that nigh of the Oracle folks are in my camp. Do y'all know how to spot an application that has been ported from SQL Server to Oracle? That's correct, everything's quoted. It's non wrong or right, but I merely can't stand up seeing it in Oracle.

So how can SQL Developer Information Modeler help?
I am going to go out of my manner to create a couple of tables that would serve as evidence for the prosecution in an Oracle courtroom of law. Thankfully, the modeler engine is smart enough to hold my hand and keep me out of trouble for the most part.

Wait ma, no FKs, mixed instance object names, and reserved words!

Let's effort to place what's wrong with this model. I will say that I accept seen all of these 'mistakes' committed in many production applications. When I meet them, I immediately retrieve – well at that place goes a developer who gets applications merely doesn't know squat nigh relational databases. Perhaps this isn't fair, just my gut isn't the most polite part of me.

  • The mixed case object names – what purpose does this serve really? Once again, I'k talking well-nigh working in a traditional Oracle environment
  • A table chosen 'Table' – don't laugh!
  • A column chosen 'column'
  • No foreign fundamental(south) rant

If we preview the underlying DDL that SQL Developer will generate to publish this model to an Oracle 11g database, here is what we see:
[sql]
CREATE TABLE MiXed_CaSe_TaBlE
(
BLAH_ID INTEGER Non Zippo ,
Text_And_Such VARCHAR2 (1024 BYTE)
) ;

Annotate ON COLUMN MiXed_CaSe_TaBlE.BLAH_ID IS 'this is my key infant';

ALTER Tabular array MiXed_CaSe_TaBlE
Add CONSTRAINT MiXed_CaSe_TaBlE_PK PRIMARY KEY ( BLAH_ID ) ;

CREATE TABLE "TABLE"
(
"Column" VARCHAR2 (100 BYTE) ,
Blah_ID INTEGER ,
ID INTEGER NOT Zip
) ;

Alter Tabular array "Table"
ADD CONSTRAINT TABLE_PK Chief Fundamental ( ID ) ;
[/sql]

I want to stress that this is ALL generated with the out-of-the-box, default settings.

'CREATE Table MiXed_CaSe_TaBlE' will result in a tabular array in the database that is NOT instance sensitive. SQL Developer recognizes that we can get abroad with not quoting this object proper name. Thank you!

'CREATE Tabular array "TABLE"' is a dissimilar beast. Since the word 'table' is a reserved give-and-take, it MUST be quoted. To admission information technology via SQL, it volition always need to be quoted. Just…

Shouldn't the tool Also be smart plenty to warn me that I don't know what I'thou doing?

SQL Programmer comes out of the box with some design and naming rules and conventions. If you apply the rules to your model, you'll see what violations are triggered. I would prefer these rules burn down as each entity is designed, but I might just need to go read the help again 😉

Hey, don't phone call a tabular array, 'Tabular array!'

Can it aid me with databases missing foreign keys too?

Yes, we can infer or presume a relationship exists when ID fields take matching attribute names in other entities. To see this, use the 'Discover Foreign Keys' characteristic. You can read more than about this in Kris' weblog here. He shows how you can build an ERD based off of views instead of tables.

Missing Foreign Keys? Nosotros tin guess them for you.