Wednesday, February 10, 2010

In most data models, there are columns that should take values only from a limited set. For example, a state of License (as in our current project) may only take three values: AppliedFor, Granted or Denied. This kind of restriction is part of what is called Domain Level Integrity. How do you implement it?

Old Way: Separate Table

I used to create a separate table for the allowed values. It can be done, but this table has only couple records in it, is rarely updated and using a whole table for this is almost an overkill. Also, you end up writing more code to manipulate it.

New Way: SQL Rules

There is an SQL construct purpose of which is exactly this kind of constraint: SQL Rule (for Sybase docs: see this). An example: