Simplify managing application codes and their values

Systems often have an abundance of simple codes and values, such as employment status, pay frequency, document type, user type, etc. Managing each of those codes in their own tables increases maintenance requirements, interfaces to manage them, and complexity for the end user. This blog offers consolidation of all simple codes and their values into three tables, (code type, code, and code values) that can be inventoried/tracked and managed from one interface. The codes and values can then be referred to/accessed through one view. How does it work? Steps:

1. Code Tables and View
2. Code Maintenance User Interface
3. Sample application

-- Code Tables and View

• Code Types: Categorizes codes into logical groups, such as employment, demographic, document, security, customer, etc. for ease in use and reporting.
  Effective and Expiration dates allow expiring code type availability without orphaning entries.

Create Table CODES_TYPE
    (
  CODES_TYPE_ID                 NUMBER(20)        GENERATED BY DEFAULT ON NULL AS IDENTITY NOCACHE,
  CT_DESCRIPTION                VARCHAR2(20 CHAR) CONSTRAINT CODES_TYPE_DE_NN NOT NULL,
  CT_DESCRIPTION_ABREV          VARCHAR2(10 CHAR) CONSTRAINT CODES_TYPE_DA_NN NOT NULL,
  CT_EFFECTIVE                  DATE              CONSTRAINT CODES_TYPE_EF_NN NOT NULL,
  CT_EXPIRES                    DATE              CONSTRAINT CODES_TYPE_EX_NN NOT NULL,
  VC_CT_DESCRIPTION_CAPS        VARCHAR2(20 CHAR) GENERATED ALWAYS AS (UPPER("CT_DESCRIPTION")),
  VC_CT_DESCRIPTION_ABREV_CAPS  VARCHAR2(10 CHAR) GENERATED ALWAYS AS (UPPER("CT_DESCRIPTION_ABREV")),
  CONSTRAINT CODES_TYPE_PK  PRIMARY KEY  (CODES_TYPE_ID)
    )
    /

• Codes: An entry for each individual code within a Code Type. For example a Sales type code might have a Sales Region code. The CO_Char_Or_Num column indicates whether the value of the code is character or numeric only. Virtual columns easily make upper case name and abbreviation versions of the code available.

Create Table CODES
    (
  CODES_ID              NUMBER(20)        GENERATED BY DEFAULT ON NULL AS IDENTITY NOCACHE,
  CODES_TYPE_ID         NUMBER(20)        CONSTRAINT CODES_CT_NN NOT NULL,
  CO_NAME               VARCHAR2(50 CHAR) CONSTRAINT CODES_CN_NN NOT NULL,
  CO_NAME_ABREV         VARCHAR2(25 CHAR) CONSTRAINT CODES_NA_NN NOT NULL,
  CO_CHAR_OR_NUM        VARCHAR2(5  CHAR) CONSTRAINT CODES_CO_NN NOT NULL,
  CO_EFFECTIVE          DATE              CONSTRAINT CODES_CE_NN NOT NULL,
  CO_EXPIRES            DATE              CONSTRAINT CODES_CX_NN NOT NULL,
  VC_CO_NAME_CAPS       VARCHAR2(50 CHAR) Generated Always as (UPPER("CO_NAME")),
  VC_CO_NAME_ABREV_CAPS VARCHAR2(25 CHAR) Generated Always as (UPPER("CO_NAME_ABREV")),
  CONSTRAINT CODES_PK   PRIMARY KEY  (CODES_ID),
  CONSTRAINT CODES_TY_FK FOREIGN KEY (CODES_TYPE_ID) REFERENCES CODES_TYPE (CODES_TYPE_ID)
    )
    /

• Code Values: All possible code values for a particular code, of a particular code type. Code values are character or numeric based on their Codes.CO_CHAR_OR_NUM designation. CV_GR_NAME and CV_GR_ABBREV allow grouping of values as an option.

