Metamodel for Product Assembly

Extend the retail-sale metamodel

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 theretail-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.

The hint in Figure 3 is the connection between production and work assignments. A production step needs people and processes to bring it to completion, so you can logically relate production step to employees and procedures. A production step needs zero to many employees to complete it, and an employee can have the skill to perform zero to many production steps. An M:N relationship exists between PRODUCTION_STEP and EMPLOYEE, and the entity WORK_ASSIGNMENT fulfills that association, as Figure 4 shows. The relationship between PRODUCTION_STEP and PROCEDURE isn't M:N—it's 1:M. A production step is part of one procedure, but a procedure can require zero to many production steps.

Refining the Business Model

One modification that you need to make in this product-assembly metamodel is to add a materials safety data sheet, which the entity SAFETY_SHEET represents in Figure 4. Various government agencies such as the Occupational Safety and Health Administration (OSHA), Food and Drug Administration (FDA), and Environmental Protection Agency (EPA) have mandated that in any product assembly or manufacturing facility that deals with hazardous or potentially hazardous materials, a materials safety data sheet for each RAW_MATERIAL must be on file and readily accessible. The government agencies distribute these safety sheets to companies upon request. The safety sheets contain the properties and health hazards associated with the raw materials, plus information about safe disposal methods. Typically, you don't implement a one-to-one (1:1) relationship in a database, but this example justifies inclusion. The safety sheet is typically stored as a scanned image data type, and the number of times that users need to refer to it is a small fraction of the total number of accesses to the data in RAW_MATERIAL. Therefore, I created a separate entity, SAFETY_SHEET, that's related to the RAW_MATERIAL entity as follows: Each raw material can have zero or one associated safety sheet, and each safety sheet refers to one raw material.

The last step in creating this metamodel is to flesh out the design by adding a few attributes, as Figure 5 shows. PRODUCT contains the same attributes as the product entity from the retail-sale metamodel—unique identifier, description, quantity on hand, reorder point (in this case, the point at which the company orders a new production run), and pricing. PROCEDURE has a unique identifier and a description. RAW_MATERIAL has a unique identifier, a name, a unit price, and a measure for tracking costs of production. SAFETY_SHEET has only a unique identifier and an image file. Both PRODUCTION_RUN and PRODUCTION_STEP have attributes that let you describe them, as well as attributes that let you track production dates and times. EMPLOYEE has attributes similar to those of the salesperson entity from the retail-sale metamodel—unique identifier, name, and Social Security number—plus job title, which helps management match a person with a production step. WORK_ASSIGNMENT has a unique identifier, a description, and a start and end date and time, which promote efficient worker scheduling.

Make It Your Own

This metamodel, which you can use as a basis for product-assembly or manufacturing data models, is complex enough to give you a good foundation yet general enough to allow you ultimate flexibility as you customize it to your own specific situation. When you customize, you first need to define your requirements, then figure out how they integrate into the metamodel. Add additional entities, attributes, and relationships, then test your extended model to make sure that it meets the requirements that you defined. Happy modeling!