I think one of the dirty secrets in the software business is how LITTLE feedback we get from our end-users. You know they are out there though, gritting their teeth, cursing the day someone trusted you with a keyboard and mouse. Some of this software is very expensive to boot, so one would expect these users would be very vocal with their feedback.
In actuality, I reckon we only only 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 do hear from an end-user, I try to take 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.)
One of the products I work with is Oracle’s data modeling tool, Oracle SQL Developer Data Modeler. It’s free, and it’s also available inside of our database IDE, SQL Developer. Yesterday we got in a request for some improvements to the tool. Per usual, the developer replied that all that stuff is already ‘in there.’ Apparently the user just needs some gentle guidance on how to get the results they desire.
The user caught my attention though, as they shared the same disdain that I have – mixed case 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 apparently you have to pay money to read this thing.
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 also case-insensitive. BUT, since the ANSI standard requires support for mixed-case object names, e.g. CREATE or REPLACE TABLE ‘TaBlE_NaMe’…, then the Oracle engine must of course also support this. The problem surfaces when you try to interact with a table or column that is case sensitive and the program doesn’t realize or support this. Assumptions always lead to problems. As the years have gone by, most developer have become more savvy to this and are careful to quote object names when necessary.
You of course have the students and developers that read that Oracle CAN support mixed case object names and then decide that they MUST try this out. Ugh++
Just because Oracle supports mixed case object names, doesn’t make it any more fun. And you want to talk about hard to read DDL scripts? Adding quotes to everything is akin to trying to read XML – all that extra text just makes my brain bleed. Now I realize that many of you might think I’m crazy, and I respect that. But I am guessing that most of the Oracle folks are in my camp. Do you know how to spot an application that has been ported from SQL Server to Oracle? That’s right, everything’s quoted. It’s not wrong or right, but I just can’t stand seeing it in Oracle.
So how can SQL Developer Data Modeler help?
I am going to go out of my way to create a couple of tables that would serve as evidence for the prosecution in an Oracle court of law. Thankfully, the modeler engine is smart enough to hold my hand and keep me out of trouble for the most part.
Let’s try to identify what’s wrong with this model. I will say that I have seen all of these ‘mistakes’ committed in many production applications. When I see them, I immediately think – well there goes a developer who gets applications but doesn’t know squat about relational databases. Maybe this isn’t fair, but my gut isn’t the most polite part of me.
- The mixed case object names – what purpose does this serve really? Again, I’m talking about working in a traditional Oracle environment
- A table called ‘Table’ – don’t laugh!
- A column called ‘column’
- No foreign key(s) 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 NOT NULL ,
Text_And_Such VARCHAR2 (1024 BYTE)
) ;
COMMENT ON COLUMN MiXed_CaSe_TaBlE.BLAH_ID IS ‘this is my key baby’;
ALTER TABLE 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 NULL
) ;
ALTER TABLE "TABLE"
ADD CONSTRAINT TABLE_PK PRIMARY KEY ( 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 table in the database that is NOT case sensitive. SQL Developer recognizes that we can get away with not quoting this object name. Thank you!
‘CREATE TABLE “TABLE”‘ is a different beast. Since the word ‘table’ is a reserved word, it MUST be quoted. To access it via SQL, it will always need to be quoted. But…
Shouldn’t the tool ALSO be smart enough to warn me that I don’t know what I’m doing?
SQL Developer 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 fire as each entity is designed, but I might just need to go read the help again 😉
Can it help me with databases missing foreign keys too?
Yes, we can infer or presume a relationship exists when ID fields have matching attribute names in other entities. To see this, use the ‘Discover Foreign Keys’ feature. You can read more about this in Kris’ blog here. He shows how you can build an ERD based off of views instead of tables.
