Applications often have the need to have selection lists present and/or limit the possible choices for entry in a field. Some lists need to be static, and others need to be a bit more flexible in terms of adding new values. I’ve seen several solutions to this, and having been “stuck” on 11g for a long time, I thought I’d test out a “new” way of doing it using a composite foreign key and hidden columns.

List of values (LOV) often need to fulfill several requirements:

• Limit the possible inputs
• Present a list of possible inputs to the user
• Display a describing text instead of codes in reports and user interface

To limit the inputs you can easily use a check-constraint on the column in the table:

create table mytable (
  id number primary key,
  my_coded_value number(1) not null,
  constraint my_coded_valud_chk check (my_coded_value between 1 and 5)
);

This is fast, easy, efficient and ensures data quality. I consider it most useful for few values that won’t change over time.

It is however not well suited for displaying the corresponding text to the user. You’d have to use a case-statement or something similar for that.

If you put the values in a separate table, you can add a foreign key to that table and it will make it easy to display the coded value’s text. It can also facilitate efficient searches based on the coded text value. However – this will potentially give you a lot of tables to create and maintain.

If you collect all the LOVs into one common table-structure, you don’t have to create a lot of tables and you can maintain these through the same API or GUI.

Based on composite foreign keys, virtual columns and invisible columns I thought I’d try a, for me, new twist on this in my current project. If I collect all my simple LOVs into a two-table structure:

create table lists (  
  list_id    number generated by default on null as identity  
             constraint lists_pk primary key,  
  list_name  varchar2(100) not null  
);

create table list_values (  
  list_id     number not null  
              constraint list_values_list_id_fk references lists,  
  code_id     number not null,  
  code_value  varchar2(100) not null,  
  in_use      varchar2(1) default 'Y',  
  constraint list_values_pk primary key (list_id, code_id),  
  constraint in_use_chk check (in_use in ('Y', 'N'))  
)  
organization index;

I’ve created the table as an index organized table (sorted by the primary key), to provide for efficient lookups of code_values.

Now – for my data table I can use a composite foreign key, but I don’t have to store the list_id – it can just be generated. And to avoid “cluttering” my output when I do a select * in ad-hoc queries, I can make it a hidden column. The hidden part is of course entirely optional.

create table customers (  
  customer_id           number generated by default on null as identity  
                        constraint customers_pk primary key,  
  status_id             number not null,  
  status_code_list_id   invisible generated always as (1),  
  constraint customer_status_lov_fk foreign key (status_code_list_id, status_id)   
  references list_values (list_id, code_id)  
);

This way, all new values in status_id must conform to a corresponding code_id in list_values with list_id = 1;

If you want to index on this foreign key, I’d suggest to use the compress-attribute to reduce the size, as status_code_list_id will always have just one value and the status_id will have a limited number of values. That way, the index won’t take any significant more space that an index on status_id alone:

create index customer_status_lov_fk
on customers (status_code_list_id, status_id) compress;

Using this technique ensures that old values can’t be deleted from the code list as long as they’re referenced in the customers table. If I want to retire some values from the list, I’ll tag them with in_use = ‘N’ and filter on that before presenting the list in listboxes in the GUI. This filtering part, I’ll hide behind a view, joining lists and list_values, so that it’s easily reusable for different GUI searches. These are also datasets that are easily cacheable on the application server and/or the client.

I’ve saved a script for this as a Live SQL session that can easily be reviewed and run:

Critique of this model:

Pros:

• Simplistic.
• Can use code_id’s that are easy to use and remember (can start from 1 for each value list). Of course I use constants in the code, but it’s better when doin ad-hoc queries.
• Can drop having a check-constraint on the customers table.
• Easy to introduce new code values.
• Can avoid triggers.

Cons:

• Have to include the codelist_id-column on every join to get the correct value. This can be remedied by using a view where the join for such columns is in place.
• Tagging a code value as out of use won’t be validated by the constraint the way it is defined now. It will be possible to insert new rows with out-of-use-values. Depending on business needs it might still be okay.
• More complex varieties like having codes with valid date-ranges may need other solutions. A trigger is a possibility here.

Anyway – I’ll test this out and see how I like it. I’ve added a descripton and a couple of attribute columns to the lists and list_values tables to improve them a bit.

I’ve created a template in my IDE to easily create the code for adding the columns, constraints, indexes and comments.

References:

Alex Zaballa and Daniel Da Meda’s blog post on invisible columns is worth taking a look at.