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:
- 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.
- 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.
Alex Zaballa and Daniel Da Meda’s blog post on invisible columns is worth taking a look at.