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 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?

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/

[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