Appending waybill elements to rail shipment tracking CLM event data may not be cost effective
If you are getting CLM event data from one of the rail industry VANs (value added networks) and they offer an option to append elements from the waybill to each CLM event record, it may seem like a simple solution, but there are numerous problems to consider first.
Problem 1: Most VANs charge by the kilobyte or at least charge you extra for each CLM event that has the appended waybill elements. CLM event data records are cheap, right? Sure, each record is only a few cents, and adding the waybill elements only add a fraction of a penny to the cost of each record. However, this adds up when you are talking about hundreds of thousands even millions of records, which can be the case for a company with several hundred or more shipments per month.
Problem 2: The average, one-way, loaded or empty rail shipment has about 30 to 50 (or more) CLM event records. Think about this. When you add waybill information to each CLM event record, such as bill of lading date/time, purchase order number, commodity, etc., this information is duplicated on each of those 30 to 50 (or more!) CLM event records. Ah, but disk storage space is cheap you say. The bloating of your database will cause, in some cases, slower response time, increased bandwidth usage and it is just bad practice to store a piece of information 50 times when really only once is required. How can that be? Keep reading and later in the article, I will explain a simple database concept called relational database design.
A Solution To Consider
Get the waybill data directly from the Class I rail carriers. Most of the Class I’s will provide an electronic copy of the waybill to the parties that are listed on the waybill (shipper, consignee, freight payer) for no charge. These Class I’s now have FTP (file transfer protocol) servers, so the waybill records are easy to download with just an internet connection and an FTP client software like Windows Explorer. The waybill documents will be in EDI (electronic data interchange) 417 format, so you will need a software to translate them and get them into your database.
Create a table specifically for waybill data and only waybill data. Then create a table for the CLM event data. Using relational database technology, you will then be able to “relate” the two tables by matching the railcar initial, railcar number, load/empty status and an a formula that matches the waybill date or bill of lading date (both are provided on the waybill) to determine which waybill should should be shown with the particular CLM event data records.
There is some work involved here, but I think you will find that in the end, you will have a less expensive, better performing rail shipment tracking system.