Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Feb 15, 2008 5:25 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
> On Feb 15, 2008 3:31 PM, James B. Byrne <byrnejb@xxxxxxxxxxxxx> 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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux