Simple Inventory Metamodel

Nearly every business needs to do some kind of inventory management. From small to large, companies need to keep track of supplies they use in the office and products they sell to their customers. So let's develop a metamodel that you can use to monitor this inventory. For my series of columns about metamodels, I defined a metamodel as a template—a data model of a generic situation (usually a business-related situation) from which you can derive and create models for your own database projects. So far in the series, I've developed metamodels for retail sales (May 2001), product assembly (June 2001), contact management (July 2001), reservations (October 2001), and scheduling (November 2001). Now, let's develop a metamodel that lets you do simple inventory control for an office, a store, or a warehouse. To manage inventory, you need to determine how to track the

Figure 1 (below) represents a conceptual view of the inventory-control metamodel, which incorporates the six kinds of data I identified. I divided this metamodel into three subschemas—CATALOG, PURCHASE ORDER, and WAREHOUSE—and assigned a different color block to each subschema. A blue block identifies CATALOG, the largest of the three subschemas. CATALOG contains not only information about products and product availability (which the PRODUCT_CATEGORY, PRODUCT, and SUPPLY_LIST entities represent) but also information about who manufactures the products (MANUFACTURER) and who distributes the products (SUPPLIER). The MANUFACTURER and SUPPLIER entities are both companies, so I chose a supertype-subtype architecture to represent these entities. A supertype-subtype architecture is a way of organizing entities to show an inheritance-type relationship between them. (For more information about this architecture, see SQL by Design, "Supertypes and Subtypes," May 1999, InstantDoc ID 5226.)

Figure 1

Figure 2 (below) shows an example of a simple supertype-subtype architecture. Automobile—the supertype—has properties that each of the subtype entities (car, van, sport utility vehicle—SUV, and truck) inherits. In Figure 1, COMPANY is the supertype entity; its subtypes are MANUFACTURER and SUPPLIER. Between these two subtype entities is another relationship. For example, a manufacturer can license zero, one, or many suppliers to distribute its products, and a supplier can distribute products for one or many manufacturers. Each manufacturer has a list of products that it manufactures, and each supplier's list holds the manufacturers' products.

Figure 2

The entities in the gold block comprise the PURCHASE ORDER subschema. They represent the name of the person who initiates the purchase order (EMPLOYEE), general information about the entity (PURCHASE_ORDER), and a record of each item ordered (PO_LINE_ITEM). Green designates entities in the WAREHOUSE subschema. One of the block's entities contains information about the location of each warehouse or store (WAREHOUSE), a second entity describes the individual storage bins or areas within each warehouse (WH_BIN), and a third entity lists all the products that are currently available in inventory (INVENTORY).

To understand this metamodel better, let's examine Figure 3 (below), which is a logical model of the simple inventory-management metamodel, rendered in Sybase's PowerDesigner 9.0.

Figure 3

(For an interpretation of PowerDesigner notation, including a comparison of PowerDesigner and SQL Server data types, see the sidebar "Understanding the Notation.") The CATALOG subschema is first under the microscope. Note that I've chosen to implement the subtype tables (MANUFACTURER and SUPPLIER) instead of the supertype table (COMPANY). Data modelers who implement the subtype table do so for one of two reasons: either the attribute sets of the subtype tables differ substantially or the subtype tables are involved in different types of relationships. Figure 3 shows that the subtype tables' attribute sets are similar, but the relationships that these tables have with other tables are different. The two subtype tables have a many-to-many (M:N) relationship, MANUFACTURER is related to PRODUCT in a one-to-many (1:M) relationship, and SUPPLIER and PRODUCT sustain a M:N relationship (which the SUPPLY_LIST table represents). In this subschema, you can store a lot of information about products, including:

In the SUPPLY_LIST table, you can include a supplier's product code (which is usually different from the manufacturer's product code) and a supplier unit cost.

The PURCHASE ORDER subschema is simple. You can perform basic tasks with this architecture, such as tracking—on an item-by-item basis—the employee who placed an order, the products ordered, the date the products were ordered, and the length of time for the order's shipment. The EMPLOYEE table contains just enough information to identify the individual who placed the purchase order (employee identifier, name, and job title). I inserted several calculated columns (OrderSubTotal, DiscountTotal, TaxTotal, OrderTotal) into the PURCHASE_ORDER table, even though I typically don't use calculated columns in my models. Generally, storing calculated values in a database isn't a good idea. You can cause synchronization problems by trying to keep totals coordinated when the values that make up the calculated column change. However, I recommend using calculated columns when you can't accurately reconstruct the calculated value over time or when recalculating the total values in the table or in a query causes serious performance problems. This metamodel has no capacity for storing price history, so because I can't accurately reconstruct the calculated values over time, I included calculated columns in the PURCHASE_ORDER table.

The PO_LINE_ITEM table that accompanies the PURCHASE_ORDER table contains standard line-item columns such as quantity ordered and unit price as well as a calculated column—LineTotal. By using LineTotal, you can capture discounts, shipping methods, and estimated and actual delivery dates on an item-by-item basis. This deviation from the standard metamodel for retail sales gives you more flexibility for managing orders and lays the foundation for an item-backorder management system.

The last of the subschemas—WAREHOUSE—is a generalized warehouse-storage architecture. Each company's warehouse or storage facility will have some special circumstance that you'll need to capture in your database, but for this metamodel, I kept the template generic. The assumption underlying this subschema is that you have one or more places where you store products, either for sale or for redistribution within your own company. I call these places warehouses. Within each warehouse are areas or locales called bins. The WAREHOUSE and INVENTORY tables share a M:N relationship, so each warehouse can house many inventory items, and inventory can be housed in one or many warehouses. Only one product is stored in each bin. In the WH_BIN table, you can capture the dimensions, capacity, and location of each bin. INVENTORY—a listing of products currently stored in each warehouse—takes the basic information about inventory on hand from the PRODUCT table.

This simple inventory-management metamodel can get you started controlling your inventory, whether you're trying to get a handle on inventory volume and costs for a manufacturing company, a product distribution company, or an office. The PURCHASE_ORDER and WAREHOUSE subschemas work together to let you track the inventory you have on hand, the inventory you have on order, and the employees who placed the product orders and the orders to restock the inventory. The CATALOG subschema lets you browse the manufacturer and supplier product listings and—in conjunction with the PURCHASE_ORDER subschema—place orders for products to restock your inventory. In a future column, I'll expand this metamodel to include more complex inventory requirements, such as pricing controls, back ordering, and inventory movement between multiple warehouses.