Database Design Patterns: The Lookup Table

Oracle Community

Database Design Patterns: The Lookup Table

Application design is seldom done from scratch because the industry has a collection of design patterns. There are many patterns for software design. There are object-oriented design patterns, and there are data modeling patterns.

Many of you know about the Design Patterns: Elements of Reusable Object-Oriented Software book. The four horseman of the software apocalypse wrote it. The four horseman are Erich Gamma, Richard Helm, Ralph Johnson, and John Vissides. They’re called the four horseman because their patterns should crush older and less efficient techniques. Their label as the four horseman borrows the allusion from the Bible, and they represent conquest, war, famine, and death.

It probably fair to say, that their techniques conquered the older methods. The conquest caused and sometimes still causes conflicts about how to design software. There are still developers who question the use of patterns. There also are developers who try to avoid using design patterns because they disagree with the structure. However, there are those who readily adopt them and frameworks that use them.

 This paper explains the concept, use, and practice involved in using lookup tables. You examine the design concept and then the implementation.

Design concept of lookup tables

A lookup table groups related things into a table to support something like a choice list. Choice lists are also called popup-lists, dropdown boxes, or floating menus. You often implement them as SELECT tags in HTML.

The simplest idea for a lookup table would be a yes_no table, like this:

You can implement the table with the following script:

SQL> CREATE TABLE yes_no
  2  ( yes_no_id   INT         NOT NULL
  3  , yes_no_type VARCHAR(30) NOT NULL
  4  , yes_no_desc VARCHAR(20) NOT NULL
  5  , PRIMARY KEY (yes_no_id));

You design a yes_no table like this to support a two row table that returns a Yes or No value in title case. The yes_no_type column holds an uppercase text string. You can query the two rows like this:

SQL> SELECT   yes_no_type
  2  ,        yes_no_desc
  3  FROM     yes_no
  4  ORDER BY yes_no_type DESC;

It returns

YES_NO_TYPE  YES_NO_DESC
------------ ------------
YES          Yes
NO           No

Or, you can return an English readable value for Yes or No like this:

SQL> SELECT   yes_no_desc
  2  FROM     yes_no
  3  WHERE    yes_no_type = 'YES';

It returns the title case version of yes. The first SELECT statement is more useful because it returns the list of possible values. You’re not using the surrogate key column yet but you will learn how valuable it is in the implement lookup section.  

The advantage of a yes_no table is straight forward if you know the rule of one. The rule of one means you only store a value one time in a relational database. Most applications ask a yes or no question, and the yes_no table provides the list of those two choices.

You can redesign the table to be mult-lingual by adding a language lookup column. The redesigned table uses the yes_no_lang column. The new design looks like this:

You create the new yes_no sample table with the following script:

SQL> CREATE TABLE yes_no
  2  ( yes_no_id   INT         NOT NULL
  3  , yes_no_lang VARCHAR(30) NOT NULL
  4  , yes_no_type VARCHAR(30) NOT NULL

  5  , yes_no_desc VARCHAR(20) NOT NULL
  6  , PRIMARY KEY (yes_no_id));

The table now supports multiple languages. Line 3 introduces the yes_no_lang column. To keep the sample small, it holds only four rows. It has Yes and No in English and Oui and Non in French.

You can query a French language set of lookup values like this

SQL> SELECT   yes_no_type
  2  ,        yes_no_desc
  3  FROM     yes_no
  4  WHERE    yes_no_lang = 'FRENCH'
  5  ORDER BY yes_no_type DESC;

It prints

YES_NO_TYPE  YES_NO_DESC
------------ ------------
YES          Oui
NO           Non

The yes_no table now holds the contents of two tables – they are an English and French lookup table. The trick to bridging between the two tables is the yes_no_lang column.

Here’s where the design argument occurs. The con argument is first and the pro argument is second.

Con argument

The natural key of the yes_no table is a composite key of the yes_no_type and yes_no_lang columns. That’s fine because the natural key is unique, right? Actually, that’s not true because the non-key yes_no_desc column has a partial dependency on the yes_no_lang column or part of the natural key. That means the yes_no table is only in first normal form.

Pro argument

Believe it or not, it’s OK to have some tables in first normal form. That is true when the functional dependency only separates internal tables. The functional dependency lets you navigate between the embedded sets of value. More or less, it’s similar to separate table names and it lets you the proliferation of small lookup tables. Creating a lookup table that holds multiple lookup value sets is a design pattern.

There is a downside to the pro argument and it occurs when the number of nested lists of values grows large. That’s because you need to have an access rule that keeps it simple for developers. A common practice uses a combination of two columns: one is a lookup_table column and the other is a lookup_column column. These two columns serve as an intuitive guide to discovering set of values for any of the list of values (or micro-tables) in a lookup table.

A lookup table would look like this:

The preceding design lets you store all lookup values in a single table. The values that you store in a lookup table should be infrequently changed and frequently queried.

The next section shows you how to query and return a set of values from the lookup table.

