COMPUTE: A Bed-Time Story

Question: Why do databases get so messed up over the years?

Answer: Because software rusts.

Once upon a time there was a software program that came in four "editions": Beta Edition, Evaluation Edition, Basic Edition and Extended Edition.

The Engineers were exceedingly lazy so instead of creating four different versions of the program, they only created one version, and they made it pretend to be whichever edition the customer asked for.

Whenever the program code needed to know which edition it was pretending to be, it would look inside this table:

CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   edition_name   VARCHAR ( 100 ) NOT NULL CHECK ( edition_name IN (
                     'Beta',
                     'Evaluation',
                     'Basic',
                     'Extended' ) ) );

For example...

IF EXISTS ( SELECT * FROM edition WHERE edition_name = 'Extended' ) THEN
   ... do some thing only the Extended Edition can do
END IF;

Then, one day, Marketing demanded that customers ask permission to use the Beta and Evaluation editions, and pay for the Basic and Extended editions. A registration key feature was added with a one-character code for the type of key:

CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   key_code       VARCHAR ( 1 ) NOT NULL CHECK ( key_code IN (
                     'B',        -- Beta
                     'E',        -- Evaluation
                     'S',        -- Basic
                     'X' ) ),    -- Extended
   edition_name   VARCHAR ( 100 ) NOT NULL CHECK ( edition_name IN (
                     'Beta',
                     'Evaluation',
                     'Basic',
                     'Extended' ) ) );

IF EXISTS ( SELECT * FROM edition WHERE edition_name = 'Extended' ) THEN
   ... do some thing only the Extended Edition can do
END IF;

A brilliant Alpha Engineer noticed

• that key_code would always determine edition_name
  

• and therefore edition_name was redundant, and dangerous,
  

and so, feeling full of Simplicity, Honor, Integrity and Truth, the Alpha Engineer eliminated edition_name:

CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   key_code       VARCHAR ( 1 ) NOT NULL CHECK ( key_code IN (
                     'B',        -- Beta
                     'E',        -- Evaluation
                     'S',        -- Basic
                     'X' ) ) );  -- Extended

This caused some grumbling among the Gamma Engineers who had to change the code:

IF EXISTS ( SELECT * FROM edition WHERE key_code = 'X' ) THEN
   ... do some thing only the Extended Edition can do
END IF;

Some time passed, and two new kinds of registration keys were introduced, R and U:

CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   key_code       VARCHAR ( 1 ) NOT NULL CHECK ( key_code IN (
                     'B',        -- Beta
                     'E',        -- Evaluation
                     'R',        -- Evaluation Renewal
                     'S',        -- Basic
                     'U',        -- Upgrade Basic To Extended
                     'X' ) ) );  -- Extended

Although key_code still determined edition, the program code had to be changed again and this caused more discontent among the Gammas:

IF EXISTS ( SELECT * FROM edition WHERE key_code IN ( 'U', 'X' ) ) THEN
   ... do some thing only the Extended Edition can do
END IF;

Then one day, Marketing dropped this bombshell:

• the Beta Edition was eliminated,
  

• the Evaluation Edition was replaced with the Rental Edition, and
  

• four new "version upgrade" registration keys were introduced to make old customers pay for the new version.
  

Now they were back to only three editions, Rental, Basic and Extended... but with 8 different key_code values! (and the promise of more to come):

CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   key_code       VARCHAR ( 1 ) NOT NULL CHECK ( key_code IN (
                     'R',        -- Rental
                     'A',        -- Upgrade Basic from version 1.0 to version 2 
                     'C',        -- Upgrade Basic from version 1.1 to version 2
                     'S',        -- Basic
                     'D',        -- Upgrade Extended from version 1.0 to version 2 
                     'F',        -- Upgrade Extended from version 1.1 to version 2
                     'U',        -- Upgrade Basic To Extended
                     'X' ) ) );  -- Extended

"The peasants are revolting!"

Oh yes they were... you could tell the Gammas were disgruntled by the graffiti they coded into the program:

IF EXISTS ( SELECT * FROM edition WHERE key_code IN ( 'F', 'U', 'X', 'D' ) ) THEN
   ... do some thing only the Extended Edition can do
END IF;

To the rescue!

One of the Beta Engineers suggested using the CREATE TABLE COMPUTE clause to reintroduce edition_name without requiring any extra effort to insert or update the value:

CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   key_code       VARCHAR ( 1 ) NOT NULL CHECK ( key_code IN (
                     'R',        -- Rental
                     'A',        -- Upgrade Basic from version 1.0 to version 2 
                     'C',        -- Upgrade Basic from version 1.1 to version 2
                     'S',        -- Basic
                     'D',        -- Upgrade Extended from version 1.0 to version 2 
                     'F',        -- Upgrade Extended from version 1.1 to version 2
                     'U',        -- Upgrade Basic To Extended
                     'X' ) ),    -- Extended
   edition_name   VARCHAR ( 100 ) NOT NULL COMPUTE ( 
                     CASE 
                        WHEN key_code IN ( 'R'                ) THEN 'Rental' 
                        WHEN key_code IN ( 'A', 'C', 'S'      ) THEN 'Basic' 
                        WHEN key_code IN ( 'D', 'F', 'U', 'X' ) THEN 'Extended' 
                        ELSE                                         'Unknown'
                     END CASE ) ); 

INSERT edition ( key_code ) VALUES ( 'F' );

SELECT * FROM edition;

one_row_only,key_code,edition_name
1,'F','Extended'

The Gammas did have to make one more change, but it was a good change, back to the Old Way Of Doing Things when code was readable and programs were maintainable:

IF EXISTS ( SELECT * FROM edition WHERE edition_name = 'Extended' ) THEN
   ... do some thing only the Extended Edition can do
END IF;

And everyone lived happily ever after!

The moral of this story is that computed columns are your friend.

---