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.
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;
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;
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.
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.
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.
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.
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
6 /* Declare a collection. */
7 lv_lookup_table lookup_table;
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;
22 OPEN c(pv_table_name, pv_column_name);
23 FETCH c BULK COLLECT INTO lv_lookup_table;
24 CLOSE c;
26 RETURN lv_lookup_table;
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.