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.

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 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.

Each type of Packout Item is grouped as a Crop Year, making the proliferation of crop inventory items that would have to be retained significant over time 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.

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 as 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.

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 9 years in project management and consulting as the Vice President of PLM Consulting, Inc. Les holds a Masters of Information Technology, and graduated summa cum laude, from American Intercontinental University.

Contact the author.