“Extend” or “Expand” the EDI Database?

Standards only remain “standard” until the next change. Even in the EDI world where trading partner data exchanges are “supposed to be” standardized, new additions and upgrades to later versions will create an opportunity to re-evaluate how data is being moved and stored for company consumption.

Traditional EDI transactions are built upon the “Header” and “Detail” premise where a single iteration of header information could be followed by many iterations of detail information. This makes it simple to build a database in support of this structure – a single transaction would see one record moved into the header file, and one or more records moved into the detail file. Header and detail would be linked together through common key values and sequential controls.

As requirements mature, changes are made and generally new information is presented. This could come by way of additional (single or multiple pieces of) data that could be stored in either the header or detail file. Trouble begins to brew when this new data is defined [by itself] as repeating information. For example, should “notes” be introduced to the transaction it is conceivable by standard that an unlimited number of notes could be sent in either the header or detail section. Assuming this new information is needed in the application, two options exist. First, the database could be extended, or second, the database could be expanded (to hold this new information).

Extending the database spreads the file in a linear direction – stretching the current length of the file. Expanding the database spreads in multiple directions. So, which is considered best practice? To make this determination it is necessary to take the example of the new “notes” and apply it to both options.

In the “extended” option, it would stand to reason that if the notes applied to header that the file would be extended in length. If the company anticipated getting five separate notes that could each be 120 characters in length, the header file would see five new fields added – each being 120 positions. Problem: What if six or more notes were received? There would be no room at the inn and any number of notes beyond five would be lost. What if zero notes were received? Then five fields of 120 characters (600 positions) would sit empty – a very “inefficient” database design.

In the “expanded” option, neither the header nor the detail file is extended but instead the database is expanded by adding a new file specific to “notes.” Each note will create a new record in the notes file. If five notes are received, five records will be added. If 100 are received, 100 will be added. If zero are received then the file will be empty – a very “efficient” database design.

In 1996, U.S. Congress enacted the Health Insurance Portability and Accountability Act (HIPAA). Several years later HIPAA crept into the B2B EDI industry and suddenly applications that were designed to handle flattened HCFA-1500 and UB-92 Health Care Claims were challenged with multi-level (hierarchical) X12-837 Health Care Claims. This wasn’t like moving from “A to B”…this was like moving from “A to Z”…and was nearly impossible without a complete overhaul of the backend application. Attempting to “extend” existing applications proved to be more painful than disc replacement surgery. However, taking the “expanded” approach made it possible to reconstruct new (many) files for this new multi-level/occurrence information.

As new requirements are introduced, adding a new file certainly beats the process of trying to modify and extend the existing production system. All databases, standards, environments, customers, and industries will present unique challenges, but success begins with a well-designed database…and one that can be easily “expanded” to accommodate all future change requirements.