database design - Designing For Lookup Values -

i wondering protocol in development shop or project dealing lookup values, such countries of world or states in united states of america.

i have seen done in 2 different ways: @ 1 place worked our lookups stored in database table prefix "l_" , had following columns: id, code, desc, ordersequence. so, example, countries of world have table like:

create table l_countries(     countryid int identity(1,1) primary key,      countrycode varchar(10) not null,      countrydesc varchar(50) not null,      ordersequence int null  ) 

more have seen example lookups baked enumerations, example:

enum countries  {    croatia = 1,     slovenia = 2,     serbia = 3,     // , on    } 

in event these originating database table, there utility generate c# code enumeration. otherwise takes time hardcode entries using assumption values not change. numeric values in enumeration, hardcoded based on id column in corresponding lookup table in database if exists, or hardcoded based on entry starting 1.

the argument former approach which, admittedly, favor, is quite flexible giving option using codes or full descriptions, manipulating order, , making changes without having recompile. although option generate code them possible, big advantage remain true "lookups" when relegated database. finally, way can used flexible: values in dictionary collection or generating asp.net listitems or html combobox elements server side code.

arguments have heard latter values not change, , there overhead in having retrieve lookup values database if cached @ application level. hardcoding them or generating enum, available without penalty of database retrieval.

my questions follows:

1. which option makes more sense or, if answer "it depends," can give scenario hardcoded enumerations better database lookup across entire application?

2. are there other ways have seen elegant solution dealing lookups? have worked on application there single table lookups (it included "lookupname" column) example. how alternate approach compare above 2 approaches?

if information static , used flow control, etc. use enum (i.e. use them medical form types, various options on medical forms, etc.)

if information static larger, or if no value or expressiveness offered making enum, , not needed joins, etc. store in xml or hashtable , read in needed and/or embed resource.

if information going used in database joins, excessively large, or if more practical store in database lookup table, so, int primary key.

hope helps