Обсуждение: 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
Thanks
Jodi
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
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@virginia.edu> To: "Postgres Admin List" <pgsql-admin@postgresql.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@virginia.edu