Re: Approaches for Lookup values (codes) in OLTP application - Mailing list pgsql-general

On Fri, February 15, 2008 12:38, Richard Huxton wrote:
>
> I'm not a Rails guy, but everything I've read about it suggests if
> you're going to gain any advantage from it, then you should follow its
> way of doing things. That means not converting anything, but rather
> writing a rails app that does the same as your current app (if I'm
> making myself clear).

Rails is "Opinionated" software but it will allow non-arbitrary keys.  I
realize that I am not expressing myself well but this is in large measure due
to transitioning from a non-RBMS environment to relational technology and
having at the same time move from a host based application to a web-based
n-tier application.  So, I tend to get muddled from time to time.

To restate my original query in more straight-forward terms: What is
considered appropriate RBMS practice to deal with encoded information which
has to be validated on input?  One always has the option of just putting a
table of values into the application itself, but I have not found much to
recommend in this approach.

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)

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?
What is the generally accepted best practice?  Does one construct a separate
code table for every transaction type?  Is it good practice to have a
transaction_type table, a code_table, and a transaction_code_union table and
lookup against the union?

This is perhaps a very minor and basic issue for this list's audience, but I
am going to live with these decisions a very long time and I would prefer to
have some idea of what is considered appropriate RBMS treatment for
application validation data as opposed to business state data.


--
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3