Add product-flow and pricing controls to your inventory model
Managing inventory is a crucial part of running a business. To control costs, ensure profits, and serve your customers, you need to track the types of products you carry, product categories, quantities in stock, products on order, reorder dates, and suppliers. In "Simple Inventory Metamodel," June 2002, InstantDocID 24585, I created an inventory-management metamodel that helps you with these basic inventory-control activities. But managing inventory is only part of a larger operation called product distribution, which also encompasses product-flow and pricing controls. The inventory-management database isn't sophisticated enough to accommodate product-flow and pricing control tasks, so let's investigate a metamodel that can incorporate these two important functions. You can follow along by comparing Figure 1, an entity-relationship diagram (ERD) that represents the inventory-management model, to Figure 2, page 46, the product-distribution model, which is an extension of Figure 1. I created these models in Sybase's PowerDesigner 9.0. For a comparison of PowerDesigner 9.0 and SQL Server data types, see Table 1, page 47.
Product-Flow Control
The easiest way to review the product-distribution model is to look at it function by function. First, let's look at product-flow control, then pricing control. Product-flow control is a new function in the model that lets you track product location in a distributed-warehouse or retail-outlet environment as well as modes of transporting those products from one place to another. The following sections describe activities that comprise product-flow control.
Tracking batches. First, I added new entities to accommodate the enhanced tracking operation. INVENTORY_BATCH, for example, is a pivotal new entity in the Warehouse subschema. Creating INVENTORY_BATCH as a child entity to INVENTORY helps you avoid making a mistake typical of simple inventory models. Those basic models fail because they assume that all items in inventory that share the same product number (e.g., all red widgets) are made the same and cost the same. But this assumption is dangerous. Even if red widgets bought last month have the same manufacturer or supplier as red widgets bought this month, the two batches might be physically different from each other, or they might be priced differently. The dye that makes the widget red, for example, might be slightly different from batch to batch. Although this distinction wouldn't make any difference in roofing nails, it would be very important in yarn or decorating materials. Adding the INVENTORY_BATCH entity is one way to isolate and track batches.
For each batch, INVENTORY_BATCH's attributes help you determine availability, special requirements, and movement. BatchQuantityOnHand, for example, reports the number of items in a batch that you have in stock. (A batch is the number of red widgets in one order.) Although you can't guarantee that all red widgets in a single batch are identical, the probability that they're identical is high. So in this model, assume that all red widgets in a batch are identical to one another and that you paid the same price for each red widget within a batch. For managing inventory such as yarn, cloth by the yard, wallpaper, and paint—in which color and texture are crucial—you need to know how much stock you have left in each batch. Note that before distribution of red widgets starts, the value of BatchQuantityOnHand equals the value of PO_LINE_ITEM.QuantityOrdered. But as you sell red widgets, the value of BatchQuantityOnHand decreases. BatchExpirationDate tracks a batch's expiration date, especially important for food; BatchSpecialStorageReq designates a special environment you need for storing a particular batch, such as a heated or refrigerated storage area; and BatchDateToBin shows the date and time that a batch is moved into a warehouse bin.
Changes in the Warehouse subschema's INVENTORY table in the product-distribution model also help you track your products. All modified and new attributes are computed attributes that you can populate by trigger code. Quantity has changed to TotalQuantityOnHand—a summation of all BatchQuantityOnHand values from the INVENTORY_BATCH table. TotalQuantityOnOrder is populated by a trigger that fires each time a new purchase order is entered into the database. The source data for this trigger is PO_LINE_ITEM.QuantityOrdered in the Purchase Order subschema. TotalQuantityOnOrder gives you an immediate and accurate count of how many red widgets are already on order so that you can better fulfill your distribution requirements. However, to see shipping and delivery dates for these orders, you'd have to create a purchase-order view. The view might include the PRODUCT, PURCHASE_ORDER, and PO_LINE_ITEM entities—but only for the red-widget ProductID.
INVENTORY_BATCH maintains a peer (1:1) relationship to PO_LINE_ITEM. Each time the company receives an order, for each line item on the purchase order, the PO_LINE_ITEM.LineActualDeliveryDate attribute can be filled in, capturing additional information for each line item in INVENTORY_BATCH.
Backordering, shipping, and storing your products. To better manage product flow, I also altered the entities PURCHASE_ORDER and PO_LINE_ITEM and developed a finer granularity of tracking dates and costs. Because the attributes of those two entities are now associated with each item ordered (PO_LINE_ITEM) instead of with the purchase order itself, you can view, item-by-item, information on back orders, order cancellations, shipping dates (both estimated and actual), shipping methods, shipping costs, and shipping numbers.
Altered relationships in the product-distribution model result in more detailed reporting about storage. In the inventory-management model, INVENTORY was a master entity to WH_BIN. Now, INVENTORY_BATCH is the master to WH_BIN, so you can store each batch in one or more bins. Note that each bin is limited to storing items from a single batch. I made this decision because tracking pricing and expiration dates is easier under these conditions. An enhanced WH_BIN now contains the new BinStatus status attribute, which might contain values such as occupied, empty, or under construction to help you locate available storage units. Also new to INVENTORY is the BIN_TYPE validation entity, which controls the domain of the new WH_BIN attribute, BinType. (Remember, in the PowerDesigner conceptual data model—CDM—foreign keys don't show in the attribute list; they're implied by the relationship.) Typical values for BinTypeDscr might be refrigerated, open area, freezer, heated area, air conditioned—anything that describes a storage area's environmental component. With BinType in WH_BIN, matching the special storage requirements in INVENTORY_BATCH is a straightforward query, comparing the value of BatchSpecialStorageReq to the values of BinTypeCode in WH_BIN.