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, InstantDoc ID 20409), product assembly (June 2001, InstantDoc ID 20573), contact management (July 2001, InstantDoc ID 21120), reservations (October 2001, InstantDoc ID 21880), and scheduling (November 2001, InstantDoc ID 22196). 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

• products you carry
• product category that each product fits into
• quantity of each product you have in stock
• suppliers you order the products from
• products you have on order
• product reorder dates

Figure 1 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 2 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.

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, which is a logical model of the simple inventory-management metamodel, rendered in Sybase's PowerDesigner 9.0. (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:

• identification codes (e.g., UPCA, UPCE, ISBN, ISSN, SKU)
• code values
• product names
• manufacturer's product codes
• manufacturer's unit cost
• unit packaging (singleton or by the dozen)
• product dimensions, color, and weight
• product description long enough to use in a print or online catalog