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

On Feb 15, 2008 3:31 PM, James B. Byrne <byrnejb(at)harte-lyne(dot)ca> wrote:
>
> On Fri, February 15, 2008 14:43, Scott Marlowe wrote:
> >
> >> 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.
> >
>
> Is this to say that one should establish a table with the code as the
> "non-unique" index and then have as its dependent values the usage contexts
> which are applied as filters? I do not comprehend what you mean by a valid tx
> type list on a 1 to many basis. If employed then an fk check presumably has
> to resolve to a unique entry in the case of code validation.

No, I was saying you should have a multi-value key in your lookup
table that gives the relation of something like::

create table tx_type_check (tx_type text, codes text, primary key
(tx_type, codes));

You populate it with all your possible value combinations, and then in
your master table have a FK to the tx_type_check table.

Does that make sense?