Re: lookup tables

Jodi,

A common model within the databases of many of our applications is to have
a "codes" table for providing "lookup" values for columns of ANY table.
This allows you to deploy a single set of routines on the front end to
getting, listing and validating fields against the code-ified list of
values.

The joining columns of this codes table are field name and table reference.
Entries' fundamental attributes are the code itself, a short description, a
long description and a usage note.

Joining to this codes table is always an option and often done in reports
were the value stored in the main table is merely the coded value but the
short or long description is desired on the report itself.

As for setting up actual foreign key references, we don't normally do that
but, admittedly, that's more due to apathy than design. The real value in
doing so would be the ability to enforce validation of these code-ified
values at a database level rather than the application level. To easily do
so, you'd probably want to add a single field primary key to the codes
table since, without one, the primary key is the composite of code and
table reference and putting the latter column into the foreign key
reference tables seems to me redundant. You could also hand craft your
constraints with triggers and procedures.

Hope this is useful.

Marc

----- Original Message -----
From: "Jodi Kanter" <jkanter(at)virginia(dot)edu>
To: "Postgres Admin List" <pgsql-admin(at)postgresql(dot)org>
Sent: Monday, August 05, 2002 8:01 AM
Subject: [ADMIN] lookup tables

The postgres book that I am referencing states that lookup tables do not
need to be related to other tables. They exist just for use by the
application to give the user a list of choices.
Wouldn't it be better to include foreign keys to their corresponding
tables? Or to merely link them via the string value so they can be joined?
Any opinions?
I am working on a database that has some lookup tables that are joined by
foreign keys and others that are just joined by the "type" field. For
example, a "contact" table has a field known as "contacttype." There is a
"contacttype" table with only one field in called "type." The "contacttype"
field in the CONTACT table and the "type" field in CONTACTTYPE table are
the same. They can be joined if I want to access the description
information in the "desc" field in the CONTACTTYPE table.
Please let me know if anyone sees any issues in doing it this way? Are both
methods valid and efficient ways to handle this?
Thanks
Jodi
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter(at)virginia(dot)edu