The Crops® Custom ERP Application for the Agricultural Industry

Columbia Gorge Organic Fruit Co.
Hood River, OR USA

I was asked by a local fruit packing company to review a failed software project that had been designed to help manage the sales and fulfillment process. I'd grown up around canneries so I wasn't surprised at the complexity of the mechanical processes (sorting, canning, etc), but what I didn't expect was the data complexity involved in transforming crops from their raw unorganized state into their various retail forms of finished goods ready for sale. Over the last 15 years, I've written a number of challenging Inventory Control and Manufacturing applications, but none quite as relationally complex, especially as it applies to developing it in an n-tier environment.

Application size and scope

There are more than 175 web pages, 45 tables, a dozen views, and 900 stored procedures within the Crops application, and all tables are included in a single database.

The number of records in the database is only limited by Microsoft SQL Server and the requirements of the business in question. We design all of our applications to conform to Microsoft 'Best Practices' to allow for high concurrency, data-correctness, and data-integrity.

The project

As an overview of the process, raw materials (crops) are received as Lot Items from growers as they are picked, which are grouped together by Grower, Crop Type, Variety, and Certification to form a Lot. Various portions of each Lot are then assigned to a Storage Type (i.e., cold storage, common storage, etc.) which categorizes them into Lot Pools, because once crops are assigned to a Storage Type, they cannot then be mixed with like crops from other storage types. Lot Pools are then processed in part or in whole through a process called a Packout that produce Packout Items, which is the final salable product. Finally, the Packout Items are re-grouped into crop inventory (or Pool Items) for sale, because the end user doesn't typically care who the Grower was, or on what day of the season it was packed. Growers share equally in the sale of items as a Pool as an average of all sales from that Pool.

The complexity of organizing the purchasing process can be best understood by seeing the various ways in which the goods and the process have to be grouped.

You won't see a Pools or a Pool Items table in the Entity Relationship Diagram (ERD), because Pool Items, being a superset of Packout Items, are represented in the application as a View to create a virtual crop inventory table (Pool Items), from which Sales Orders are procured.

This has a number of advantages, especially since the data source already exists in Packout Items. By using Views to represent these tables, we avoid data-redundancy at a number of levels, significant amounts of transactional code that would otherwise be required to maintain these two tables (especially Pool Items) and to update the various aggregations needed in an inventory table to represent quantities adequately (Qty Produced, Qty Sold, Qty Adjusted, Qty Available). Instead, by using a View, we represent those aggregations by deriving them at run-time which assures their accuracy because there is no data-redundancy.

The business also needs a standard inventory control construct to handle Purchase Orders and Sales Orders for non-manufactured items, shook, etc., which interfaces with the Packout process to represent a bill of materials to capture the costs incurred in the process (boxes, cans, labor, etc). Note that because a Sales Order for selling crop items is so different from a standard Sales Order, we represent them separately, sharing only those entities they have in common.

The resulting (simplified) ERD is shown below.

If Pools and Pool Items were represented as tables instead of views, Pools would be represented as a parent table to Packout Items, and Pool Items would reside between Packout Items and Crop Sales Items, though it would also be a parent to Packout Items (one-to-many).

From an object-oriented technical perspective, this is essentially a number of class extensions starting from the raw crop goods, extending to the finished items, with additional parent classes or class contractions required to accurately represent the business domain decomposition. Architects often represent these class extensions with object ID's or "surrogate keys" (i.e., Lot Id, Lot Pool Id, Packout Id, etc.) However, because of the additional groupings/contractions, reporting requirements, and business analysis requirements, this is ideally represented without surrogate keys, and more true to relational rules where surrogate keys are used only as a last resort. With the application constructed in this fashion, we don't have to perform complicated joins or various "wide row" views in the reporting process to achieve the results desired.

