Re: Approaches for Lookup values (codes) in OLTP application

On Fri, Feb 15, 2008 at 12:12 PM, James B. Byrne <byrnejb(at)harte-lyne(dot)ca> wrote:
>
> I can over-ride Rails assumptions and force a primary key formed by multiple
> columns which will have a unique index automatically created for the
> previously described "system_values_table". My question still hinges upon
> what to put into the referential table, a foreign key lookup or just the
> encoded value and let the application do the reference checking?
>
> Consider the example of ISO 3166 country codes. There are at least two ways
> to handle this:
>
> 1. Have a table just for country codes and have the code the primary key
>
> 2. Have a systems value table having a code prefix column and the code value
> concatenated into a key
> (table_prefix = "country_codes" + table_value ="CA" for example)

Generally speaking, I tend towards using the real value as the key and
foreign key in lookup tables, but occasionally using an artificial
numeric key is a better choice.

If you'll generally always need to know the actual value, you should
use it, because then it will be stored in the main table as well.
But, if you access that value only 1 time for every 100 accesses, it
will likely be faster to have it be on the other end of an int value,
which usually takes up less space.

> For something externally provided and widely used like country codes then
> option one is attractive and possibly the most sensible and robust solution.
> But consider things like transaction status codes. Perhaps an invoice
> transaction has five possible codes and a credit-note has only three, but one
> of those three is not valid for invoices. Where does one put such things?

You could use a simple multi-part check constraint for that, or, if it
needs to be more fluid than that, you could use some kind of multi-key
table that points to a valid tx type list on a 1 to many basis, and
when you insert you FK check the two values against that table.