Accommodating Dynamic Terminology in your App
When building applications intended to service multiple clients, the ability to easily customize certain aspects is a must. In the health benefits management arena, this need couldn't be any greater, most notably with regards to terminology and phrasing. Whereas one client may wish to refer to their employees' usage of tobacco as "smoker certification", yet another will insist on calling it "tobacco usage". In order to accommodate this dynamic requirement, my team and I came up with what we dubbed our "Lexicon" component, so I thought I'd share the basics of it here in case anybody else has a similar need.
The overall concept and approach is this:
Firstly, let's look at the table used to store the lexicon data:
Simple enough, right? So, if we have a title that needs to be displayed within the Third Party Administrator pod (see illustration), we create an itemID of "pod.tpaadmin.title", providing a record for the default wording, one for our client 690 who wasn't happy with our default wording, a default spanish label, and a default rap label.
Now, we need a Lexicon object to perform lookups for us. The relevant method we'll be using ("getTerm") will look for this client's override value first. If it finds it, it will be returned. Otherwise we'll grab the default value. Here's our "getTerm" method:
Last but not least, here's how we utilize the Lexicon object within our display template (using Model Glue's 'Viewstate'...could just as easily be application, session, or request):
The lexicon also turned out to be very handy for managing the text to display within links. We have a seperate table containing links with generic descriptions, then utilizing the GUID of the link record as the termID, created lexicon entries to control what link text and language was displayed for a given client.
Pretty straightforward, eh?
Doug out.
Disclaimer: The code and samples in this post have been written as simply as possible in order to illustrate "how" the lexicon works. In actuality we utilized Reactor and Coldspring to operate our Lexicon. The reader is encouraged to use these snippets as a starting point, but by all means to come up with more efficient ways of execution, object setup, and architecture for his or her own purposes.
The overall concept and approach is this:
- Every term is given a generic name;
- Every term will have a default value with the ability to have customized override values for specific clients;
- Every term is given a language identifier, to accommodate the fact that languages other than english may be needed;
- The component that performs lexicon lookups is made available to every display template in some global scope (application, session, request, viewstate (when using Modelglue), etc.);
- Terms are output inline within the display template via a "getTerm" method call;
Firstly, let's look at the table used to store the lexicon data:
- 'ID' is an autonumbering field;
- 'itemID' contains the generic reference to the term. We utilized a hierarchical naming convention that identified the specific area of the app the term applied to, but any identifier could be used here, including a simple GUID;
- 'ClientID' is either NULL (indicating this is the generic default term to use) or contains the ID of a specific client (indicating that we should use this client's term for this item);
- 'term' is the actual text that will be displayed;
- 'languageID' is the id of a language record from our 'languages' table (a simple list of languages we wanted to support). In our case, 1 = english, 2 = spanish, 4 = rap;
CREATE TABLE [dbo].[Lexicon] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[itemID] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[clientID] [int] NULL ,
[term] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[languageID] [int] NULL
) ON [PRIMARY]
GO
[id] [int] IDENTITY (1, 1) NOT NULL ,
[itemID] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[clientID] [int] NULL ,
[term] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[languageID] [int] NULL
) ON [PRIMARY]
GO
Simple enough, right? So, if we have a title that needs to be displayed within the Third Party Administrator pod (see illustration), we create an itemID of "pod.tpaadmin.title", providing a record for the default wording, one for our client 690 who wasn't happy with our default wording, a default spanish label, and a default rap label.
Now, we need a Lexicon object to perform lookups for us. The relevant method we'll be using ("getTerm") will look for this client's override value first. If it finds it, it will be returned. Otherwise we'll grab the default value. Here's our "getTerm" method:
<CFFUNCTION name="getTerm" returntype="string" access="public" hint="I return the appropriate term for a given itemID" >
<CFARGUMENT name="termID" required="true" type="string" hint="I am the id of the term we're looking for. Typically this will be either a string or a UUID" />
<CFARGUMENT name="languageID" type="numeric" required="no" DEFAULT="1" hint="I am the id of the language we want to use. default is 1 for english." />
<CFARGUMENT name="clientID" required="true" type="number" hint="I am the id of the client associated with this lookup." />
<CFARGUMENT name="DSN" required="true" type="string" hint="I am the DSN to use for queries." />
<cfset var local = structnew() />
<!--- look for the client's override value first... --->
<CFQUERY name="local.getTerm" DATASOURCE="#arguments.DSN#">
SELECT term
from lexicon
where
clientID = <cfqueryparam value="#arguments.clientID#" CFSQLTYPE="CF_SQL_INTEGER">
AND
languageID = <cfqueryparam value="#arguments.languageID#" CFSQLTYPE="CF_SQL_INTEGER">
AND
termID = <cfqueryparam value="#arguments.termID#" CFSQLTYPE="CF_SQL_VARCHAR">
</CFQUERY>
<cfif local.getTerm.recordcount neq 1><!--- nothing client-specific returned, grab the default term --->
<CFQUERY name="local.getTerm" DATASOURCE="#arguments.DSN#">
SELECT term
from lexicon
where
clientID IS NULL
AND
languageID = <cfqueryparam value="#arguments.languageID#" CFSQLTYPE="CF_SQL_INTEGER">
AND
termID = <cfqueryparam value="#arguments.termID#" CFSQLTYPE="CF_SQL_VARCHAR">
</CFQUERY>
</cfif>
<CFRETURN local.getTerm.term />
</CFFUNCTION>
<CFARGUMENT name="termID" required="true" type="string" hint="I am the id of the term we're looking for. Typically this will be either a string or a UUID" />
<CFARGUMENT name="languageID" type="numeric" required="no" DEFAULT="1" hint="I am the id of the language we want to use. default is 1 for english." />
<CFARGUMENT name="clientID" required="true" type="number" hint="I am the id of the client associated with this lookup." />
<CFARGUMENT name="DSN" required="true" type="string" hint="I am the DSN to use for queries." />
<cfset var local = structnew() />
<!--- look for the client's override value first... --->
<CFQUERY name="local.getTerm" DATASOURCE="#arguments.DSN#">
SELECT term
from lexicon
where
clientID = <cfqueryparam value="#arguments.clientID#" CFSQLTYPE="CF_SQL_INTEGER">
AND
languageID = <cfqueryparam value="#arguments.languageID#" CFSQLTYPE="CF_SQL_INTEGER">
AND
termID = <cfqueryparam value="#arguments.termID#" CFSQLTYPE="CF_SQL_VARCHAR">
</CFQUERY>
<cfif local.getTerm.recordcount neq 1><!--- nothing client-specific returned, grab the default term --->
<CFQUERY name="local.getTerm" DATASOURCE="#arguments.DSN#">
SELECT term
from lexicon
where
clientID IS NULL
AND
languageID = <cfqueryparam value="#arguments.languageID#" CFSQLTYPE="CF_SQL_INTEGER">
AND
termID = <cfqueryparam value="#arguments.termID#" CFSQLTYPE="CF_SQL_VARCHAR">
</CFQUERY>
</cfif>
<CFRETURN local.getTerm.term />
</CFFUNCTION>
Last but not least, here's how we utilize the Lexicon object within our display template (using Model Glue's 'Viewstate'...could just as easily be application, session, or request):
<cfset lex = viewstate.getValue("lexicon") />
<span class="contentTitle">
<cfoutput>#lex.getTerm(termid="landing.pagetitle",clientID="690",DSN="#application.dsn#")#</cfoutput>
</span>
<br />
<span class="subContentTitle">
<cfoutput>#lex.getTerm(termid="landing.subtitle",clientID="690",DSN="#application.dsn#")#</cfoutput>
</span>
<span class="contentTitle">
<cfoutput>#lex.getTerm(termid="landing.pagetitle",clientID="690",DSN="#application.dsn#")#</cfoutput>
</span>
<br />
<span class="subContentTitle">
<cfoutput>#lex.getTerm(termid="landing.subtitle",clientID="690",DSN="#application.dsn#")#</cfoutput>
</span>
The lexicon also turned out to be very handy for managing the text to display within links. We have a seperate table containing links with generic descriptions, then utilizing the GUID of the link record as the termID, created lexicon entries to control what link text and language was displayed for a given client.
Pretty straightforward, eh?
Doug out.
Disclaimer: The code and samples in this post have been written as simply as possible in order to illustrate "how" the lexicon works. In actuality we utilized Reactor and Coldspring to operate our Lexicon. The reader is encouraged to use these snippets as a starting point, but by all means to come up with more efficient ways of execution, object setup, and architecture for his or her own purposes.