Mapping static/enum/lookup tables such as FooType and BarStatus

Question

Given the following schema:

Foo
----------
BarId
FooA
FooB
FooTypeId

FooType [LookUp Table]
----------
TypeC
TypeD

Bar
----------
BarZ
BarStatusId

BarStatus [LookUp Table]
----------
TypeE

The type and settings tables are static, they contain static data that could be mapped to enums. A.k.a Lookup tables. Foo and Bar are normal tables.

My question is; How do I idiomatically map these lookup tables using Fluent NHibernate? What is the best practice?

class FooType
{
    TypeC ...
    TypeD ...
}

enum TypeC { ... }
enum TypeD { ... }

Does one maintain a single Entity of FooType in memory/cache for the application's lifetime? Does one read the FooType entity from the db every time one wants to use it?

Creating a new FooType() from code would result in a new FooType inserted into the lookup table, which is not desired (same data, different table id)

What is best practice when dealing with lookup tables?

Could FooType be created as a Singleton?

Answer 1

Just my opinion / practice.

Don't create a lookup table at all - save your queries unnecessary joins. Just have a column directly mapped to an enum property. NHibernate will conventionally use the string version of the enum - if filtering on these columns, add an index to them.

If you really want to use the integer value (why would you, db becomes human unreadable), but say you had to because of some legacy chod, you could use an attribute and a convention...

/// <summary>
/// flags that a property of type enum is persisted with enum item's numerical value 
/// </summary>
[Serializable]
[AttributeUsage(AttributeTargets.Property)]
public class NumericEnumAttribute : Attribute
{
}

/// <summary>
/// convention for the <see cref="NumericEnumAttribute"/> - ensures that the enum value is persisted as an integer
/// </summary>
public class NumericalEnumConvention : AttributePropertyConvention<NumericEnumAttribute>
{
    protected override void Apply(NumericEnumAttribute attribute, IPropertyInstance instance)
    {
        instance.CustomType(instance.Property.PropertyType);
    }
}

If you want to use tables that are readonly - then you should explore both the caching options and the read-only flags of the entity configuration. Flagging an entity as read-only. Great heads-up on that here:

Set up caching on entities and relationships in Fluent Nhibernate?