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

Updated ActiveRecord Code Generator

Today, I updated the ActiveRecord Code Generator a bit. I checked in changes to use primary and foreign key details from INFORMATION_SCHEMA. The original code used naming conventions to decide what various fields were used for - ID = Primary Key, Field_ID = Foreign Key to table Fields. If you want to use naming conventions, let me know and I can add a setting in App.Config to allow this (along with any "real" key constraints).

How does Rails scaffolding select HTML input tags?

Recently, a reader saw my fix for SQL Server booleans, and asked me a followup question: why does Rails display a yes/no selection instead of a checkbox? The short answer is look in {RUBY_HOME} /lib/ruby/gems/1.8 /gems/actionpack-1.10.2 /lib/action_view/helpers, but your path may vary depending on whether you are using gem, "edge rails", etc. Anyway, look in the file "active_record_helper.rb" for a method called "all_input_tags", and notice that it calls "default_input_block" if you don't supply an input_block. Now notice that "default_input_block" creates a label and calls "input(record, column.name)" which in turn calls "InstanceTag#to_tag" which finally looks at the datatype and maps boolean to a select tag. Perhaps a wiser Rails explorer can provide us with the rationale for this, but I guess we could add a MixIn for InstanceTag that redefines the to_tag() method, or just do a dirty and unmaintainable hack l...

Features of the Code Generator

I just updated my code generator to optionally generate validation attributes. This simple change includes App.config file entries for all check boxes, and a new checkbox for "Validation" - aka validation attibute generation. While I was making this change, I realized that I really need to pass a CodeGenerationContext object to the DbTable, DbField and ModelGenerator classes. The requester can populate the context, and pass it to the code generator. Anyway, enough about the code, let's talk about the templates. I made a simple template this weekend to generate a DataGridView column array, suitable for databinding. I'm sure my new template will need some tweaks to handle Foreign Keys better (it currently just displays them as TextBox). Let's look at a template. ##FILENAME:PR_${table.GetClassName()}_Insert.sql ## ## Generate a SQL stored procedure to insert a record into the ## specified table and return the newly created primary key ## ## FUTURE: The generat...