Anticlue

An Example of One Data for all Lookup types

Earlier this month, I blogged on having one table to contain all reference data. There were a several good comments on the subject, so I thought I would take this opportunity to expand.

First, in the healthcare environment, there is a need for flexibility within applications to add new tables to track new items very frequently. This level of meta data is then passed across the enterprise via an interface engine. All the data is passed at an application level from application to application. It isn't as simple as at the data level or having a generalized Service Pool for all meta data for the instititution. Also when mneumonic data is passed from Application to Application, the export goes throught a translation table, and then in the receiving system, the import also goes through another translation table on its way into the receiving system. For example in the registration system, a discharge disposition of release to home is AHR, in the coding system, the mneumonic value displayed to the users maybe 1, and then in the billing system is may just be AH. Confused yet?

So with this requirement, the other item that happens is regulation come along or new systems arrive, and the thought is to convert the meanings of some mneumonics to synchronize display across the institution. IE in the example above all the systems would display the value of 1 for releasing a patient to home. When we completed this option, it included a data conversion of several production systems. Therefore, it became obvious to have a key field for the lookup data id, being separate from the mneumonic and description.

Having system managers or application analysts, maintain and keep this data up to date is another good practice. The tricky part is moving the administration into the hands of the system managers. This is has a couple of considerations, first the ability to report off of this data is critical and normally handled via SQL Views of the data. The ability to add a field to the system for the display of this information, this is a little trickier.

So how to go about this and what about performance hits? In healthcare the data is interfaced in a realtime fashion to from system to system via HL7. Therefore there is no noticible performance hit from a users perspective, because the interface has already encumbered all of the information. I've seen this data system in several large applications, and the user performance hit was negilible when the indexing was done correctly.

The other added benefit is that this is expandable to any application, the developer only needs to be worried about the views of the information, and the addition of the data elements to an existing table. Over time I hope to include the automation of these items too.

For a little further investigation into what I am describing, please download the zip file. The data management application uses CF, SQL Server and Mach-ii. It is quickly put together for further expansion an idea. Please let me know of any bugs, I'll try to fix them.