The problem: Often, EDI data elements of the same type may occur multiple times. One common example is “Dates”. The number of Dates needed (or sent) is generally limited [only] to the number of Dates required by the business application of the party who “calls the shots”. If the customer expects to retain a trading partner’s business, they will make every attempt to comply with that trading partner’s requirements. The trading partner might send the supplier Dates such as “Scheduled Shipment Date”, “Expected Delivery Date”, “Cancel Date”, or a host of other possible dates. The same is true for Reference Numbers, Notes/Comments, Product Item Numbers, Addresses, and so on…they all represent EDI data that could be sent in abundance. The question is, “what is the most efficient way to configure the EDI system to accommodate these repeating (and sometimes redundant) pieces of data?”
ANSI X12 [syntax], while an EDI “standard”, can strangely appear to be far from it. Some “multiple occurring” pieces of data are repeated a fixed number of times within the same segment (e.g. Product Item Numbers inside a PO1 segment), while others can be repeated through multiple segment occurrences (e.g. Dates through repeating DTM segments). As confusing as it might seem, the problem is often “how best to store the data” – by repeating pieces of data being moved into repeating fields of a single record/row, or to a separate file/table altogether?
Some argue it is best to keep the number of files/tables to a minimum; others that “flexibility” is most important, and employing a separate table for each repeating set of data is the most flexible approach. Having faced many implementations over many years, I have witnessed every configuration possible – from a single file/table that has a field/column for every possible piece of EDI data [to be] received, to many files/tables where the number is in direct correlation with the number of received EDI segments (one file/table per segment). The answer falls someplace between these two extremes.
When repeating pieces of data are contained within a single segment, those repeating “elements” are generally set to a fixed maximum number of occurrences (“generally”, because there are a few instances where elements can actually “repeat” within the same segment). For these situations, there is a known (maximum) number of occurrences that is more than one but typically less than ten so retaining fields [for each] inside one record/row is most practical…why create a file/table that will generate a new record/row for each occurrence when a known number exists and it is often limited to just a few? Conversely, when repeating pieces of data are transmitted through multiple segments, each segment has one set of relevant elements, however, the segment can generally occur an unlimited number of times (“generally”, because there are a few instances where repeating segments do have a set number of maximum occurrences). For these situations, the infinite possibilities doesn’t lend well for having separate fields in a single record/row…the occurrences could change anytime and immediately the database could be short of space.
The solution: While repeating segments have the “infinite occurrence” designation, fortunately, companies are smart and know the problems this could present. They will determine and document what is to be sent, and by assessing your general trading partner base it is possible to come up with an overall view of how a certain segment might be used across the board. My rule of thumb and magic number is “four” (4) – if the segment could occur four times or fewer, then it is my practice and recommendation to create multiple fields/columns in a single record/row (plus one additional set for future use). If the segment could potentially occur more than four times, then I recommend a separate file/table as this would save space in the database if no segments of this type are received but also creates more flexibility for growth (if additional occurrences are received).
The one exception to this rule is in the case of “loops” – groups of segments that repeat together (e.g. the N1 loop, consisting of the N1, N2, N3, N4 and potentially other segments). In this case, the number of fields/columns needed to support this amount of data is many and my recommendation and magic number is then “one” (1) – if the loop of segments will occur two or more times then, “table them!”