Create Table CODES_VALUE
    (
  CODES_VALUE_ID              NUMBER(20)                GENERATED BY DEFAULT ON NULL AS IDENTITY NOCACHE,
  CODES_ID                    NUMBER(20)        CONSTRAINT CODES_VALUE_CI_NN NOT NULL,
  CV_VALUE_CHARACTER          VARCHAR2(50 CHAR) ,
  CV_VALUE_NUMBER             NUMBER(20,2)      ,
  CV_DESCRIPTION              VARCHAR2(50 CHAR) CONSTRAINT CODES_VALUE_CD_NN NOT NULL,
  CV_GR_NAME                  VARCHAR2(20 CHAR) ,
  CV_GR_ABBREV                VARCHAR2(10 CHAR) ,
  CV_EFFECTIVE                DATE              CONSTRAINT CODES_VALUE_CE_NN NOT NULL,
  CV_EXPIRES                  DATE              CONSTRAINT CODES_VALUE_CX_NN NOT NULL,
  VC_CV_VALUE_CHARACTER_CAPS  GENERATED ALWAYS  As (Upper("CV_VALUE_CHARACTER")),
  VC_CV_VALUE                 GENERATED ALWAYS  AS (CASE  WHEN "CV_VALUE_CHARACTER" IS NOT NULL THEN "CV_VALUE_CHARACTER" ELSE TO_CHAR("CV_VALUE_NUMBER") END),
    VC_CV_VALUE_CAPS                    GENERATED ALWAYS  AS (CASE  WHEN "CV_VALUE_CHARACTER" IS NOT NULL THEN UPPER("CV_VALUE_CHARACTER") ELSE TO_CHAR("CV_VALUE_NUMBER") END),
  VC_CV_DESCRIPTION_CAPS      VARCHAR2(50 CHAR) GENERATED ALWAYS AS (UPPER("CV_DESCRIPTION")),
  CONSTRAINT CODES_VALUE_PK     PRIMARY KEY (CODES_VALUE_ID),
  CONSTRAINT CODES_VALUE_CI_FK  FOREIGN KEY (CODES_ID)  REFERENCES CODES (CODES_ID)
    )
    /

• Full Code View: Ties Code Type, Name, and Values into one view for easy accessibility and use. Query functions can be made to retrieve key field values, and code names, types, and values using a combination of parameters for all codes in one place. List of Values and SQL queries of one view are options as well.

CREATE OR REPLACE FORCE VIEW VW_CODES_CUR_FULL
AS 
Select 
     Codes.Codes_ID
    ,Codes.CO_Name
    ,Codes.CO_Name_Abrev
    ,Codes.CO_Char_Or_Num
    ,Codes.CO_Effective
    ,Codes.CO_Expires
        ,Codes.VC_CO_NAME_Caps
        ,Codes.VC_CO_NAME_Abrev_Caps
    ,Codes_Type.Codes_Type_ID
    ,Codes_Type.CT_Description
    ,Codes_Type.CT_Description_Abrev
    ,Codes_Type.VC_CT_Description_CAPS
    ,Codes_Type.VC_CT_Description_Abrev_CAPS
    ,Codes_Type.CT_Effective
    ,Codes_Type.CT_Expires
    ,Codes_Value.Codes_Value_ID
    ,Codes_Value.CV_Value_Character
    ,Codes_Value.CV_Value_Number
    ,Codes_Value.VC_CV_Description_CAPS
    ,Codes_Value.VC_CV_VALUE_CAPS CV_VALUE
    ,Codes_Value.CV_Description
    ,Codes_Value.CV_Effective
    ,Codes_Value.CV_Expires
  From  Codes
       ,Codes_Type
       ,Codes_Value
  Where     Codes.Codes_Type_ID   = Codes_Type.Codes_Type_ID
        And Codes_Value.Codes_ID  = Codes.Codes_ID
        And Current_Date Between Codes.CO_Effective       And Codes.CO_Expires
        And Current_Date Between Codes_Type.CT_Effective  And Codes_Type.CT_Expires
        And Current_Date Between Codes_Value.CV_Effective And Codes_Value.CV_Expires;

-- Code Maintenance User Interface Manage the code types, names, and values using a simple APEX page with corresponding master/detail interactive grids, respectively. Integrate your security into the page or view depending on your security model to allow users/others to maintain and/or see specific codes. An example is below.

-- Sample application Create a sample application using the Oracle EMP and DEPT example tables. The code below modifies the EMP table to include columns for Payroll Frequency, Employment status, and Organization Level with related foreign key constraints. Each of these correspond to codes shown in the example above. The column names end in CID using a naming convention that indicates the value is a primary key in the code values table.

Alter Table EMP Add (PayFreq_CID Number(20), Status_CID Number(20), Level_CID Number(20));

Alter Table EMP Add Constraint EMP_PayFreq_FK Foreign Key ( PAYFREQ_CID)  References Codes_Value(Codes_Value_ID);
Alter Table EMP Add Constraint EMP_Status_FK  Foreign Key ( STATUS_CID)   References Codes_Value(Codes_Value_ID);
Alter Table EMP Add Constraint EMP_Level_FK   Foreign Key ( LEVEL_CID)    References Codes_Value(Codes_Value_ID);

These new columns appear below in a Dept / Emp master/detail Interactive Grid displaying departments and employees. The employee name is a link opening a modal edit page where the new payroll frequency, status, and level columns can be updated.

Upon opening the Employee modal update page the new columns are shown as drop down select lists for selecting the appropriate value.

The drop down values result from the column's source SQL Query definition selecting Code Value descriptions and values from the Code view.

In summary, the Code Type, Code, and Code Value tables consolidates many tables into three enabling inventory/tracking and maintenance in a single interface, reducing the number of needed tables and related support, and access/usage from a single view when utilizing the codes throughout a solution.
Hopefully you have found this useful. Your comments are welcome.