Re: Approaches for Lookup values (codes) in OLTP application - Mailing list pgsql-general

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.

I should rather think that one should set up a uniqueness constraint for a
particular code/context combination. Then one select might by code value and
context as a where clause parameter.

Say :

CREATE TABLE system_values_table (
  value_as_char char(8),
  value_context char(30),
  PRIMARY KEY (value_as_char, value_context)
  )

I understand from the PostgreSQL documentation (CREATE TABLE) that "PRIMARY
KEY" implies "UNIQUE", "NOT NULL", and "INDEX".  Is this correct?

Presuming a table entry having value_as_char ="ACTV" and value_context =
"INVOICE" then when I do a SELECT I would pass the code value (as char)
together with the context thus?

SELECT * FROM system_values
  WHERE value_as_char = input_code_as_char, value_context = "INVOICE"

I presume that the decision to place the code value first or the context value
first in the primary key construct depends upon whether one foresees the need
to span selects based on the context.  So, for example, if I intended to
provide the UI with a drop down list populated with the available codes then
it would be better to have:

...
  PRIMARY KEY (value_context, value_as_char)
...

and I could then populate the selection list with a select having the form:

...
SELECT * FROM system_values
  WHERE value_context = "INVOICE"
...

The DBMS can then decide how to get the qualifying rows back and the index
would be usable in this case, whereas if the code value came first in the
composite key then the index would be useless for this query.

Have I got this more or less straight?

--
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3