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.