SQL & PL/SQL

Hello folks,

I have a question for you PL/SQ developers out there. This isn't a specific problem or query I'm raising here, more a question of general approach. I'm probably not using the correct terms here so forgive me. Also, I've already posted this in the ApEx forum, however there's a degree of overlap into pure PL/SQL so I thought you were all bound to have experience of something along the same lines.

Anyhoo...

How to deal with Magic values - i.e. values which hold no intrinsic value in and of themselves, other than for state, process or conditional logic control. I use them quite a lot in my PL/SQL code (as I'm sure most developers do in one context or another).

From a Data architecture perspective, I'll generally have some sort of table for storing the 'facts': names, addresses etc, etc. Any application-specific magic values ('status', 'type') will be held as a foreign key in this table, which will reference a form of lookup table.

Example:

EMOTION
 
ID   Description
==   ===========
1    HAPPY
2    SAD
3    NEUTRAL
 
PERSON
 
NAME ... EMOTIONAL_STATE 
==== 	 ===============
BILL 	 1
JERRY    1
BRIAN	 3
DONNA    2

So far, so banal...

Now, say I have a process that needs to reference someone's emotional state for some sort of conditional logic:

declare

   n_estate number;

begin

   select emotional_state into n_estate
     from Person 
    where name = 'BILL';

   ...
   case when v_state = 1 then
      -- do something
      ...
   case when v_state = 2 then  
      -- do something else
      ...
   else
      -- otherwise something else again
      ...
end case;
...
end;

straight away your bad code radar should be going crazy: you're coding literals in there! So, the old java programmer in me wants to store these as constants - I'll generally square them away inside a package somewhere, like so:

create or replace package PKG_CONSTANTS as
 ...
   ES_HAPPY constant number:= 1;
   ES_SAD constant number := 2;
   ES_NEUTRAL constant number := 3;
 ...
end PKG_CONSTANTS;

Thus the code becomes

Case when v_state = PKG_CONSTANTS.ES_HAPPY then ...

Herein lies the crux of the issue. I'm effectively defining the same value twice: once in the lookup table (for data integrity) and once in the package. If new values are defined (say "Existential Ennui") or existing values are changed, I need to make sure the two are aligned, which hinders maintainability.

I thought about initialising the values as sort of pseudo-constants in the package initialise code but then you end up replacing one literal with another; you end up with code like:

create or replace package PKG_CONSTANTS as
 ...
   ES_HAPPY number;
   ES_SAD constant number;
   ES_NEUTRAL constant number;
 ...
end PKG_CONSTANTS;
/

create or replace package body PKG_CONSTANTS as  

   ...
 
   rf_curs sys_refcursor;
...
begin
   
   for rf_curs in 
      select ID
             ,description
        from EMOTIONAL_STATE;
        
   loop
   
      case description
      when 'HAPPY' then
         ES_HAPPY := ID;
      when 'SAD' then
         ES_SAD := ID;
      when 'NEUTRAL' then
         ES_NEUTRAL := ID;
      else
         null;
      end case;
   
   end loop;

end PKG_CONSTANTS;

I also thought about using dynamic PL/SQL to re-write and recompile the constants package in the event of a value being changed in the lookup table...seems like quite a lot of work, given that the magic value is pretty much meaningless outside of the scope of the application.

So... how to deal with this? What approach to you take? Does data integrity over-ride application programming style?

Any contributions would be welcome!