I have a number of tables, all of which have a char(4) code. In one
instance it is indicating a status flag for a particular case record (eg
Open, Closed, Pending etc); in another it is indicating the type of quick
lookup function (eg in History, or Diagnosis etc).

At the moment, for each type of code I have defined a table containing all
the possible code values, and long text to which I have related the tables
to ensure that only valid code entries are possible.

eg:

Table CaseCodes contains ("OPEN", "Case open"), ("CLOS", "Case closed" ),
( "PEND", "Case Pending" )
Table Case contains a "CaseCode" (foreign key) amongst other fields

Table LookupCodes contains ("HIST", "History lookup"), ("DIAG", "Diagnosis
looking"), ...
Table Lookups contains a "LookupCode" (foreign key) amongst other fields.

It occurs to me that I could have one central dictionary of these codes, and
instead of using Sql built-in referential integrity, I could force it using
triggers.

Pros/cons:

Many tables:
* Easy joins
* Built in primary key/foreign key constraints

Single table:
* Lots of trigger code to be written/maintained
* Difficult for user written joins
* Single table easier to administer/code for at the front end

Does anyone have any feelings about this/come across anything similar? Any
advice? (This is a long term project so I want to get my design just
right). Is it possible to have constraints against views to a single table?
(and in which case, is this a good solution)

Best wishes
James