Implement a lookup table

There’s a common_lookup table in the video store model that the Oracle Database 12c PL/SQL Programming book uses. It stores lookup values. You use the lookup values to discover primary key values for things like the type of credit card you want to use in a web transaction.

The following script implements the lookup table from this article:

SQL> CREATE TABLE lookup
  2  ( lookup_id    INT         NOT NULL
  3  , table_name   VARCHAR(30) NOT NULL
  4  , column_name  VARCHAR(30) NOT NULL
  5  , lookup_type  VARCHAR(30) NOT NULL
  6  , lookup_value VARCHAR(30) NOT NULL
  7  , PRIMARY KEY (lookup_id));

After creating the table, the sample code only inserts four test rows. The test rows four major credit cards: Amex, Discover, Visa, and Master Card. The following query returns the set of values for credit cards with the surrogate key column:

SQL> SELECT   lookup_id
  2  ,        lookup_type
  3  ,        lookup_value
  4  FROM     lookup
  5  WHERE    table_name = 'ACCOUNT'
  6  AND      column_name = 'ACCOUNT_TYPE'
  7  ORDER BY lookup_type;

Lines 5 and 6 narrow the return set to the rows within a single set of values. The query returns the lookup_type and lookup_value, which is really nothing more than a name-value pair.

You can parameterize the query by creating an object table function. The first step requires that you create an object type for the SELECT-list values. An object type is a SQL data type that contains the equivalent of one row of data.

After creating the object type, you create a SQL collection, or TABLE, data type. If you’re unfamiliar with Oracle’s TABLE data type, it acts like a list rather than an array. That means you can continue to add elements until you run out of memory.

The following creates the object type as a SQL data type:

SQL> CREATE OR REPLACE
  2    TYPE lookup_object IS OBJECT
  3    ( lookup_id     NUMBER
  4    , lookup_type   VARCHAR2(30)
  5    , lookup_value  VARCHAR2(30));
  6  /

You create a SQL TABLE data type with the following statement:

SQL> CREATE OR REPLACE
  2    TYPE lookup_table IS TABLE OF lookup_object;
  3  /

With these two components, you can write a PL/SQL function that returns any one of the set of values in the lookup type. The function takes the navigational elements of the natural key, which are the table and column name. The table and column name identify a foreign key column that points back to a value in the lookup table.

You implement the get_lookup_set function like this:

SQL> CREATE OR REPLACE
  2    FUNCTION get_lookup_set
  3    ( pv_table_name    VARCHAR2
  4    , pv_column_name VARCHAR2) RETURN lookup_table IS
  5 
  6    /* Declare and construct an empty collection. */
  7    lv_lookup_table  LOOKUP_TABLE := lookup_table();
  8 

  9    /* Declare a dynamic cursor. /
 10    CURSOR c
 11    ( cv_table_name   VARCHAR2
 12    , cv_column_name  VARCHAR2 ) IS
 13      SELECT   lookup_id
 14      ,        lookup_type
 15      ,        lookup_value
 16      FROM     lookup
 17      WHERE    table_name = cv_table_name
 18      AND      column_name = cv_column_name
 19      ORDER BY lookup_type DESC;
 20 
 21  BEGIN
 22 
 23    FOR i IN c(pv_table_name, pv_column_name) LOOP
 24        /* Allocate memory to the collection. */
 25        lv_lookup_table.EXTEND;
 26 

 27        /* The assignment pattern for a SQL collection is
 28           incompatible with a cursor return type, and you
 29           construct an instance of the object’s base type. */
 30        lv_lookup_table(lv_lookup_table.COUNT) :=
 31            lookup_object( i.lookup_id
 32                         , i.lookup_type
 33                         , i.lookup_value );
 34 

 35    END LOOP;
 36 
 37    RETURN lv_lookup_table;
 38  END;
 39  /

The get_lookup_set function manages three key steps to create and populate a collection. Line 7 declares the variable and constructs an empty collection instance. Line 25 allocates memory for one element in the collection instance. The code on lines 30 through 33 do three things. First, the right operand on lines 31 through 33 calls the base object type of the collection and constructs an instance of the base lookup_object type from the cursor’s column values. The left operand uses the allocated memory size of the collection to set the index value, and then it assigns the right operand’s lookup_object instance to the left operand.

