Product-Distribution Metamodel

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.

Extending this model to track inventory across warehouses requires that you make additional changes to the model. In the original high-level conceptual model that I created for the inventory-management model, I explained how MANUFACTURER and SUPPLIER were in fact two categories of the same entity (subtype entities of the supertype entity COMPANY). If you're transferring products from one warehouse to another, you (or your company) become the supplier, and the new relationship between SUPPLIER and WAREHOUSE confirms that you (the supplier) have products stored at multiple locations. The relationships between WAREHOUSE and PURCHASE_ORDER indicate the source of the products (product_origin) and the delivery address (product_destination). I added another entity, REGION, which identifies the geographic or political region that each warehouse belongs to. (You can also use this entity to associate employees with a region.) If you track inventory from warehouse to warehouse, using the Purchase Order subschema to record the date you transfer the products gives you a complete record of inventory movement. The record details what you transferred, when you transferred it, how much you transferred, where it came from, and where you delivered it. Another new validation entity, SHIP_METHOD, lets you control the values you use for the shipping method. You might ship each item in PO_LINE_ITEM a different way to optimize your product-transportation budget. Typical values for ShippingMethod might be FedEx, UPS, and USPS.

Pricing Control

Pricing control is a new function in the product-distribution model that gives you an instant report on how much you paid for a batch of product, when you ordered the batch from the distributor, the suggested retail price, and the price history. This type of information lets you readily calculate your anticipated profit margins and your costs for given reporting periods.

Storing price history. The basic control for storing price history (i.e., for recording the average price you've paid for a product over time) lies in the Catalog subschema's INVENTORY_PRICE_HISTORY entity. You can design your model so that triggers populate this table's three non-key attributes—HistAvgPricePaid, HistSuggRetailPrice, and HistDateRecorded—whenever a change occurs in the value of INVENTORY.AvgPricePaid. However, be aware that your triggers might cascade from PO_LINE_ITEM.LineTotal to INVENTORY.AvgPricePaid to INVENTORY_PRICE_HISTORY.HistAvgPricePaid, so you'll want to evaluate this configuration carefully before putting it into production. Cascading triggers can cause a lot of contention with other production programs because the cascading triggers have to hold record or page locks on the tables they're modifying until a transaction is complete. If you adopt this model for your product-distribution database project, you'll probably want to build in additional price-history controls, perhaps for shipping costs. You can use this price-history table as a template to guide your efforts.

Monitoring product costs. Changes in the INVENTORY entity that I referred to earlier include pricing enhancements. RetailPrice in the inventory-management model has changed to AvgPricePaid—an average of the LineTotal values from PO_LINE_ITEM for all items that have the same ProductID. New to INVENTORY is SuggestedRetailPrice. By making the value of SuggestedRetailPrice a percentage increase of AvgPricePaid, you can float the retail value of an item based on your costs. This capability lets you know at any given moment what your product costs are and what your projected profit should be. If you've been able to buy items at a lower price, you can pass the savings along to your customers almost immediately. You now have an immense advantage over your competitors—who, of course, don't have this model to work from and must price their products manually.

The product-flow and pricing functions are the two major enhancements I made to the inventory-management metamodel. I also added a minor enhancement—the new IDENT_TYPE validation entity, which defines PRODUCT. IDENT_TYPE to PRODUCT is a one-to-many (1:M) relationship; IDENT_TYPE lets you assign to each product a unique identifier that describes the product and its packaging. These unique identifiers are ISBN values (e.g., the title of a hardbound book has a different ISBN from the title of the book's paperback version), ISSN values (e.g., for magazines or regular publications), USP codes (both USPA and USPE), and SKU values. The IDENT_TYPE validation entity helps define PRODUCT.IdentificationCode's value.

Adapt the Template

Product distribution—encompassing inventory management—is a challenging operation that requires the support of a well-designed database. Although this product-distribution metamodel looks complex, it's just a template that you can use to get started on your own project. As you work with the model, you'll probably discover that functionality your organization needs is missing. Using the principles of good data modeling that I've presented in this column, you can expand this model until you've designed an adaptation that works in your production environment.