Its also worth noting that if we employed a more standard means of forcing these items into a standard inventory construct, we could use "smart-keys" for the Inventory identification and proper "grouping" (i.e. CropYear, CropType, Variety, Certification, StorageType, Label, Grade, ContainerType, ContainerSize). However, that poses several challenges: when selecting items for sale, the user would be presented with a long list from which to choose, and would be a significant performance issue because of the number of records retrieved. With this construct in hand, when the items are selected for sale from CropSalesItems, the user can filter their selection against a direct View (Pool Items) over Packout Items to narrow their choice by category until they arrive at the item(s) desired (hence the reason for all the joins back to the source). When selecting an item for sale, users choose via a drop down list from each of the above attributes related to their source tables (i.e., CropYear, CropType, Variety, etc.), narrowing the eventual list of possibilities to be selected against the Pool Items view to a small subset of Packout Items.

Each type of Packout Item is grouped as a Crop Year, making the proliferation of crop inventory items that would have to be retained over time significant since they have related records and couldn't be deleted. Since Pool Items (crop inventory) is a view, this isn't an issue and the redundancy between Packout Items and Pool Items is avoided and all reporting possibilities for historical purposes retained.

Code extensions and customizations

The code customizations are limited to relational-integrity customizations between 4th normal form constructs, selection filters, some alignment modifications, and a few validation requirements. We go to great lengths to keep UI tier aspects very thin, and relegate business rules and data-management requirements to their appropriate tier.

Page layout customizations

The only page customizations required were simple adjustments using the Iron Speed Designer configuration options. We really do believe in high-level RDBMS concepts and practices: traverse and collect data methodologies, high levels of normalization, view utilization, etc.

Metrics for success

Because we utilized stored procedures for handling C/R/U/D, and abstract concepts to reduce/eliminate redundant transactional code, we achieved as pure an implementation as possible thereby eliminating bottlenecks that can often result. As queries are added for retrieving data-sets, we process them through query analyzer to ensure optimization.

Iron Speed Designer impact

The most significant aspect is that we attempted several approaches for this project over the last year before using Iron Speed Designer to achieve a marriage of the desired User Interface (UI) with the desired Relational Database Management System (RDBMS) architecture depicted above. In our earlier attempts, we were forced into compromises in one tier or another to arrive at a workable solution though no solution was ideal. Of the approaches we took and the developer tools to we tried, only Iron Speed Designer was able to handle the object-oriented-to-relational complexity without issue.

In addition to decomposing the business rules for automating the production process, we've also been transforming their network into a foundation that could support all the features users wanted from their IT solution. When complete, this will include end-to-end tracking of the production process, a complex label printing integration, and integration with the crop sorting mechanisms by auto-recording the results of their manufacturing process as the goods come off the line via barcode recognition. Equally important is the ability of Brokers and Customers to place orders via the Internet, as well as for Growers to monitor the progress of their fruit at its various processing stages. Once complete, this will be offered as a shrink-wrapped, vertical turn-key solution running on a Microsoft Small Business Server backbone to other packing operations, especially smaller businesses who can't afford the much larger Enterprise Resource Planning solutions or their sizable customization costs.

Next steps

Once complete, this will be offered as a shrink-wrapped, vertical turn-key solution running on a Microsoft Small Business Server backbone to other packing operations, especially smaller businesses who can't afford the much larger Enterprise Resource Planning solutions or their sizable customization costs, and will include end-to-end tracking of the production process, a complex label printing integration, and integration with the crop sorting mechanisms by auto-recording the results of their manufacturing process as the goods come off the line via barcode recognition. Equally important is the ability of others to place orders via the Internet, as well as monitor the progress of their fruit at its various processing stages. Functionality will be added as needed/requested, and it's already designed to handle any number of users, dependent solely on the supporting hardware/backbone of the organization in question. We'll also be marrying our enterprise accounting program ("Books") with inventory control to provide seamless double-entry accounting features.

About the developer

Les Cardwell
President of White Box, Inc.

Les Cardwell, is the President of White Box, Inc. based in Mt. Hood, OR and an affiliate of AKC Development Corp. Les has been with White Box, Inc. since 2004 and specializes in providing custom software development, system analysis/design and IT project management services to customers in the US, Canada, and Europe. Prior to joining White Box, Les spent nine years in project management and consulting as the Vice President of PLM Consulting, Inc. Les holds a Master's of Information Technology, and graduated summa cum laude from American Intercontinental University.