Re: Thoughts on List items - foreign key vs. storing codes

>> I'm working with a database that has a generic table for list
items which links an id value to a display item. <<

Generic table!!??? Is it too late to hunt down the moron who did this to you and kill him? Think of it as a software quality improvement program with a gun ...

"To be is to be something. To be something, is to be something in particular. To be nothing in particular is to be nothing." -- Aristole

>> 1. Add another field [sic] which will store the character
(A,D,M,Q) then
remove the foreign key field [sic] entirely and count on the user interface to make sure valid periods are entered. <<

One problem you have is that you don't know that a column is not a field -- this is fundamental. Unlike a field in a file system record, A column has a domain, a datatype and constraints. Use constriants on the DB to ensure valid data:

CREATE TABLE Foobar
( ...
 report_type CHAR(1) DEFAULT 'A' NOT NULL

          CHECK (report_type IN ('A','D','M','Q')),   ..);

>> 2. Leave the foreign key field [sic] in and add the character
field [sic] and update it through triggers (i.e. denormalize) and use the character field [sic] for querying. <<

Redundant data, denormalization, and triggers!! Wow! You don't care about performance or maintaining code at all!

Rule of thumb: Use CHECK constraints (or better, CREATE DOMAIN statements) when the list of code values is short and does not change much. Use a foreign key reference when the list of possible values is long or volatile. A typical declaration might look like this:

<code value> <data type> DEFAULT <code value> NOT NULL REFERENCES <code table name>(<code value>) ON UPDATE CASCADE
ON DELETE SET DEFAULT
>> Is there an advantage of indexing an integer over a character field
[sic]? <<

Integers will search faster than long character strings, but who cares about nanoseconds in the year 2003? The real question is what is the best datatype for modeling the data element. First get the model right, then worry about performance. Received on Fri May 09 2003 - 19:59:52 CEST