After I posted the article, Niels Hecker asked a very good question about why I choose to read line-by-line instead of use a BULK COLLECT option. As a rule lookup lists are very short and any processing gain is small, but you can use a BULK COLLECT statement as well. There are actually two options with the BULK COLLECT. The first uses a parameterized cursor and the second uses an implicit dynamic query (simply substitutes the parameter variables inside the WHERE clause. Both examples work from Oracle Database 11g forward (actually, it strikes me they should work form Oracle 9i forward but I didn't test those desupported versions).

The parameterized cursor example provided by Niels in his comment works:

SQL> CREATE OR REPLACE
  2    FUNCTION get_lookup_set
  3    ( pv_table_name VARCHAR2
  4    , pv_column_name VARCHAR2) RETURN lookup_table IS
  5 
  6    /* Declare and construct an empty collection variable. */
  7    lv_lookup_table  LOOKUP_TABLE;
  8 
  9    /* Declare a dynamic cursor. */
 10    CURSOR c
 11    ( cv_table_name            VARCHAR2
 12    , cv_column_name VARCHAR2 ) IS
 13        SELECT   lookup_object( lookup_id
 14                              , lookup_type
 15                              , lookup_value)
 16        FROM   lookup
 17        WHERE             table_name = cv_table_name
 18        AND      column_name = cv_column_name
 19        ORDER BY lookup_type DESC;
 20 
 21  BEGIN
 22
 23    OPEN  c(pv_table_name, pv_column_name);
 24    FETCH c BULK COLLECT INTO lv_lookup_table;
 25    CLOSE c;
 26 
 27    RETURN lv_lookup_table;
 28  END;
 29  /

There are some subtle differences that make this work. Line 7 only declares the lv_lookup_table collection variable. You can't initialized the collection before the statement runs because the sizing is dynamic and implicit. The other change occurs on lines 13 through 15 where you use the columns from each row to construct the base object type of the collection in the SELECT-list.

If you don't want to declare a cursor, which I would discourage, you can also use a BULK COLLECT like this:

SQL> CREATE OR REPLACE
  2    FUNCTION get_lookup_set
  3    ( pv_table_name VARCHAR2
  4    , pv_column_name VARCHAR2) RETURN lookup_table IS
  5 
  6    /* Declare and construct an empty collection variable. */
  7    lv_lookup_table  LOOKUP_TABLE;
  8 
  9  BEGIN
 10    /* A bulk collect without a formal cursor. */
 11    SELECT   lookup_object( lookup_id
 12                           , lookup_type
 13                           , lookup_value )
 14    BULK COLLECT INTO lv_lookup_table
 15    FROM    (SELECT   lookup_id
 16             ,            lookup_type
 17             ,            lookup_value
 18             FROM               lookup
 19             WHERE    table_name = pv_table_name
 20             AND      column_name = pv_column_name
 21             ORDER BY lookup_type DESC);
 22 
 23    RETURN lv_lookup_table;
 24  END;
 25  /

Like the earlier example, you can't initialize the collection in the declare block or prior to the BULK COLLECT statement in the execution block. If you do, you raise an exception. Also, the SELECT-list on lines 11 through 13 constructs the object instances before assigning the set to the SQL object type collection variable.

You call the function in any of its variants with the table and column names like this:

SELECT *
FROM   TABLE(get_lookup_set('ACCOUNT','ACCOUNT_TYPE'));

The TABLE function is a SQL built-in. It converts a SQL collection into a SQL result set, which is how we can consume the results in an external programming language, like Java or a scripting language. The foregoing query returns the following result set:

LOOKUP_ID  LOOKUP_TYPE    LOOKUP_VALUE
---------- -------------- --------------
      1    VISA_CARD      Visa
      2    MASTER_CARD    Master Card
      3    DISCOVER_CARD  Discover
      4    AMEX_CARD      Amex

You have learned how and why database development can benefit from lookup tables. You also have a full implementation that you can use in your application development.

In a subsequent article, you will learn how to leverage this to integrate this server-side design logic to build web applications.

5415 2 /
Follow / 6 Jul 2016 at 11:27am

Hello Michael,

is there a speical reason that you don't create the object_instance on the fly in the cursor and use BULK COLLECT like:

SQL> CREATE OR REPLACE

 2    FUNCTION get_lookup_set

 3    ( pv_table_name    VARCHAR2

 4    , pv_column_name VARCHAR2) RETURN lookup_table IS

 5  

 6    /* Declare a collection. */

 7    lv_lookup_table  lookup_table;

 8  

 9    /* Declare a dynamic cursor. /

10    CURSOR c

11    ( cv_table_name   VARCHAR2

12    , cv_column_name  VARCHAR2 ) IS

13      SELECT   lookup_object( t.lookup_id

14                            , t.lookup_type

15                            , t.lookup_value ) AS obj

16      FROM     lookup t

17      WHERE    t.table_name = cv_table_name

18      AND      t.column_name = cv_column_name

19      ORDER BY t.lookup_type DESC;

20  

21  BEGIN

22    OPEN  c(pv_table_name, pv_column_name);

23    FETCH c BULK COLLECT INTO lv_lookup_table;

24    CLOSE c;

25  

26    RETURN lv_lookup_table;

27  END;

28  /

Follow / 8 Jul 2016 at 10:32am

Niels,

I was just trying to show the easiest way for a beginner. I've added your example into the article and put another example without a structured cursor. I didn't provide the example with a WITH clause because I think anybody who knows how to write a WITH clause knows how to use it. Thanks for reading the article and helping me make it better for other readers.

Michael