Enumerations in the database and O/RM

Question

Suppose I want entries in the table Regions to have a type, e.g. a city, a country etc. What's the accepted way of storing this type, assuming I'll be using O/RM (NHibernate in my case) ? I see two options:

• Have an enum in the C# bussines layer with the types and store the type as a tinyint in the table.
• Have a lookup table RegionTypes with the type identifiers (strings or ints) and reference them in the Regions table.

The second approach seems more reasonable from the database point of view, since I have foreign key constraints, plus I can have additional data about the region types, e.g. a city is a child type for country (and since I'm using SQL Server 2008 spatial features, I actually need this information for spatial manipulations). However, looking from the C# point of view, I'll basically have to have an entity RegionType and load it from the database every time i want to assign it to a region (as I understand NHibernate wouldn't allow me to make the type an enum if I store it in a lookup table). This is a little tedious for such a simple task, knowing that region types are basically fixed and unlikely to change.

What about other types, like DayOfWeek which are unlikely to ever change or have additional properties, should they have their lookup tables and entities ?

Answer 1

I usually go with the latter option, though, I'll create a generic sort of set up where items of multiple types can be stored in the same table so that I don't end up with 15 types tables. The basics are something like this

Types
   TypeID int,
   Name varchar(20),
   Description varchar(100)

Type_Items
   ItemID int,
   TypeID int -> Types.TypeID,
   Name varchar(20),
   Description varchar(100),
   Value varchar(100)

Answer 2

One reason to use lookup tables is the common use case of displaying the possible values in a menu or something. It's easy to query a lookup table, but less easy if the enum is hard-coded in a database data type or constraint, or in a C# enum.

Answer 3

General rule is to use Enums if you are sure that set of values will never be changed by user. Otherwise it's better to use lookup table.