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

On Feb 15, 2008 5:25 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> 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?

Here's what I had in mind, a simple example:

-- Create and load the lookup table:
create table tx_type_check (tx_type text, codes text, primary key
(tx_type,codes));
insert into tx_type_check values ('invoice','inv1');
insert into tx_type_check values ('invoice','inv2');
insert into tx_type_check values ('invoice','inv3');
insert into tx_type_check values ('invoice','shr1');
insert into tx_type_check values ('invoice','shr2');
insert into tx_type_check values ('credit','shr1');
insert into tx_type_check values ('credit','shr2');
insert into tx_type_check values ('credit','crd1');

-- Create a master table that references this lookup table:
create table txm (id serial primary key, tx_type text, tx_code text,
foreign key (tx_type,tx_code) references tx_type_check
(tx_type,codes));

-- test it
insert into txm (tx_type, tx_code) values ('invoice','inv1');
INSERT 0 1
insert into txm (tx_type, tx_code) values ('invoice','shr1');
INSERT 0 1
insert into txm (tx_type, tx_code) values ('invoice','crd1');
ERROR: insert or update on table "txm" violates foreign key
constraint "txm_tx_type_fkey"
DETAIL: Key (tx_type,tx_code)=(invoice,crd1) is not present in table
"tx_type_check".

and we can't insert invalid combinations of the two.