Working with reference data: Lookups part 2 - A generic solution for reference data

In part 1 I talked about certain objects that are often present in an application’s model that are static lists of reference data that can be identified as a Lookup which can have three purposes:

1. A means of defining entities through configurable attributes, such as the DeliveryOption class described in part 1.
   
2. A way of grouping entities to a certain type; perhaps a TeamMember’s RoleType or a Product’s Category.
   
3. An object that has no meaning in the domain and serves no other purpose than providing information to users or reports; a Person’s Title for example.

Lookups have a common structure to them. Each item has an ID, description, code (optionally) and a set of attributes. Applications often have a table for each lookup,  i.e. a Title table or a DeliveryOption table. But given that all lookups have the same structure it makes more sense to create a generic data structure, meaning:

1. The data access code only needs to be written once, rather than for each type of lookup.
   
2. New lookups can be added with ease simply by adding data rather than schema changes and new data access code.
   
3. All lookups can be configured by the users in one central screen; we wouldn’t have to create a config screen for each one.
   
4. Given that reference data rarely changes it makes sense to cache the data. With a generic solution, lookups can be cached at one central access point as opposed to a cache for each type.

In part one I explained why code should reference ‘attributes’ rather than a row ID or string literal. This was achieved by referencing a specific boolean column that represented the piece of logic that requires a decision to be made. An example of this was the ‘IsRoyalMail’ column, whereby the value was used to decide whether the  order should be sent to the post office or a courier. Since we’re trying to create a generic solution, it’s impractical to keep adding new columns to our generic lookup item table – for a large system there could be hundreds/thousands of columns that represent points of logic. Instead we can create a new table called ‘Attributes’ that will represent these points of logic. Consider the following data structure:

• Lookup – This table is the grouping table for the individual items. There would be one record per type of lookup, i.e. Title, DeliveryOption, RoleType
  
• LookupItem – This table stores the individual items. i.e. “Mr”, “Mrs” “Miss”, “Doctor”.
  
• Attribute – This table simply stores a list of points of logic. i.e. “IsRoyalMail”, “IsCourier”.
  
• LookupItemAttribute – The link table that stores which lookup items have which attributes. i.e. The “Royal Mail” DeliveryOption has the “IsRoyalMail” attribute.

In part one, the ‘attribute’ columns contained either true or false. You may however wish to store other information against a particular attribute. Perhaps you wish the UI screen to change colour depending on the gender of the person entity. The link table ‘LookupItemAttribute’ has a ‘Value’ column, making this possible. By defining the attribute ‘GenderScreenColour’ and linking this to both the male and female gender lookup items you are able to specify different values for the colour you want. For maximum flexibility the ‘Value’ column could be an image/binary column meaning you can serialize any type of object that represents configurable values; such as a colour.
Once we have this structure in place, managing reference data becomes a piece of cake, particularly with the use of generics:

1:        ILookupService lookupService = ServiceLocator.Current.GetInstance<ILookupService>();  
2:        //Get all roles  
3:        ILookup<TeamMemberRoleType> roles = lookupService.GetLookup<TeamMemberRoleType>();  
4:        //Get specific role by ID  
5:        TeamMemberRoleType role = lookupService.GetLookupItem<TeamMemberRoleType>(command.RoleTypeID);  
6:        //Get the developer role  
7:        TeamMemberRoleType developerRole = TeamMemberRoleType.GetDeveloperRole(roles);  

Here is an example of how we can now define our lookup types:

1:    [Serializable]  
2:    public class TeamMemberRoleType : LookupItem<TeamMemberRoleType>  
3:    {  
4:      internal static readonly Guid DeveloperAttributeID = new Guid("89FD406C-034E-4F12-938C-8DF5C783F153");  
5:      internal static readonly Guid TesterAttributeID = new Guid("8D768B47-93DA-40D9-8581-743501C0E1B6");  
6:      internal static readonly Guid ProductManagerAttributeID = new Guid("EA16D97A-DC2A-4468-96F7-8796FC0085EB");  
7:    
8:      public bool IsDeveloper  
9:      {  
10:        get { return HasAttribute(DeveloperAttributeID); }  
11:      }  
12:    
13:      public bool IsTester  
14:      {  
15:        get { return HasAttribute(TesterAttributeID); }  
16:      }  
17:    
18:      public bool IsProductManager  
19:      {  
20:        get { return HasAttribute(ProductManagerAttributeID); }  
21:      }  
22:    }  

The base type LookupItem<T> provides most of the standard properties that we need for our lookup (such as description and list of attributes it has). All we are left to do is add our type specific properties which the application can make decisions on. At the top of the class you’ll notice that the attribute ID’s are hard coded. You may be thinking “but I thought he said don’t code against IDs?”. Which is true, I did. The difference here is that attribute ID’s represent a logic point in the application, NOT a configurable database record that the user may wish to remove or add items to. The ID 89FD406C-034E-4F12-938C-8DF5C783F153 is an identifier for the application signifying a developer role. Even if the attribute record is accidentally removed from the database, within the application code that ID remains as an identifier that represents the developer role. By having these ID’s we can then have public properties on TeamMemberRoleType that indicate the type of role without the need to have a boolean column on our table for each one.
For the Gender example we could implement a GetColourFromAttributeValue method which would deserialise the attribute value to a Color object (or perhaps the value would store the RGB value), and then provide a property on our Gender lookup such as:

1:      public Color ScreenColor  
2:      {  
3:        get { return GetColorFromAttributeValue(GenderScreenColorAttributeID); }  
4:      }