IT:AD:Patterns:Reference Data Strategy

The requirements that must be met are as follows:

* MUST: Reference data must be cached as close to usage as possible, in a format as near as possible to final usage.

• Consider using XActLIb/'s XAct.Caching.ICachingService within the Application Layer. Consider using a second cache in the Presentation Layer layer.
• If localisable – which all apps really should be – Consider caching per language code.

* MUST: List items must be logically Enabled

• Consider using XActLIb/'s IHasEnabled contract.

* MUST: Be ordered. Nothing like thinking something is naturally ordered alphabetically, to find out that in another language, the order is different.

• Consider using XActLIb/'s IHasOrder contract.

* COULD: The identifier is normally an int but can be a Distributed Identifier/ if there is any list modifications added by offline remote client. * SHOULD: Be localizable.

The interface to apply to Reference Data is as follows:

public interface IReferenceData<T> : IHasIdentifier<T>, IHasEnabled, IHasOrder, IHasResourceCode  {
  //T Id {get;set;}
  //bool Enabled {get;set;}
  //int Order {get;set;}
}

Data will cached in the Application Layer as follows:

Countries _cachingService.Get<Countries>();

void Add<TData>(string key, TData objectToCache, Func<bool> isValid);

This in other words, starts off looking like:

public interface ILookup : IHasIdentifier<T>, IHasEnabled, IHasOrder,  {
  //int Id {get;set;}
  //bool Enabled {get;set;}
  //int Order {get;set;}
}

But…You have to think of the visible text. And that's where things get weird.

In most apps, in English speaking countries, you'll see it as a Text, or Name column, containing the actual text that is shown in the drop down.

In other words, if we were dealing with an Reference table called Gender with a schema [ID, Name] it would contain something like the following entries (in reverse alphabetical order, not sexist in any way…):

1 Undefined
2 Male
3 Female

One way we could load this reference data into memory would be to use a IReferenceDataRepositoryService (internally using IGenericRepositoryService) to load an ICollection<Gender>.

For perfromance reasons, a clone of the collection (caching the results directly is a bad idea) should be cached in the Application Layer.
At this point, with it cached and readily available in the cache, you have everything you need to perform application logic.

But that's pretty limiting. It can't be easily translated.

[ID, Name]is a red-herring, and the only thing that makes any sense in the db is to change it something akin to [ID, ResourceKey]:

IT:AD:Patterns:SOC to the rescue.

Forget trying to localise the Db for a second – it's a red herring.

The Concerns you have to consider are:

• Persistence (use are a Repository of course)
• Translation

Don't mix the two into one move.

Think of it as a ResourceKey column instead.

That is then used at a higher level against a ResourceHandler/ResourceService.

1 RES011
2 RES012
3 RES013

You can use any code you want, as long as you remember that it is a unique code, not an English phrase:

1 RES_LU_GENDER_U
2 RES_LU_GENDER_M
3 RES_LU_GENDER_F

At the UI layer – the only place you'll really need to localize the string, you will create a second cache – this one cached per language (not user) – created by iterating over the ICollection<Gender> to make a second set of ICollection<Gender>, with the Resource Codes passed through a IResourceService to localize them to the local language.

A reason that this should be done via a ResourceService, and not the Db is that Translation actually is hard. Creating multiple tiers of cached dictionaries to back an ASP.NET ResourceManager, so that it can fallback from a language you may be looking for isn't trivial. Falling through fr-CA to fr to en-US to en to universal turns out to be costly to develop in C#. Even more costly as a stored proc. Avoid.

Making your own Db based backing of the microsoft stack's resource handler doesn't preclude you from keeping your text in the db. I much prefer that option to keeping it in a resource file, so that it's still editable/fixable after release (oops).

You'll note that the above strategy takes care of UI requirements.

It doesn't address Reporting needs.

True.

But before I answer that, let me flip it around…if we didn't do it in the app's code, what would we do? Try to do it via Stored Procs. Not only would it be costly to develop, but it would be under performant as there is no cache mechanism in SqlServer. It just never would have been an optimal strategy.

CORRECTION: There *is* a way to create a cached table in sqlserver: PINTABLE: 
http://www.mssqltips.com/sqlservertip/1317/keeping-data-available-in-the-sql-server-data-cache-with-pintable/
But I don't think that negates the point of this strategy. Localisation logic in a StoredProc is still going to be 
expensive to develop, and using a stored proc at the bottom of the stack
to localise something at the top of the stack, smacks of....not sure...but it smells.

So…what should one do?

Not sure for this edge case. But I'm damn sure that for an app's requirements, solving it at the Db layer is not the performant and maintainable solution.