Design Challenge #20 - Decoding codes

You are responsible for modeling best practices in
your company, and have noticed three different ways for modeling codes. Take Gender for example.  Gender Code, which can have the values 'F' or 'M' (or possibly '01' or '02'), has been modeled without a code lookup, with a code lookup, and with a generic code lookup.

Without a code lookup, there is no description available for the code:

With a code lookup, there is a description available for each code. A Gender Code value of 'F' (or '01') has a Gender Description of 'Female' and a Gender Code value of 'M' (or '02') has a Gender Description of 'Male':

With a generic code table, all of the codes are stored in a single structure. In this example, the Type Code can contain the value 'Gender', Actual Code contains 'F' (or '01') or 'M' (or '02'), and Code Description contains 'Female' or 'Male':

When should you use each of these options?

 

To read the response to this design challenge, please click here