Hi

im designing relatively simples datamodels although im not sure what is the best appraoch to take when concerning a column that has an absolute number of possible values.

should i embedd values within a CHECK constraint, or alternatively, should i enbedd these values into a LOOKUP table? Obviously (or to me, anyway) if i have less-that-five or so values, then i'd use a CHECK constraint for convenience, although reasonably, what number of values or reasons (added table joins etc) would constitue the use of a LOOKUP table.

this may appear trivial, but i'm really interested in peoples views,

thanks in advance.

It's a tricky decision. The most important factor is how static the list of values is. If it is highly static then there is little to be gained from having a separate table; you can build static selectors (e.g. radio groups) into your application secure in the knowledge that you will probably never have to change them. On the other hand, if it is very (or even fairly) likely that new values will be added to the list at a later date, then using a lookup table and basing your application's selectors as queries against that table makes your application more robust in the face of changes to the constraint.

You should also consider whether or not the domain values will be accessed independently.

Well, I used to believe that five-or-so fixed values was better in a CHECK constraint rather than a table. But many experiences have changed my mind. Now, for more than one value, I use a table every time. There are many reasons, but the most important (ie. the most common reason for changing an existing CHECK constraint into a table) is report tasks using the db. If you have (example):

ClientType table (instead of CHECK constraint on ClientType col in Client table)
ClientType Description
F Fat
T Thin
G Tight

The report programs that need the description column do not have to have their own (programmatic) mapping of ClientType to Client.Description; such mapping is duplicated and presents an administration problem. Plus the Description can be changed easily, and is immediately reflected in the reports.

Of course, ClientType is an FK column in the Client table.

Cheers