We have a database that is filled with tables utilizing an integer column for things like 'Status'. For example, Customer t has a Status column and 0-3 are the integers used in this column. It also has a StatusComputed column that's computed as (case [Status] when 0 then 'Open' when 1 then 'Hold' when 2 then 'Closed' when 3 then 'Prospect' else 'Unknown' end). Without doing any testing I'd have to guess that the query optimizer would perform better with a query that has WHERE Status = 0 rather than WHERE StatusComputed = 'Open'. Does anyone have any opinions?
I prefer using lookup tables for this. It's much easier to add new rows to a lookup table than it is to modify an existing table if you need to add another status, especially if all code changes need to go through a change control process. (Adding a row isn't usually considered a code change, so it doesn't need to go through the change control process.)
Drew
I'm actually not aware of what a lookup table is. Are you referring to a normalized table? Like status is the key field in the Customer table and they reference a lookup table called "CustomerStatus"?
For reference, the DB I'm referring to is maintained by our software developer. It's their design and the ERP software references the Status column (0-3) and displays Open, Hold, Closed, or Prospect based on its C# code. They decided to put the computed columns in for us customers simply for reference. No more need to write a case statement in the select clause, instead just select the StatusComputed column.
I'm interested to know more about lookup tables though. I searched it and my first hit was a Joe Celko article. Since he's not for the faint of heart (and since I fainted) I figured I'd let you respond to point me in the right direction. 😀
It's just what it sounds like. You use one value to look up another. Normalization is a separate, but related concept. Lookup tables are usually normalized, but a normalized table isn't necessarily considered a lookup table.
Generally, you have a single code that you look up in a table to get the description. Joe mentions the "One True Lookup Table" in which case, you will need two (or more) codes which will give you a unique combination, but otherwise the idea is the same--you look up the unique combination to get a description.
Also, you have your terminology a little mixed up. The key field of a table MUST be unique. So, status cannot be the "key field in the Customer table," because it is not unique in the customer table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA