sql - Should I use a code in my lookup table -

i woking on orable database , adding couple of lookup tables.

the general question should lookup table contain code , description , code fk main table, or should lookup table contain description , fk main table.

i arguing code/description pair. feel if have type = contractor , code = cn stored proc should where type='cn' rather having type=contractor , no code , saying in stored proc: where type='contractor' because if want display: general contractor user rather contractor. have change stored proc. feel should not have this. (changing stored proc requires recompilation in dev, migration test, retesting clients, , migrating prod requires going through change control proccess involves 2 week waiting period; whereas modifying record in table not require of this)

my data modeler wants use description. main argument require unnessesary join.

which way should done? , if if should done code/description way how convince data modeler?

thanks!

type_cd    type_dsc cn         contractor in         inspector 

summarizing of answers, think there 4 alternatives lookup table:

alternative 1:
• description (primary key, longer varchar2 column)

alternative 2:
• code (primary key, short varchar2 column)
• description (not null, longer varchar2 column)

alternative 3:
• id (a meaningless primary key, integer value derived sequence)
• description (not null, longer varchar2 column)

alternative 4:
• id (a meaningless primary key, integer value derived sequence)
• code (unique key, short varchar2 column)
• description (not null, longer varchar2 column)

the primary key column in main table foreign key constraint on top.

some characteristics per alternative:

alternative 1:
• no join required when querying main table
• clear meaning when doing ad-hoc queries on main table
• requires more storage main table
• index on main table bigger in other alternatives
• updating description value means maintenance trouble , possibly application downtime.

alternative 2:
• join required when want retrieve description value
• join in not required if want filter on lookup values: can use code value that.
• pretty clear meaning when doing ad-hoc queries on main table
• minimal additional storage requirements main table
• index on main table small.
• updating description value easy, code abbreviation description. when updating description value, code can become confusing.

alternative 3:
• join required when want retrieve description value
• when filtering on lookup values, you'd have use description values in queries id's meaningless.
• meaning not clear when doing ad-hoc queries on main table
• minimal additional storage requirements main table
• index on main table small.
• updating description value easy , doesn't cause confusion code values

alternative 4:
• join required when want retrieve description value
• join required when filtering on lookup values, use code value in lookup table.
• meaning not clear when doing ad-hoc queries on main table
• minimal additional storage requirements main table
• index on main table small
• updating description value easy , can update code value make resemble description value. may have revisit of code when doing this, though.

personal opinion:

i @ how plan use main table , lookup table. queries important , have run efficiently? values ever change?

my personal choice alternative 2 or 4. i'd use alternative 2 if absolutely sure code value can never change. , rare. country codes change, social security numbers change. currency codes change, etcetera. so, of time, i'd choose alternative 4. i'd not concerned join, because lookup table small table.

but: choose alternative suits requirements.

please feel free edit text when know more characteristics of alternative.

regards,
rob.