Tag: #JoelKallmanDay

When dealing with mature environments it’s not good enough to only understand what the current trend or “best practice” is. We also have to understand the history of the environment, and the technology stack we are using, as that will help inform us about why decisions were made they way they were. Let’s look at a few examples…

DECODE and CASE Expressions

CASE expressions were first released in Oracle 8i (1998), with the CASE statement added to PL/SQL in Oracle 9i (2001). You can read more about them here. Prior to that if we wanted something similar to a CASE expression in our SQL, we had to use the DECODE function. Here’s some examples so you can see what they look like.

# Value Match CASE Expression
select case id
         when 1 then 'Banana',
         when 2 then 'Apple',
         when 3 then 'Cherry'
         else 'Unknown'
       end as fruit
from   t1;

# Searched CASE Expression
select case
         when id = 1 then 'Banana',
         when id = 2 then 'Apple',
         when id = 3 then 'Cherry'
         else 'Unknown'
       end as fruit
from   t1;

# DECODE        
select decode (id,
               1, 'Banana', 
               2, 'Apple', 
               3, 'Cherry',
               'Unknown') as fruit
from   t1;

We all know it takes people years to introduce new syntax into their code, so we will probably have examples of code well into the 2000’s still using DECODE, even though CASE expressions are easier to read, far more flexible and part of the ANSI standard.

It may be hard for some of the younger crowd to believe, but there is code out there that has been running for a very long time. About 30 years worth of PL/SQL and about 40 years of SQL. When we are looking at the code base for a mature application, we may run into things that don’t look familiar. Languages and design patterns evolve, but we have to be capable of dealing with mature code.

ANSI Joins

ANSI joins were introduced in Oracle 9i (2001). You can read about them here, and here.

I know I’m going to get a lot of hate from the Oracle crowd for this, but ANSI joins are superior to the old-style Oracle joins, where the join conditions were in the WHERE clause, with a sprinkling of (+) symbols everywhere for outer joins. I’m sure the comments will try and defend them saying things like ANSI joins are just syntax candy, and that’s fine, but I still believe ANSI joins are superior…

The point is, even if we are writing new code using ANSI joins, we still have to be capable of understanding the old syntax, because we will run into a lot of code that still uses it, because it is historical code, or new code written by historical developers. 🙂

One True Lookup Table (OTLT)

The One True Lookup Table design pattern, or rather anti-pattern, is one of those disasters that managed to work it’s way into loads of systems. There was a period when many of us were using Oracle Designer/2000 as a central repository for our ERD and physical modelling, and Designer encouraged the One True Lookup Table anti-pattern. It used to generate a number of tables, one of which was called CG_REF_CODES. This was essentially a dumping ground for name-value pairs, which grew to encompass almost all reference type data. Many people brought up on this design approach continued to use it in other projects, even when they were not using Designer anymore. I can think of one popular student system that makes heavy use of this anti-pattern. 🙁

OTLT has always been a terrible idea. I’ve been guilty of using it too, but it has always been a nightmare. If you have a system that has SQL statements that do 20 different joins to the same table, you have probably encountered the OTLT anti-pattern. You have to understand how to deal with it, and treat it as a learning experience of what not to do!

You will look at it and think the people who designed it must have been crazy, but you have to remember this is what the tools were encouraging us to do back then. If someone worked as a database designer/modeller (they used to exist), they might never actually write code, so they would not see what a disaster this design pattern was, and never learn not to do it again.

Conclusion

I always encourage people to stay up to date with technology. It’s important we keep progressing, but that doesn’t mean we can afford to forget the past. Not every system we work on is brand new, using all the latest tech and doing things using the latest approach. To be useful, we need to be able to work with both the old and the new.

Also, not everything new is good. There are plenty of examples of an “old” approach to solving a problem being superior to the new “cool” approach. It’s important we sample all of them and pick the right tool/approach for the job.

Cheers

Tim…