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!
End of Article
Prev. page
1
[2]
next page -->
