Columbia Gorge Organic Fruit Co. |
Crops® Custom ERP Application |
"Of the approaches we took and the developer tools we tried, only Iron Speed
Designer was able to handle the object-oriented-to-relational complexity without
issue."
- Les Cardwell, President of White Box, Inc.
|
|
|
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.
|
|
|
|
|