EDI to Database

Databases are almost unavoidable when working with EDI files, simply because EDI is the exchange of data between computers; and databases are where computers store their data. 

It is also not surprising to find similarities between an EDI file and a database.  Both contain data, as well as information about the data they contain. One can look at the format of an EDI file as an efficient container of data for transport; while a database, as a container of data for storage.

However, although similarities exists between these two technologies, the format of their file structure is different, which makes it difficult for users (especially those that are new to EDI) to connect the relationship between the two. Unfortunately, many tend to conclude that EDI is a ‘monster’ technology too complicated to undertake.  But in reality, if one can understand relational database and database structure, then one will find EDI to be just as simple.

This article does not cover the basics of EDI, but a PowerPoint show, "Basics of EDI", is available for download at our website (http://www.edidev.com/edidev_docupdate.htm) for those who are EDI beginners. 

Additionally, this article will frequently refer to Framework EDI and its utilities.  So please download Framework EDI from our website at (http://www.edidev.com/ediregis.htm), and install it so that you can easily follow the examples in this article.

EDI Implementation Guideline

An EDI file, in its raw format, looks cryptic, which is the reason why people find EDI to be difficult – it’s unreadable.  But, we just have to keep in mind that the EDI format was not made to be read by us, but was created specifically for computers so that they can parse it easily.  However, we can obtain information about an EDI format, from its associated document called the implementation guideline.  The implementation guideline is probably the only document that you would ever need for obtaining instructions about how to process EDI files of your trading partner.  Before EDI files are exchanged, trading partners have to agree on the EDI format and mapping.  These rules and instructions are entered into the implementation guideline document.  Depending on your trading partner, the implementation guidelines can come in many formats.  Some companies publish their implementation guidelines in .pdf, .doc or .rtf file extension.  But a more efficient method of publishing the implementation guideline is by SEF (or Standard Exchange Format) files because the guidelines are entered in a standard format, which can readily be translated by a computer, as well as a user with the help of a SEF Reader.  (Read more about SEF files and the SEF Reader at http://www.edidev.com/SefReader.htm)

The implementation guideline would be the document to use for comparing the EDI file structure against your database structure.  With the EDIdEv SEF Manager, the implementation guidelines in a SEF file can be viewed in a hierarchical format to show (and edit) the structure of a Transaction Set (Message).  (Read more about the SEF Manager at http://www.edidev.com/SefManager.htm)

The EDI File Structure

Although an EDI file, and a database are similar in many ways, there really isn’t a one to one correlation between them; even though a lot of people for the purpose of simplicity compare data elements of an EDI file to fields of a database, and a data segment of an EDI file to records of a database.  This comparison may be acceptable to introduce people to EDI, but does not help when parsing and translating an EDI file into a database.

Data Segment vs. Database Field
In actuality, it is more accurate to say that a data segment of an EDI file is like a field of a database; and a group of these data segments would make up a record in a database.  For example, compare the DTM segment of an EDI file, and a date field of a database. 

DTM*002*20040723

The above data segment has in the first data element a value “002”, which is a code that means “Delivery Requested; and in the second data element, the date value “20040723”.  The DTM data segment basically contains the Delivery Requested Date information, which is “07/23/2004”.  This data segment can be mapped into a database field below:  

Delivery Requested Date

07/23/2004

Data Segment Group or Loop vs. Database Record
Just like many fields would make up a record in a database, a group of data segments would also make up a record, and should be mapped into a record in a database.  Below is an example of a group of data segments that would make up a record in a database. 

ST*850*00023  
BEG*00*SA*0101  
DTM*002*20040723  

The above data segments would get mapped into a database record below:   

Loops vs. Database Table 
Therefore each iteration of the same groups of segments or loops would be an additional record, and the whole set of records would compose a table in a database.  For example, the groups of data segments in an EDI file

ST*850*00023  
BEG*00*SA*0101  
DTM*002*20040723  
…  
SE  
…  
ST*850*00024  
BEG*00*SA*0102  
DTM*002*20040723  
…  
SE  
…  
ST*850*00025  
BEG*00*SA*0107  
DTM*002*20040723  

...would get mapped into the following table in a database 

However, it is important to determine the loop entity identifier, because an EDI file can use the same loop template to hold a completely different set of information.  For example, there can be two instances of the same N1 loop: one loop to contain the "Bill To" information; while the other loop, to contain the "Ship To" information.  In this case,  two separate tables would have to be created (in a normalized relational database) to store the information.  For example an EDI file with the following content...

ST*850*00023~  
BEG*00*SA*0101**20040723~  
DTM*002*20040723~  
N1*BT*COMPANY ABC~  
N3*123 DRIVE STREET~  
N4*STARCITY*CA*76503~  
N1*ST*INC XYZ~  
N3*987 AVENUE ROAD~  
N4*RANCHCITY*TX*30603~  
PO1*1*16*EA*12.00**CB*000111111~  
PO1*2*13*EA*30.00**CB*000555555~  
…  
SE*30*00023~  

...if viewed in the EDIdEv eFileManager, shows 5 loops (including the Transaction Set/Message that starts with the ST segment, and ends with the SE segment).

However, only 4 loops contain different sets of information, and should be mapped into their own tables.  The last PO1 loop is a second iteration of the same PO1 loop entity, and is therefore mapped and added into the same Purchase Items table.  See below.

Message Table

Message No	Purchase Order No	Deliver Requested Date
0023	0101	07/23/2004

Bill To Table

PO No	Company Name	Address	City	State	Zip
0101	COMPANY ABC	123 DRIVE STREET	STARCITY	CA	76503

Ship To Table

PO	Company Name	Address	City	State	Zip
0101	INC XYZ	987 AVENUE ROAD	RANCHCITY	TX	30603

Purchase Items

PO	Quantity	Unit	Price	Product ID
0101	16	EA	12.00	000111111
0101	13	EA	30.00	000555555

Dumping an EDI file into a Database

Many programmers especially those who are new to EDI have the erroneous idea that it is easier to “dump” data from an EDI file into a flat table so that they can later manipulate, organize and transfer the data into a relational database.  They also incorrectly think that this method avoids them from having to know the EDI file structure.  But, not only is this thinking wrong (since it takes just about the same amount of coding to dump data into a flat table, as it would to translate an EDI file directly into a normalized relational database); but it is also inefficient, which has a greater chance of creating errors because it creates two processes that would have to be executed in series before the data in the EDI file can finally be used. 

The problem with just dumping an EDI file into a database table without any mapping or translation, is that an EDI file has codes called qualifiers or entities that describe the data.  Without translating this qualifiers or entities, the data would make no sense.  For example, an EDI section with the following data 

BEG*00*SA*0101**20040723~  
N1*BT*COMPANY ABC~  
N3*123 DRIVE STREET~  
N4*STARCITY*CA*76503~  
N1*ST*INC XYZ~  
N3*987 AVENUE ROAD~  
N4*RANCHCITY*TX*30603~

…has loop entities “BT” and “ST”, which means “Bill To” and “Ship To” respectively.  So if someone where to dump this section into a table without any translation, they would get a table that would look like:

Therefore another process would have to get created and run, to translate the above database so that the data can finally give out useful information as shown below.

Purchase Order Table

PO No	PO Date
0101	20040723

Bill To Table

PO No	Company Name	Address	City	State	Zip
0101	COMPANY ABC	123 DRIVE STREET	STARCITY	CA	76503

Ship To Table

PO	Company Name	Address	City	State	Zip
0101	INC XYZ	987 AVENUE ROAD	RANCHCITY	TX	30603

 Another problem with dumping an EDI file into a flat table, is that the flat table becomes inefficiently huge with many redundant data.  For example, using the same section of an EDI file ...

ST*850*00023~  
BEG*00*SA*0101**20040723~  
DTM*002*20040723~  
N1*BT*COMPANY ABC~  
N3*123 DRIVE STREET~  
N4*STARCITY*CA*76503~  
N1*ST*INC XYZ~  
N3*987 AVENUE ROAD~  
N4*RANCHCITY*TX*30603~  
PO1*1*16*EA*12.00**CB*000111111~  
PO1*2*13*EA*30.00**CB*000555555~  
…  
SE*30*00023~  

...we would end up with a flat table as shown below.

The redundancy of data in the flat table can be clearly seen from this example with a small portion of an EDI file.  Had there been more loops in the EDI file, the size of the table would have been exponentially bigger.  The size as well as the redundancy of data in the flat table would make managing and translating the information over into an existing production database difficult.

On the other hand, if  the EDI file were to be translated directly into a normalized relational database, the transfer of data over to the production database would be a one-to-one correlation between the fields of the two databases.

Figure 1.  Illustrates the relationship between the temporary database and the production database.

Click here to download a sample Visual Basic program that translates an EDI file into an Access database.  (Sample program requires Framework EDI)

Click here to download Framework EDI 

If you have further questions, please email support@edidev.com.

EDIdEv provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. EDIdEv  support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.

  Home