What is a “Staging Database,” and Why Consider It for EDI?

For this discussion, a “Staging Database” refers to any intermediate database that would fill the gap between EDI software transactions and the back-end business application. Whether obtained through a third party or developed in-house, some applications provide interface files that are “EDI Intelligent” and support the required EDI transactions. For applications that do not, the Staging Database should be considered for the following reasons and with the following purposes:

Data Grouping/Restructuring: Spreadsheet-type (SDQ) orders, where a single purchase order describes multiple ship-to destinations, might not be supported in the application because many applications define an order as a single ship-to destination. In this case, the single spreadsheet order needs to be broken down into separate orders by ship-to destination prior to insertion into the application. A multi-line order could have the same ship-to destination repeated several times. Therefore, data grouping would be required for later reorganization of all lines (for each ship-to) and transforming them into a single order within the application.

The Staging Database would provide sorting/grouping functionality to support such reorganization. For example, an order with two lines, each having three SDQ pairs (defines the “ship-to location” and “quantity” breakdowns), and where one location is repeated on both lines, might result in six orders instead of five without such grouping.

User Interface: Some received transactions may require user review and approval (hold and release) prior to entering the application system based on company policies. Because many users are not familiar with the structuring of raw EDI data, the Staging Database would provide for such report generation, and review and approval screens. For example, address books or organizational relationships received from customers/suppliers might need to be reviewed and associated with internal location identifiers prior to being accepted into the application.

Turnaround Transactions: These are “reporting” transactions and are typically the result of an inquiry received from the trading partner seeking status information. In many instances, the details of the status requests are not required by the application, but they are required as part of the status returned to the partner.

For example, an Order Status Inquiry received from the partner (to check the status of an order) may not be directly supported in your application, although the actual order status can be determined from the application’s data set. The Staging Database would make it possible to “hold” this data to be later retrieved during the outbound (return) process.

Turnaround Supplemental Data: This would include data received from incoming transactions, and though not required by the application, they are required (by the trading partner) to be sent back to them on return transactions.

For example, a customer may require their Supplier or Department ID on the Invoice sent back, but that data might not be applicable to your business, or the application might be incapable of storing this data. For this case, the information could be stored and then later retrieved from the Staging Database.

Although EDI was the format specifically discussed and used to justify the implementation of a Staging Database, the examples shared would apply to any other externally shared data format.