Don’t Try This at Home – Database Edition

thatjeffsmith Database Stuff 6 Comments

Tell Others About This Story:

Just because you can do something, doesn’t necessarily mean that you should. Case in point, database object names. Yes, you can call a table in Oracle pretty much anything you want, including ‘TABLE.’ Using quotes allows you to do some pretty silly things like use reserved words, mixed case names, and worse.

On a call today someone mentioned that they found it odd that you could have a table and an index share a name. Yes, you CAN do that. But maybe you could try not to do that?. Just for readability sake, try a little harder maybe?

Here’s a fun one, a table called ‘INDEX.’ Which has a column named ‘INDEX.’ Which has an index called ‘INDEX.’ You laugh, but I have seen some crazy stuff out there. I shouldn’t have been surprised, but I was to find that SQL Developer fully handled this scenario. You see, even our testers have to spend precious time making sure that the stuff you CAN do but shouldn’t, actually works. I can only imagine what kind of fun the parser folks have with this kind of stuff.

Don’t try this at home, kids!

The Mythbusters would definitely not approve!

What weird stuff have you been asked to support?

Here’s another fun example from the field. A very large organization had decided to store the lookup values for every ID in every table they were referenced so as not to make it ‘hard’ for their support staff to join them with the appropriate parent tables. Imagine SCOTT.EMP with a ‘DeptName’ field that accompanied the ‘DEPTNO’ field. I think that’s what known as -1 Normal Form.

I’m sure you can top me. Let’s hear it!

Tell Others About This Story:

Comments 6

  1. Hi Jeff,

    oh yes, ACCESS->Oracle via ODBC, just say no! I think that document is still on the Toad files area. I’d forgotten about that one. shudder!


    1. JeffS Post

      How many horror stories start or end with NULL? Has to be one of the least understood concepts in the relational database world.

      Thanks for sharing Norm, but this isn’t as bad as your ACCESS > Oracle Explain Plan you published a few years back!

    1. JeffS Post

Leave a Reply

Your email address will not be published. Required fields are marked *