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:
tinyint in the 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 ?
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.