Skip to main content

My take on Enums in SQL Server

I am a registered user on SQL Server Central, and I read a good article there on "Enums in SQL Server". I thought that I would quickly present my own humble solution to this problem, although I do not mean to imply that my way is better - you can decide for yourself.

First, the problem to be solved is to represent a limited set of choices, where new choices would typically require a developer's intervention to implement the business logic. An example would be a list of task priorities in a to-do list.

CREATE TABLE Priorities(
ID tinyint not null identity(1,1) PRIMARY KEY,
Code char(3) not null unique,
Priority varchar(20) not null
) ;

INSERT INTO Priorities (Code, Priority) VALUES ('911', 'Emergency') ;
INSERT INTO Priorities (Code, Priority) VALUES ('HIG', 'High') ;
INSERT INTO Priorities (Code, Priority) VALUES ('NOR', 'Normal') ;
INSERT INTO Priorities (Code, Priority) VALUES ('LOW', 'Low') ;
INSERT INTO Priorities (Code, Priority) VALUES ('ATA', 'As Time Allows') ;

The best feature of this lookup table style is that it allows you to choose a key size that is appropriate for the number of data elements, and tiny int is 1 byte. I feel confident that an integer key comparisons will always be as fast as or faster than a char(x) string, since we never have to worry about collation sequences. We do not use the description field or the primary key in our business logic, we use the "Code" field exclusively. This allows us to change the descriptions based on user dictates (and it will happen!), minimize storage requirements in our task table where it matters, and if we ever need to archive the data, just convert Priorities.ID to Priorities.Code. It also means that our business logic doesn't care what data type is used for the primary key, minimizing code disturbances if/when we move from tinyint to smallint.

Having presented my take on the problem, the approach taken in the Enum article has its advantages. Archiving is dead simple - just copy the lookup key. And there is no need to force the code list to be unique since it is a primary key and this property is guaranteed. Also, debugging is simpler, as the developers will not usually have to translate the codes to determine their meaning.

Either way, this avoids a classic problem in programming - mixing usages. I have always felt that we should not let a user assign primary keys to tables, as they are not likely to make unique values, and they tend to want to change their mind, which makes for some miserable modified key update code that ripples through your database, touching related tables that otherwise would not care that (for example) a user has changed their name.

Comments

Popular posts from this blog

Castle ActiveRecord with DetachedCriteria

My current development environment is Visual Studio Express C# Edition (read that as free ), Castle ActiveRecord's latest svn trunk(usually within a few days), and NHibernate svn trunk. As of NHibernate version 1.2.0, there is a very cool new class out there ... DetachedCriteria. This class lets you set all of your Castle relational attributes like BelongsTo, HasMany, etc. as lazy fetch, and over-ride this for searches, reports, or anytime you know ahead of time that you will be touching the related classes by calling detachedCriteria.SetFetchMode(..., FetchEnum.Eager). As a good netizen, I have tried to contribute to NHibernate and Castle ActiveRecord even if only in the smallest of ways . Oh yeah, I tried mapping to a SQL VIEW, and it worked GREAT! I received a comment after my last post, indicating that there is a better way, and I am sure of it, but the view guaranteed that I only have one database request for my dataset. NHibernate was wanting to re-fetch my missing as

Castle ActiveRecord with Criteria and Alias

Update May 25, 2007: ActiveRecord now supports DetachedCriteria, which eliminates the need for the SlicedFindAll that I wrote below. It is nice when a library moves to add support for such commonly needed functions. So in summary, use Detached criteria instead of the code below. It is still a nice example of using NHibernate sessions. I have a history log, where each history record "belongs to" a service record. I have to treat this as a child-to-parent join, since some children are orphans. I wanted to use the FindAll(Criteria), but I wanted the option to have optional criteria, orders and aliases. My solution was to create an ARAlias class to represent an Associated Entity and an alias, and then build an ARBusinessBase class with the following method: public static T[] SlicedFindAll(int firstResult, int maxResults, Order[] orders, ARAlias[] aliases, params ICriterion[] criteria) { IList list = null; ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionF

Castle ActiveRecord calling a Stored Procedure

Update: I have contributed patch AR-156 that allows full integration of Insert, Update and Delete to ActiveRecord models . If you've been reading my blog lately, you know that I have been seriously testing the Castle ActiveRecord framework out. I really love it, but I have an existing Microsoft SQL Server database with many stored procedures in it. I have tested the ActiveRecord model out, and I am sure that I will learn enough to be able to use it for standard CRUD (create, read, update, delete aka. insert, select, update, delete) functionality. BUT ... If I really want to integrate with my existing billing procedures, etc, I will have to be able to call stored procedures. I have taken two approaches ... write the ARHelper.ExecuteNonQuery(targetType, dmlString) method that gets a connection for the supplied type, executes dmlString, and closes it. write the ARHelper.RegisterCustomMapping(targetType, xmlString) method that allows me to add mappings that refer to my auto-gener