Thoughts on List items - foreign key vs. storing codes

From: Robert <rscholl_at_pentegra.com>
Date: 7 May 2003 08:44:56 -0700
Message-ID: <431cd4b6.0305070744.5aa8e0f1_at_posting.google.com>

I apologize if this topic was already covered, but I was unable to find the right keywords.

Anyway, I'm working with a database that has a generic table for list items which links an id value to a display item. For example reporting periods are: A for annual which has an id value of 143, D for daily which has a value of 149, etc. Whenever I retrieve data I need to join to this table which complicates my query but ensures no invalid choices will be used.

To simplify my work I'm thinking of 2 approaches:

1. Add another field which will store the character (A,D,M,Q) then remove the foreign key field entirely and count on the user interface to make sure valid periods are entered.
2. Leave the foreign key field in and add the character field and update it through triggers (i.e. denormalize) and use the character field for querying.

Another part of this equation is that I'm using SQL 2000. Is there an advantage of indexing an integer over a character field?

Any opinions / thoughts?

Thanks,
Rob Received on Wed May 07 2003 - 17:44:56 CEST