A key component of conducting business is the act of providing various types of services to various parties. As previously noted, businesses generally provide two types of product offerings: They sell either goods or services. When businesses sell services, they have a responsibility to perform those services, and then they need to bill for them. This often involves the completion of some type of work effort. Businesses also perform work efforts within their internal organizations to accomplish tasks, such as completing a project, producing inventory for sale, or maintaining some corporate asset.
Some questions that enterprises need to answer in the course of doing business include the following:
- What type of work effort is required?
- What items need to be produced, or what services need to be delivered?
- Who will be involved and what are their roles?
- Where will the work take place?
- How long will it take?
- What is the current status of the effort?
- Are the appropriate resources (people, inventory, equipment) available? If not, when will they be?
- What standards and metrics are in place to measure work effort effectiveness?
- What results are work efforts producing and how efficient are they?
This chapter will illustrate the following models that help answer those questions:
- Work requirements
- Work requirement roles
- Work effort generation (alternate model also provided)
- Work effort associations
- Work effort party assignment
- Work effort time tracking
- Work effort rates
- Inventory item assignment
- Fixed asset assignment
- Party fixed asset assignment
- Work effort type standards
- Work effort results
Work Requirement and Work Efforts
A WORK REQUIREMENT and a WORK EFFORT are two different but related entities. A work requirement represents the need to perform some type of work. This could be a requirement stemming from a decision to manufacture inventory items, deliver services, conduct a project, or repair an asset of the enterprise such as a piece of equipment or a piece of software.
A work effort is the fulfillment of the work requirement. This includes setting up and planning for the actual work that will be performed, as well as recording the status and information related to the efforts and tasks that are taking place.
Where does a work requirement come from? A work requirement is created when there is a need to perform some type of work by the enterprise, either for the enterprise or for an external organization, most likely a customer. Examples of work requirements include the following:
- The need to manufacture a particular item because market research indicates an increased demand for that item beyond previous projections
- The need for a piece of equipment within the enterprise to be repaired
- The need for an internal project such as an analysis of existing operations, development of a plan, or creation of a new product or service
Figure 6.1 shows the key entities used to define a work requirement. First, the WORK REQUIREMENT is a subtype of the previously modeled REQUIREMENT entity. The REQUIREMENT TYPE defines the possible categories for requirements, which, of course, includes categorizations for WORK REQUIREMENTs. Possible values for REQUIREMENT TYPE include “project,” “maintenance,” and “production run.” The WORK REQUIREMENT may be for work to be delivered of either a DELIVERABLE, FIXED ASSET, or a PRODUCT.
Figure 6.1 Work requirement.
Several key pieces of information need to be kept in the WORK REQUIREMENT entity. The description attribute defines the need of the requirement. The requirement creation date is a date by which the work is required. The required by date within the REQUIREMENT entity specifies the date by which the requirement is needed. The estimated budget attribute identifies how much money is allocated for fulfilling this requirement. The attribute quantity determines the number of items needed in the requirement. The reason attribute explains why there is a need for the requirement.
Table 6.1 shows some data that could appear in the WORK REQUIREMENT entity.
Table 6.1 Work Requirement Data
In addition, other information is needed depending on the REQUIREMENT TYPE. If the type is “production run,” then information about what is to be produced and how many is of critical importance. This is why the model includes an optional attribute for quantity and a possible relationship to PRODUCT.
If the type is “maintenance” or “repair,” then there is a definite need to know what piece of equipment needs to be worked on. To accommodate this need, the model also includes an optional relationship to FIXED ASSET.
Finally, if the type is “internal project,” then WORK REQUIREMENT may be associated with a DELIVERABLE. This would include such things as a management report, analysis document, or the creation of a particular business method or tool. The DELIVERABLE TYPE entity would contain a list of the possible types of deliverables an enterprise may produce for itself, such as “management report,” “project plan,” “presentation,” or “market analysis.”
Note that there is an exclusive arc across DELIVERABLE, FIXED ASSET, and PRODUCT because a single WORK REQUIREMENT can be related to one PRODUCT or one DELIVERABLE or for work on one FIXED ASSET, but not all three. Table 6.2 shows examples of various types of work requirements.
Table 6.2 Work Requirement Types
The data shown indicates that work requirement #50985 is a “production run.” Because of this, the product to be produced, “engraved black pen with gold trim,” and the required quantity to produce, 2,000 of these items, are also included. Requirement #51245 and #51285 are also requirements to produce certain amounts of the same pen. Requirement #60102 is an “internal project” related to the deliverable “2001 Sales/Marketing Plan.” Work requirement #70485 is a “maintenance” task that requires a particular piece of equipment to be repaired, so the asset ID for this machine is also included. As discussed with previous examples, appropriate business rules need to be in place to ensure that work requirements of certain types are appropriately related to the entities describing what the work requirement produces.
This data model does not include relationships from WORK EFFORT to SHIPMENT to handle the work requirements or corresponding work efforts of managing the delivering of inventory items. Usually, the sales order and shipping entities covered in Chapters 4 and 5 provide enough information to help the enterprise manage the delivery of items. The enterprise generally doesn't need to track the work efforts involved in delivering a product because they are usually quite simple and consist of loading, shipping, and unloading the items. This model, however, can be easily modified to provide for tracking work requirements and work efforts associated with delivery of items by creating a relationship from WORK EFFORT to SHIPMENT ITEM (work efforts will be discussed later in this chapter).
Anticipated demand is a situation where WORK REQUIREMENTs may be needed, and it warrants special consideration. Not only do specific customer or internal requirements generate the need to schedule a production run, but so can anticipated or forecasted demand (as shown in Tables 6.1 and 6.2). Anticipated demand from a corporate forecaster can result in an internal work requirement to produce certain inventory items. For example, a forecast based on the trend analysis in a sales data warehouse could show that there should be a spike in future sales of a particular item. Rather than wait for the actual sales orders to start coming in, a work requirement to produce the expected increase is entered so that when orders do come in the enterprise will have an ample supply on hand to meet the demand.
Anticipated demand applies only to inventory items, not services, because it is not really possible to prefabricate a service. It is not possible to fix something or provide accounting, legal, or professional services to a client before a contract or order for this is in place. It is, however, possible to prepare for anticipated service orders by preparing standard work products to be used as templates, but those would be treated as internal projects. For instance, an enterprise may initiate a work requirement for an infrastructure project to prepare an outline project plan in anticipation of an accounting audit review or other consulting-type engagement.
Work Requirement Compared to Order
Is a work requirement a special type of ORDER because it describes a requirement to complete some type of work? As shown in Figure 4.9 in Chapter 4, each REQUIREMENT has a many-to-many relationship with each ORDER ITEM; however they are different entities because the REQUIREMENT represents the need and the ORDER represents a commitment to have the need fulfilled.
A REQUIREMENT also has major differences in structure and attributes from a standard order item. For instance, for internal work requirements, there is generally no need to track the terms of the work order, relate it to agreements, or include pricing structures. It is possible, though, that companies may need this information for internal monitoring of intercompany transactions because different parts of the company may perform these work effort functions.
Is a WORK REQUIREMENT then related to a subtype of ORDER ITEM, namely a WORK ORDER ITEM? This is a valid construct and one that is adopted within these models because order items represent commitments, including items for completing work. Both sales and purchase order items may be related to work orders, and therefore the data model would need another type of classification for PRODUCT ORDER ITEMs and WORK ORDER ITEMs. For example, a professional services firm may record several requirements for a customer that lead to an engagement (a type of order), which is then associated with several work efforts (projects). The section on work efforts will show how to model this.
Another question that arises is where a requisition falls into the model. Depending on the definition for requisition, it is either a synonym for a requirement or an order. If a requisition describes a request for getting something done or ordering something, then a requisition is really a requirement. If a requisition describes a commitment to having work done or having something ordered, then the requisition is synonymous with an order item.
Just as there are many roles that parties play in a purchase or sales order (refer to Chapter 4), there are many roles that organizations and people play in the work requirement. Among these roles, several could be important to an enterprise including: the internal organization for which the work requirement is created, the person requesting the work, people involved in the authorization of the work requirement, and the person responsible for ensuring the work requirement is completed. Figure 6.2 depicts the entities needed to maintain this information.
Figure 6.2 Work requirement roles.
The WORK REQUIREMENT ROLE TYPE entity is used to store all the valid roles defined by an enterprise that could be related to a WORK REQUIREMENT. The PARTY WORK REQUIREMENT ROLE entity contains the intersection of PARTY, WORK REQUIREMENT, and WORK REQUIREMENT ROLE TYPE. In this case, the primary key will be a combination of the work requirement ID, party ID, and role type id. This, however, is not enough because it is possible (though not likely) that one party may be assigned the same role at different times over the life of a work requirement. Because of this, the model includes a date (or alternatively one could add an additional sequence ID) as part of the primary key in order to ensure uniqueness. Table 6.3 contains sample data to demonstrate some of these possibilities.
Table 6.3 Party Work Requirement Role Data
As the example data shows for work requirement ID #50985, John Smith has two roles. He created the work requirement, and he is responsible for tracking the work requirement through its cycle from start through fulfillment. At one point he is replaced by Dick Jones, but later he is reassigned his original role. Notice, too, that Dick Jones has the role “responsible for” on two work requirements during overlapping time periods. As with the model for role assignments in orders, this model is flexible enough to allow for a variety of options.
Enterprises need to track requirements to perform work, commitments to do work, and the actual work that is being performed. Requirements have been addressed in the last section and it was established that the commitment to do work is a type of order item. The remainder of this chapter will focus on managing the work effort, which is the tracking of the work being performed.
Figure 6.3a shows a data model that tracks work from the REQUIREMENT to one or more committed WORK ORDER ITEMs to the fulfillment of one or more WORK EFFORTs. The WORK REQUIREMENT represents the need to do something, the WORK ORDER ITEM represents the commitment to complete some work, and the WORK EFFORT entity tracks the fulfillment and performance of work that results from a WORK ORDER ITEM. There is a many-to-many relationship between each of these, allowing requirements to be flexibly combined into commitments and commitments to be grouped into manageable work efforts. The ORDER REQUIREMENT COMMITMENT associative entity was explained in Chapter 4, and the WORK ORDER ITEM FULFILLMENT associative entity is introduced in this chapter.
Figure 6.3a Work effort generation.
The WORK ORDER ITEM will usually result from WORK REQUIREMENTs; however, it could result from certain PRODUCT REQUIREMENTs such as for products that need to be manufactured. The work effort may be fulfilling an internal commitment to do work, or it may be fulfilling an external requirement such as a SALES ORDER ITEM. The work effort may result from scenarios such as these:
- A work requirement (as defined in the previous section).
- A customer orders an item that needs to be manufactured.
- A service that was sold now needs to be performed.
- A customer places an order to repair or service an item that was previously sold to him or her.
In addition to defining the basic information for a WORK EFFORT, the model also keeps track of WORK EFFORT TYPE, WORK EFFORT PURPOSE TYPE, and the FACILITY where the work takes place.
Work Effort Type and Work Effort Purpose Type
In order to fulfill the requirement of either a work requirement or a work order item, the WORK EFFORT entity is used. The WORK EFFORT is subtyped according to its level of detail. Possible subtypes include PROGRAM, PROJECT, PHASE, ACTIVITY, and TASK. The WORK EFFORT TYPE entity is used to include more types if necessary. The standard work hours attribute in WORK EFFORT TYPE is used to record the estimate of how many hours it would normally take to complete this effort.
A WORK EFFORT tracks the work to fix or produce something for manufacturing and involves the allocation of resources: people (labor), parts (inventory), and fixed assets (equipment). Therefore each WORK EFFORT may be for the purpose of a WORK EFFORT PURPOSE TYPE. Subtypes for WORK EFFORT PURPOSE TYPE include MAINTENANCE, PRODUCTION RUN, WORK FLOW, and RESEARCH. The work effort may be a MAINTENANCE effort, such as performing preventive maintenance on various pieces of equipment. It could be a PRODUCTION RUN to fulfill an immediate or anticipated request. The quantity to produce maintains the expected quantity for the production run, and the quantity produced records the actual production from that production run. Another purpose for a work effort is a WORK FLOW, which indicates that there is a task or effort that was assigned in the process of doing business, perhaps by a work flow system. RESEARCH would indicate that the work effort is needed to track the researching of some type of question—for example, the reason why a customer had a certain credit status.
Other information that an enterprise may want to record about the WORK EFFORT could include a name for the overall effort (such as a project name) and a detailed description of the effort. To facilitate project tracking, the enterprise may also want to list a scheduled start date, scheduled completion date, and estimated hours for the effort. The actual start datetime, actual completion datetime, and actual hours are stored to track efficiency of efforts. If there is a need for any special terms that anyone needs to know about, those can be recorded as well.
Some institutions have other considerations that need to be considered. Funding or time limits may be imposed under certain circumstances by various agencies, so the model includes attributes to store total dollars allowed and total hours allowed. An example of this would be IRS regulations restricting the amount of time a contractor can work for an enterprise without being considered an employee. Also, many government-funded organizations have spending limits set by budget appropriations or even by law.
Fulfillment of Work Requirements
Each requirement to do work may be fulfilled by work efforts. Depending on the nature of the work requirement and how much data the enterprise has the will and means to capture, the work requirement may be either directly related to the work effort or related to an order item that, in turn, is related to a work effort.
For example, if there is a requirement from a customer to have certain work performed—for instance, the customer may have specified a need to build a specific application system—the enterprise would want to record the REQUIREMENT, relate it to the ORDER ITEM (which it obtained, we hope), and then track the corresponding work effort(s). The many-to-many relationships from REQUIREMENT to ORDER ITEM and then from ORDER ITEM to WORK EFFORT provide a very flexible structure to map requirements, to commitments (orders), and then to the corresponding projects (work efforts).
If there was an internal need to complete work—for instance, a repair to a piece of machinery—then the enterprise may want only to capture the WORK REQUIREMENT and corresponding WORK EFFORTs. The associative entity, WORK REQUIREMENT FULFILLMENT, between WORK REQUIRMENT and WORK EFFORT, allows work requirements to be combined into a single effort or a single requirement to be managed in several work efforts.
As indicated by the data in Table 6.4, a single work effort may have originated from one or more work requirements, or the corresponding work effort may be originated from an ORDER ITEM or a WORK REQUIREMENT. The ORDER ITEM will typically be from a sales order and not a purchase order because sales orders, especially for services, more typically create a need to track a work effort. The enterprise usually will not track work efforts for items that they have purchased, although it may happen if the work effort is large enough and it is important for the purchaser to track the progress of the delivery.
Table 6.4 Work Efforts from Work Requirement
The model shows a many-to-many relationship from REQUIREMENT to ORDER ITEM. There may be a sales order that has an item for 1,000 items. Management may decide to manage this as three separate work efforts and perhaps create three production runs in separate plants in order to generate the inventory needed to fulfill this one order. Likewise, an internal work requirement to revamp an enterprise's computer systems could be divided into multiple projects in order to phase the development effort.
Table 6.4 contains data samples for some scenarios of how a work effort originated. Several requirements may be combined into a single work effort, as shown in the first two rows of Table 6.4. Requirement items #50985 and #51245, which may have come from different managers, are combined into a single production run. Conversely, a single requirement, for example #51285, may be fulfilled with two work efforts in order to better manage each of these production runs. This scenario may occur with other types of requirements being fulfilled by a single work effort or multiple work efforts originating from a single requirement.
Thus, the associative entity WORK REQUIREMENT FULFILLMENT handles this many-to-many relationship. The last two rows show the requirements and the corresponding work effort for fixing an engraving machine and developing a sales and marketing plan.
Table 6.5 provides an example of the data when a REQUIREMENT is mapped to an ORDER ITEM and then fulfilled with a WORK EFFORT. The requirement was from a customer who had a need for customized pens. This need was converted into a sales order item to produce 2,500 customized pens. The order item was fulfilled using two work efforts, namely production run #1 and production run #2.
Table 6.5 Work Efforts Originated from Order Items
A final question to ask is: Where is the WORK EFFORT going to take place? As shown in Figure 6.3a, the WORK EFFORT may be performed at or associated with one and only one FACILITY.
As stated in the discussion of WORK EFFORT, some parts of the work effort may not happen at the main site. Take for example a project to develop a sales and marketing plan for the enterprise. The effort is associated with a main office where the headquarters are located. Various tasks associated with this effort, such as interviews, may happen at a branch office, while others, such as writing up the report, happen at the main office. For the tasks that do not occur in the main office, there may be a desire to record that secondary work location. The optional relationship to FACILITY from WORK EFFORT may occur at any level in the WORK EFFORT breakdown (because work efforts are defined recursively). For those efforts that have no facility association, it is assumed that they occur at the main facility associated with the parent WORK EFFORT (or that location is immaterial in this case).
The FACILITY is used because these work efforts may happen at an actual physical structure such as a particular building, room, or floor. The enterprise may need another relationship (which is not shown here), from WORK EFFORT to POSTAL ADDRESS, just in case the facility is not stored and there is simply an address that is known.
Work Effort Generation—Alternate Model
Some organizations do not have a need to maintain which requirements are fulfilled by which order items. They may, however, have internal requirements and associated work efforts that fulfill these work requirements. Figure 6.3b provides an alternate model for work effort generation to handle this type of situation.
Figure 6.3b Work effort generation—alternate model.
Work efforts may be defined at various levels of detail and broken down in different ways. Each subtype of WORK EFFORT may be related to different subtypes depending on the circumstances. For instance, to manage programs within an enterprise and associated internal projects PROGRAMs may be broken down into PROJECTs, which may be broken down into ACTIVITYs, which may be broken down into TASKs. For a production run, a JOB may be broken down into ACTIVITYs—for instance, as in a production run. Tasks are the activities or steps that need to occur to accomplish a work effort. For any WORK EFFORT there may be any number of WORK EFFORTs that may be broken down into any number of other WORK EFFORTs. In addition, for some tasks there may also be a WORK EFFORT DEPENDENCY, meaning that one effort may need to be performed before another task, which is maintained in the WORK EFFORT PRECEDENCY. Another type of dependency is that one effort may need to be done concurrently with the other effort that is maintained in the WORK EFFORT CONCURRENCY. The one-to-many recursion around WORK EFFORT provides for work efforts to be redone by other work efforts and to capture this relationship (see Figure 6.4).
Figure 6.4 Work effort breakdown.
Work Effort Association Definition
Table 6.6 shows the work efforts for a job of producing pencils on a particular production run. Included in the data are the attributes for scheduled start date and scheduled completion date and the estimated hours. These tasks will be useful for planning staff and equipment assignments (discussed in a later section). Included are scheduled work efforts and the sub-efforts that make up each work effort. Job #28045, which is a production run of pens, has four activities related to it. Notice that the first activity of setting up a production line is an activity not only of this production run but also of the next production run. Hence, each work effort may be made up of many other work efforts, and conversely each work effort may be used within one or more other work efforts. This many-to-many recursion is handled by the WORK EFFORT ASSOCIATION entity in the model, and Table 6.6 shows how work efforts are related to other work efforts. The WORK EFFORT TYPE carried out by the enterprise may track the standard work hours usually spent on this type of effort. Notice that the estimated hours of the WORK EFFORT may be different from the standard work hours because particular circumstances are involved in the execution of the task (perhaps the project manager knows that very efficient workers will be assigned and will therefore lower the estimate).
Table 6.6 Work Effort Breakdown Data
The WORK EFFORT DEPENDENCY entity allows the enterprise to track the fact that some efforts may not be a breakdown of other efforts but may actually be dependent on other efforts. For example, the task “operate machinery” cannot be executed until the task “set up production line” is completed. The WORK EFFORT DEPENDENCY entity provides a method for relating work efforts with other work efforts. Work efforts may be dependent on each other in different ways. In the example regarding “operate machinery” and “set up production line,” the second effort must be preceded by the completion of the first task and is a WORK EFFORT PRECEDENCY subtype. Other situations may occur where two tasks need to be executed in parallel. In that case, the WORK EFFORT CONCURRENCY subtype would be used.
An alternative to using the WORK EFFORT ASSOCIATION could be to show a WORK EFFORT with a one-to-many relationship to a separate entity, WORK TASK (with a recursion around WORK TASK) in order to distinguish the whole project from its individual phases, activities, tasks, and so on. The issue with this model is that there are usually many similarities between the whole work effort and its parts. For instance, parties, inventory, and fixed assets may be assigned to a whole project (a work effort) or an individual task. The benefit of having a WORK EFFORT that is recursively associated with other WORK EFFORTs, is that work efforts may be dynamically linked to other work efforts as needed.
In order for the WORK EFFORT to be completed, certain resources need to be made available. Parties, inventory, and equipment may need to be assigned to WORK EFFORTs at different levels of detail. For instance, people may need to be assigned roles at a high level, such as a program or project, and roles may also be assigned at more detailed levels of activity. This section discusses the assignment of parties to WORK EFFORTs. It includes the entities WORK EFFORT PARTY ASSIGNMENT, WORK EFFORT ROLE TYPE, PARTY SKILL, SKILL TYPE, WORK EFFORT STATUS, and WORK EFFORT STATUS TYPE (see Figure 6.5).
Figure 6.5 Work effort party assignment.
For planning and scheduling purposes, the model includes a means by which people, or groups of people, can be assigned or allocated to a WORK EFFORT. This is accomplished through the WORK EFFORT PARTY ASSIGNMENT entity (refer to Figure 6.5). With this it is possible to assign parties to work efforts in various roles as well as at various levels of the work effort. Table 6.7 gives examples of the data available when the relationships are resolved.
Table 6.7 Work Effort Party Assignment Data
Dick Jones is shown as being assigned as the project manager to the sales and marketing plan development work effort, with Bob Jenkins being the project administrator. John Smith is assigned to the project from March 5, 2001 to August 6, 2001 and then not assigned for a good part of August due to a vacation, then assigned again from September 1, 2001 through December 2, 2001. Dick, the project manager, can see that there will be a three-week period when John Smith is not assigned to this effort (between August 6 and September 1) and has assigned an employee, Jane Smith, with a lot of interest in this work effort, to be scheduled for this effort during this time period. With this information the manager will know not to assign John to any critical tasks that must be completed in August.
Additionally, the data indicates that for work effort #39409 there are additional work efforts within the overall work effort. One work effort is to develop a project plan that has two parties assigned to it. Dick Jones, the project manager, is responsible for developing the plan, and his assistant, Bob Jenkins, is responsible for entering it into the project management system. John Smith is assigned the effort of conducting initial interviews. An outside contracting firm is allocated to the project for four months; its responsibility is to do market research. Thus the data structure allows for efforts to be completely outsourced with no internal employee actually assigned to the work, only a particular organization. In this case, the internal manager is not concerned with who does the work, only that it be well managed and tracked with this outside firm. This illustrates the reason why parties are assigned to work efforts and not just persons.
When scheduling these assignments, managers need to know the qualifications of prospective parties. This can be handled using the PARTY SKILL and SKILL TYPE entities. PARTY SKILL contains a list of parties, skill types, years of experience, and a skill rating. Table 6.8 contains sample data.
Table 6.8 Party Skill Data
As indicated by the data, skills are associated not only with people but with companies as well (this is why the entity is PARTY SKILL, not PERSON SKILL). This information could be vital to project managers who are staffing new efforts. If no people within the enterprise are available for use, the manager can evaluate outside agencies for their ability to support the effort in question. Additionally, for people who have not already been allocated, this information can be used to determine appropriate work effort assignments. Years experience tells how many years that the person or organization has been involved in this skill. It may seem that this is derivable data from the start date that one started using a skill; however, skills are often used on and off within a time frame, and often the data in this attribute is subjective. The rating indicates how proficient the party is in the skill, and this may also be subjective.
Another required piece of information is the status of the work effort. It is through the association to WORK EFFORT STATUS that the status for WORK EFFORT is maintained. The status of the work effort may have an effect on the REQUIREMENT STATUS (see Figure 4.9); however, they may also be independent of each other. For instance, a status of “completed” on the work efforts required to implement a requirement may lead to the requirement having a status of “fulfilled.” They, however, may have different unrelated statuses, such as the requirement having a status of ”approved” and the work effort having a status of “in progress.”
Some example data for work effort statuses is provided in Table 6.9.
Table 6.9 Work Effort Statuses
Based on this data, the status for any level of the work effort may be available. A manager could see that the work effort is still in progress, that three of the four activities have been completed, and that the last activity of quality assuring the goods has not yet been completed. Because there are many statuses for any work effort, the data can also show when work efforts were started, completed, or underwent any other status at any point in time.
This entity allows a WORK EFFORT to be assigned to one or more PARTYs in order to allow different roles or just allow many people to be assigned in order to accomplish the effort faster. If desired, an enterprise could put in place business rules that require parties to be assigned at the top level of the effort—for example, the overall project—in order to be assigned to lower levels of responsibility. For this reason, a scheduled start date and scheduled completion date are included as attributes of the work effort in order to schedule parties or record what actually occurred in terms of their assignment. For instance, a person may be assigned to a work effort, and then the dates of the scheduled assignment may change until the person is actually working on the effort. The scheduled completion date is optional to handle such things as ongoing service contracts or people who are assigned indefinitely to an effort until they are notified otherwise. Because an enterprise may also wish to track such things as assignment extensions, the from date is also included in the primary key of WORK EFFORT PARTY ASSIGNMENT, allowing more than one assignment for the same PARTY and WORK EFFORT.
Because many parties could be assigned to a single effort, some other information that may be important to track would be the role a particular party plays in a work effort. This is tracked by selecting the appropriate WORK EFFORT ROLE from the entity WORK EFFORT ROLE TYPE and assigning this role to a particular PARTY. This information is especially useful when one party fills multiple roles, as is often the case, or for identifying who does what on a task that requires a team of people. Possible roles could be “supervisor,” “analyst,” “laborer,” “quality assurer,” or “contractor.”
Work Effort Assignment Facility
The relationship from WORK EFFORT PARTY ASSIGNMENT to FACILITY appears even though each work effort (and their sub-work efforts) may also be related to a facility. Why is that? This optional relationship is there to handle efforts that must be completed in what is now a very mobile society. With the increase in telecommuting, there is no longer any assurance, or requirement, that a particular work effort or part of a work effort must always be in a particular location. Take, for example, the task of building a complex data warehouse model for a large corporation. This task could involve many data modelers as well as business analysts. It is entirely conceivable that they could all work remotely from home offices, communicating with each other and the client via fax, e-mail, and voice mail. In this case, the facility that each individual is using is associated with the individuals assigned to the work effort.
Instead of a relationship to FACILITY, should the CONTACT MECHANISM for that party be used as the location for the WORK EFFORT PARTY ASSIGNMENT? It may very well be that only a postal address or e-mail address is known for that party. If the FACILITY of the assignment is sometimes not known, the CONTACT MECHANISM could be added as another relationship to the WORK EFFORT PARTY ASSIGNMENT.
For WORK EFFORTs that have no location recorded for them, it is assumed that they occur at the facility associated with the parent WORK EFFORT or that the location is immaterial.
Tracking time entries is critical to many organizations, and a model for this is shown in Figure 6.6. The TIME ENTRY entity is, of course, very important for payroll, but it is also important for task tracking, cost determination, and perhaps client billing. This entity quite simply holds information about how much time was spent during a given period on various WORK EFFORTs. Included in this information are the from datetime, thru datetime, and the hours worked in order to capture the time frame and amount of hours worked. Each TIME ENTRY may be part of a TIMESHEET that may record many time entries. Each TIME ENTRY may be for a particular WORKER, which is a PARTY ROLE, namely anyone who performs work, and may be an EMPLOYEE or a CONTRACTOR. Each TIMESHEET may have several other PARTYs in various TIME-SHEET ROLEs categorized by TIMESHEET ROLE TYPEs such as “approver,” “manager,” “enterer.”
Figure 6.6 Work effort time tracking.
The model shows that each WORKER may be submitting many TIME-SHEETs over time, which are each composed of TIME ENTRYs for a particular period of time. Each TIME ENTRY is within a TIMESHEET and also tied back to a WORK EFFORT, which determines the effort to which time is charged.
Why not relate the TIME ENTRY to a WORK EFFORT PARTY ASSIGNMENT, as the time entry must be attributed to a PARTY that is assigned to a WORK EFFORT? The reason that TIME ENTRYs are not directly related to WORK EFFORT PARTY ASSIGNMENTs is that this would result in redundantly storing the PARTY of the time entry. The PARTY of the time entry is already identified, as a TIME ENTRY is within a TIMESHEET for the hours of a WORKER, which must be for a PARTY.
If other units of measures are needed for time entries aside from hours, such as the number of days, then instead of an hours attribute, the attribute should be quantity with a relationship to UNIT OF MEASURE.
Table 6.10 contains sample time entry data. Dick Jones submitted a time-sheet covering the period of January 1, 2001 to January 15, 2001. He had two time entries to develop a plan and to identify potential team members. The first task was worked on from January 2 to January 4, and 13 hours were worked; the second was from January 5 to January 6, and 7 hours were used. John Smith had two time entries totaling 36 hours on one timesheet, consisting of two time entries for the February 1–February 16 period and another 45 hours for the next time period for one of the same tasks, “Conduct Initial Interviews.” Depending on how granular time entries are, there may be several time entries for the same work effort and even within the same time period. For instance, if time entries were recorded each day, there may have been several time entries for “Conduct Initial Interviews” for John Smith, one time entry for each day.
Table 6.10 Timesheet and Time Entry Data
In addition to tracking time, many applications need to track the rate and/or cost of the work effort. If the work effort is for professional services that will be billed, then it is very important to track the rate that will be charged for the effort. Alternatively, there may be a cost of doing work, for instance, to establish the cost of performing various business operations.
Figure 6.7 shows a model to capture the rates and/or costs associated with work efforts. Rates and costs may be based on three factors: the rate/cost established for a particular party, the rate/cost established for a certain type of position (the position of “assembly line worker” may have a standard rate), or the rate/cost established for the work effort assignment.
Figure 6.7 Work effort rates.
There may be more than one rate/cost for each of these factors because the rates/costs may change over time. Therefore, each WORK EFFORT PARTY ASSIGNMENT may have many WORK EFFORT ASSIGNMENT RATEs (over time). Also, because sometimes rates are established by the person or organization conducting the effort (John Smith's rate is $180 per hour), each PARTY (PERSON or ORGANIZATION) may have many PARTY RATEs over time. And because many times a position dictates the rate (a senior consultant's rate is $250 per hour) each POSITION may have several rates over time.
The RATE TYPE entity provides the ability to classify the various types of rates to allow the flexibility to capture different types of rates such as billing rates, payroll rates (amount that needs to be paid to the worker), costs, overtime rates, and so on.
Each RATE entity may store a rate, overtime rate, cost, or other type of rate depending on the needs of the organization. The RATE TYPE would indicate which rate is being specified. A RATE TYPE description of “billing rate” establishes how much is billed and whether it is to be billed to an external or internal organization. A RATE TYPE description of “cost” determines how much will be used as the cost basis to calculate how much the work is costing. Depending on the application, either or both of these are needed. For instance, a professional services application may store both because it needs to know its cost for the professional as well as the rate charges to the client. A manufacturing firm may need to store the cost of the people who are assembling a product, and it may or may not need the rate attribute. For instance it may want to track rates to establish how much to charge another part of the organization (this would be an internal transaction for accounting purposes).
The entity WORK EFFORT ASSIGNMENT RATE is used to record the rates and/or costs charged to a particular party working on a work effort (WORK EFFORT PARTY ASSIGNMENT). The rate or cost may come from the rate assigned to a party or a position, or it may be maintained for the specific work effort, depending on the enterprise's business rules. The entity has a from date and thru date to allow for the recording of multiple rates for an assignment over time. The relationship to RATE TYPE provides the ability to record various kinds of rates against an assignment such as for billing rates, costs, regular time, overtime, second shift, third shift, and so on.
Table 6.11 shows examples of rate data.
Table 6.11 Work Effort Rate Data
As the example shows, there are four different rate types recorded for Mr. Smith: “regular billing” rate, an “overtime billing” rate, a “regular pay” rate, and an “overtime pay” rate. The “regular billing” and “overtime billing” rate types determine how much to charge clients for Gary Smith's time. The “regular pay” and “overtime pay” determine how much to pay Gary Smith.
These rates may have been determined for that work effort, they may have been derived from Gary Smith's normal rate,they may have been derived from Gary Smith's position in the organization, or they may have been derived from a combination of these factors. Using the information for the billing rates, it would be easy to calculate how much to charge the client for Gary Smith's time, or pay Gary Smith, by multiplying his hours from the TIME ENTRY times the appropriate rate type.
Note that business rules need to be in place to ensure that the rates used are for the same time period as the hours being charged for. There are also business rules needed to determine what rate should be used if more than one rate applies—for example, if Gary Smith has rates for his position, for him and for his work effort party assignment. Also there is a need to put business rules in place to determine what constitutes overtime work.
Notice also in the table that Mr. Smith has an increase in pay rate recorded to be effective on May 15, 2001. There is no thru date. This indicates that the rate will be good until the end of the assignment. Also, note that the “overtime pay” is the same as the “regular pay” after the increase occurs. This would indicate that the enterprise may have automated rules on when to pay overtime, but in this case Mr. Smith no longer gets a higher rate. By recording the second rate, the payroll process that calculates his check does not need to be changed; it will simply calculate his overtime pay as always, but it effectively is at the same rate.
In order to complete certain work efforts, raw materials or other items may be required. Figure 6.8 shows the intersection entity WORK EFFORT INVENTORY ASSIGNMENT between WORK EFFORT and INVENTORY ITEM. This tracks the actual use of inventory during the execution of a work effort. Table 6.12 shows the data that might be associated with assembling pencil components, which is a task (a type of work effort) within a larger work effort to produce 100 pencils.
Figure 6.8 Inventory assignments.
Table 6.12 Inventory Assignment
| WORK EFFORT | INVENTORY ITEM | QUANTITY |
| Assemble pencil components | Pencil cartridges | 100 |
| Assemble pencil components | Erasers | 100 |
| Assemble pencil components | Labels | 100 |
As indicated by the data shown, this one task (assemble pencil components) used three different items from inventory to be completed. For each item used, a quantity of 100 was used. When this work effort uses inventory, business processes need to be in place to ensure that the inventory information is updated to reflect the depletion from inventory.
As with inventory, some work efforts will require various pieces of equipment, machinery, vehicles, or property in order to be completed. These are called FIXED ASSETS in this model (see Figure 6.9). This entity shows several subtypes of interest: PROPERTY, VEHICLE, EQUIPMENT, and OTHER FIXED ASSETs. In addition, other asset types may be identified within the entity FIXED ASSET TYPE. In order to track when and for what an asset is being used, the model includes the entity WORK EFFORT FIXED ASSET ASSIGNMENT. To further establish the state of the assignment, a reference to WORK EFF ASSET ASSIGNSTATUS TYPE is used.
Figure 6.9 Fixed asset assignments.
Other information stored in FIXED ASSET that may be of interest to an enterprise includes the asset ID, a name for identification, date acquired, which was established when the asset was acquired (important for depreciation, which is discussed in Chapter 8, Figure 8.5), date last serviced, which may not be needed if this can be derived from maintenance records (which are work efforts), and date next service, which is when the next service is scheduled. The production capacity maintains a value for the asset's production capabilities and a relationship to UNIT OF MEASURE, that allows this capacity to be maintained in various measurements such as the number of units that can be produced per day. Examples of data that could be found in FIXED ASSET are shown in Table 6.13.
Table 6.13 Fixed Asset Data
Of course, many kinds of assets may be important to an enterprise for various reasons. These can be listed using the FIXED ASSET TYPE entity. Notice that there is a recursive relationship on this entity. This relationship allows for the detailed breakdown of the various asset types. As an example, take the fixed asset type of “equipment.” The information that a given asset is a piece of equipment is probably not enough in some cases. It would be nice to know what kind of equipment it is, should it be needed for a work effort. In the same manner, it might be good to know that a “vehicle” is a “minivan” and that the “minivan” is actually a “Ford Aerostar.” Table 6.14 shows sample data for this entity.
Table 6.14 Fixed Asset Type Data
| FIXED ASSET TYPE ID | DESCRIPTION | PARENT ASSET TYPE |
| 1000 | Equipment | |
| 1390 | Pencil-making machine | Equipment |
| 1458 | Pen-making machine | Equipment |
| 1532 | Paper-making machine | Equipment |
| 2000 | Vehicle | |
| 2019 | Truck | Vehicle |
| 2188 | Mac truck-18 wheels | Truck |
| 2266 | Fork lift | Vehicle |
| 2356 | Jet airplane | Vehicle |
| 2567 | Car | Vehicle |
In the examples, there are three asset types (“pencil making machine,” “pen making machine,” “paper making machine”) that roll up to a type of “equipment.” In addition, there are several types of vehicles, “truck,” “fork lift,” “jet,” and “car,” that roll up to “vehicle.” Then there is the type “Mac Truck-18 wheels,” which is a type of “truck,” which is a type of “vehicle.” All of these are asset types. This model is flexible enough to allow as much detail as needed.
Fixed Asset Assignment and Status
Because a machine or piece of equipment can usually be used only for one effort at a time, it is necessary to tell what is being used and when it is in use. Figure 6.9 shows the model for tracking this information. In it there is the WORK EFFORT FIXED ASSET ASSIGNMENT entity, which is at the intersection of WORK EFFORT and FIXED ASSET. In this entity are attributes for storing the start and end date for the assignment, which are the from date and thru dates. These will be very important for task scheduling purposes. In addition, there is a relationship to WORK EFF ASSET ASSIGN STATUS TYPE. The status type will indicate such things as whether the assignment is “requested” or “assigned.” The allocated cost attribute provides a record of how much cost was recorded to that work effort for the usage of that fixed asset. This information would be important to capture the costs incurred during any work efforts for usage of fixed assets.
Table 6.15 gives some examples.
Table 6.15 Fixed Asset Assignment Data
Note that the from and thru dates for the assignment may not be the same as the scheduled start date and scheduled completion date on the WORK EFFORT. A certain machine may be needed only for part of the work effort's life cycle. Business rules (or database constraints) need to be in place, however, to ensure that the equipment assignment is at least within the scheduled date range for the associated effort.
Besides being assigned to work efforts, assets can be assigned or checked out to a PARTY. During this time, it is not uncommon to hold the party responsible for the safekeeping or use of the asset in question. This association is depicted in Figure 6.9. Again, an intersection called PARTY FIXED ASSET ASSIGNMENT joins information from PARTY and FIXED ASSET (see Figure 6.10). Also related to this entity is PARTY ASSET ASSIGN STATUS TYPE, which provides additional information about the assignment. Sample data is provided in Table 6.16.
Figure 6.10 Party fixed asset assignments.
Table 6.16 Party Fixed Asset Assignment Data
Assignment data like this provides the enterprise with vital information for tracking the condition and whereabouts of its assets. In this case, the enterprise knows that John Smith has the truck known as “Truck #25” until January 1, 2001, so if they need to give someone else a car, they know that this car is not available. The data also shows that Dick Jones was assigned a particular tool set and that he lost it.
In order to facilitate planning, this model includes additional entities to record various standards regarding different types of work efforts for the purposes of planning (see Figure 6.11). This data model provides information about what types of skills, what goods, and what types of fixed assets are needed for various types of work efforts. These metrics can be used to predict the resources needed for the scheduled work efforts, so that the organization can properly prepare.
Figure 6.11 Work effort type standards.
Figure 6.11 shows the standards that apply to each WORK EFFORT TYPE. Each WORK EFFORT TYPE may have standards regarding the types of skills that are generally needed to accomplish the effort (WORK EFFORT SKILL STANDARD), the type of goods or parts that are needed within the work effort (WORK EFFORT GOOD STANDARD), and the types of fixed assets needed to accomplish the work effort (WORK EFFORT FIXED ASSET STANDARD). These standards would need to be determined by the enterprise and entered using these structures. This information can then be used for scheduling resources and estimating time and costs for each work effort. Additionally it can be used to compare actual skills, goods, and fixed asset usage against the standards.
Each of these standards entities has attributes that define expected needs for that type of work effort. The WORK EFFORT SKILL STANDARD maintains the estimated num people, which records how many people of that skill type are typically needed for that type of work effort. The estimated duration records how long the skill is needed for the work effort type. The estimated cost is an estimation of how much this type of skill will cost for the associated work effort type.
The WORK EFFORT GOOD STANDARD maintains an estimated quantity attribute, maintaining how many of each GOOD is typically needed, and an estimated cost attribute, maintaining the estimated cost that is expected for that type of work effort.
The WORK EFFORT FIXED ASSET STANDARD maintains an estimated quantity attribute to determine how many of the fixed assets are needed for the work effort type. The estimated duration maintains how long that fixed asset is typically needed for the work effort type. The estimated cost maintains the anticipated cost for that type of work effort.
Similarly to WORK EFFORTs, each WORK EFFORT TYPE has associated WORK EFFORT TYPEs that represent the standard WORK EFFORT TYPE BREAKDOWNs and WORK EFFORT TYPE DEPENDENCYs. These represent the standard types of efforts that normally make up a larger type of work effort as well as the standard dependencies that occur.
Figure 6.10 also shows that each WORK EFFORT TYPE may be characterized by the fact that it is used to repair a FIXED ASSET TYPE or to produce a certain DELIVERABLE or a certain PRODUCT.
The WORK EFFORT SKILL STANDARD entity provides the enterprise with information on how many people are needed with what skills, for how long, and what are the estimated costs. For a “large production run of pencils” there may be a skill of “foreman” for three hours with a cost of $100 as well as a skill of “assembly line worker” for three people for three hours each with a cost of $200.
The WORK EFFORT GOOD STANDARD entity relates WORK EFFORT TYPEs to the GOODs that are typically needed for that work effort. If the PARTs model is used (Figure 2.10b), the entity PART would be substituted for GOOD. The entity includes an attribute for the estimated quantity required and an attribute for estimated cost. This information can be used for ensuring that enough of those goods exist in inventory to execute a particular work effort. Table 6.17 contains sample data for this entity.
Table 6.17 Work Effort Good Standard Data
Note that the relationship is to GOOD, not INVENTORY ITEM. Because this information is for planning, it is necessary to know only the type of GOOD needed. There is no need to locate an actual inventory item in stock. If, in examining this information, it is determined that for a planned WORK EFFORT, the WORK EFFORT TYPE will require more of a GOOD than is currently in inventory, then the enterprise will know that it needs to reorder that good in order to complete the planned effort.
Another point to note is that not all WORK EFFORT TYPES will have inventory requirements. For example, a work effort type of “prepare project plan,” which is a work effort associated with providing service as opposed to creating products, would not have any inventory requirements.
An alternative to this model for manufacturing firms and other firms that deal heavily with bill of materials, is to change the relationship to PART instead of GOOD and distinguish PARTs from GOODs. This is an option in many parts of the data models, and it is discussed in more detail in Volume 2, Chapter 2.
Work Effort Fixed Asset Standard
In a similar manner, WORK EFFORT FIXED ASSET REQUIREMENT will provide information for the scheduling and assignment of types of fixed assets. Other information carried by this entity includes the estimated quantity of the type of fixed asset needed, the estimated duration amount of time the fixed assets will be used in the execution of this WORK EFFORT TYPE and the estimated cost. Examples of this data are given in Table 6.18.
Table 6.18 Work Effort Fixed Asset Requirement Data
Finally, it is important to document the results of the work effort. The data model in Figure 6.12 shows the entities and relationships needed to maintain this information. While the WORK EFFORT TYPE maintained what the WORK EFFORT standards showing what should have been produced, it is important to capture what was actually produced as a result of the WORK EFFORT.
Figure 6.12Work effort results.
The model in Figure 6.12 shows that each WORK EFFORT may result in the production of one or more DELIVERABLEs via the WORK EFFORT DELIVERABLE PRODUCED; it may result in the repair of a FIXED ASSET; or it may result in production of one or more INVENTORY ITEMs via the WORK EFFORT INVENTORY PRODUCED entity.
Keep in mind that the relationships to these entities should be maintained at lower levels of the WORK EFFORT instances. This allows the deliverables, inventory items produced, and fixed assets repaired to be rolled up to see the total results for an overall work effort such as an entire project.
The details of the data model in this chapter involve the requirement for work efforts, creation of work efforts, scheduling of resources, and completion of work efforts (see Figure 6.13). Included are the complete breakdown of work efforts to other work efforts and the tracking of assignments of people, fixed assets, and inventory necessary for the completion of the tasks. Incorporated into the model as well are the entities needed to record the standards associated with type of work efforts as well as the actual results from work efforts.
Figure 6.13 Overall work effort model.
With this data model, enterprises should be able to effectively maintain lists of ongoing efforts and the various resources that are available or already assigned to the completion of these efforts. The availability of this information could be critical to the continued success and development of the enterprise; it is therefore of importance and needs to be included in the overall corporate model.
Please refer to Appendix A for a listing of entities and attributes. SQL scripts to build tables and columns derived from the logical models in this book can be found on the full-blown CD-ROM, which is licensed separately.