SQL Server Magazine

My series about metamodels began with a look at the retail-sale metamodel (see "Metamodel for Retail Sales," May 2001). Understanding that a metamodel is a template that you can use to jump-start your own data modeling projects, let's proceed to create a product-assembly metamodel, an extension of the retail-sale metamodel. Every company sells something, either goods or services, and sometimes both. Many companies also manufacture or assemble their own products. The product-assembly metamodel gives a picture of the assembly and manufacturing process while incorporating aspects of the retail-sale metamodel. Most people are aware of the components of a sale (i.e., a buyer, a seller, a product, and a place), but not everyone is familiar with the product-assembly process.

The Production Line
To start your data-modeling process, you need to name the product assembly's core components, which become your model's entities: RAW_MATERIAL, assembly (or manufacturing) PROCEDURE, production EMPLOYEE, and finished PRODUCT, as Figure 1 shows. Although I label a product's composition as raw material, sometimes a product can be a collection of other products, such as a dining room set composed of a table and four chairs. In nearly all cases, for raw material to become a product (or for a component to become part of a larger product), a procedure transforms the raw material or component, and a production worker monitors or takes an active part in the transforming process.

Defining the Relationships
The next step in building the metamodel is defining the entity relationships. The relationship between PRODUCT and RAW_MATERIAL is many-to-many (M:N). That is, one instance of raw material can make zero or many instances of product (raw material in stock that isn't yet in production creates the zero condition), and one instance of product is composed of one or more instances of raw material. EMPLOYEE and PROCEDURE also sustain a M:N relationship. An employee can perform zero or many procedures (you have to accommodate the intern or trainee, who initially might not be skilled at any procedures), and a procedure might need zero or more employees doing the work. (A procedure might require zero employees if a camera, computer, or robot automatically monitors it.)

So, how can you interrelate procedures, products, raw materials, and employees? To begin with, you can logically assume that employees who perform procedures turn those raw materials into products. Following this assumption, your first task is to resolve the M:N relationships between RAW_MATERIAL and PRODUCT and between EMPLOYEE and PROCEDURE. You want to pull apart each M:N relationship into its component pair of one-to-many (1:M) relationships. In some very high-level data models, you can leave the M:N relationships unresolved, but in data models such as this one, the M:N relationship is so complex that to understand it, you have to resolve it immediately.

Figure 2 shows the expanded relationships between RAW_MATERIAL and PRODUCT and between EMPLOYEE and PROCEDURE. Note that EMPLOYEE to PROCEDURE is a straightforward M:N relationship. A supervisor can assign an employee to work on zero or many procedures; similarly, a procedure might need zero or many employees assigned to it. The new associative entity WORK_ASSIGNMENT relates employees to procedures. In contrast, the original M:N relationship RAW_MATERIAL to PRODUCT is a little more complex. The new associative entity PRODUCTION_RUN, which is the manufacturing or assembly activity, links PRODUCT and RAW_MATERIAL. The link between product and production runs is clear: A product is the end result of one or many production runs, and each production run yields a product, as Figure 2 shows. However, a raw material can be part of zero or many production runs at the same time that a production run needs one or many raw materials.

Resolving a Complex Relationship
To explain the complexity of the relationship, you need to resolve the new M:N relationship between RAW_MATERIAL and PRODUCTION_RUN. Figure 3 shows the resolution and hints at the way RAW_MATERIAL:PRODUCT and EMPLOYEE:PROCEDURE interrelate. The new associative entity PRODUCTION_STEP is a detail entity that lets you break down the production run into its component steps. You decompose the production run the same way you break down a SQL Server task into its individual job steps (e.g., Enterprise Manager, SQL Server Agent, Jobs) or divide a SALE into SALE ITEMS (as "Metamodel for Retail Sales" demonstrates). The raw materials are associated with each step, and one or many steps comprise a production run.

   Prev. page   [1] 2     next page