On Feb 15, 2008 3:31 PM, James B. Byrne <byrnejb@harte-lyne.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?
