Track your company's workers and their work
Scheduling happens everywhere—at your workplace and at your home. From timing a product release to getting yourself up in the morning and arriving at the office before the boss, you live with schedules. Occasionally, you need to design software to help you keep your work activities on track, so let's look at how to create a metamodel for a complex scheduling system, the fifth metamodel in my series. To begin, let's revisit the metamodel that I described in "Metamodel for Reservations System," October 2001. Figure 1, an entity relationship diagram (ERD) that repeats the scheduling portion of that metamodel, shows the entities that represent the four criteria for creating a schedule. Those criteria and their representative entities (in parentheses) are
- work to be done (EmpSchedule)
- someone to do the work (Employee)
- a time frame in which to do the work (Shift)
- a place where the work gets done (Station)
The scheduling part of this metamodel for the reservations system is simple—too simple for most scheduling applications. Therefore, let's enhance it and make it more generically adaptable. First, you need to represent a list of work that needs to be done and specify whether the work will be repetitive or one-time-only. Then, you need to relate the work to the person or people who will do the work.
Figure 2, page 70, also an ERD, represents a schedule for repetitive work, which can be either work that needs to be done repeatedly or work that needs to be done at multiple workplaces. The entity Work is a simple list that describes each unique type of work. The related entity, WorkInstance, is a list of the work that has been done or that will be done. Because each WorkInstance inherits meaning from Work, WorkInstance and Work sustain a many-to-one (M:1) relationship. For example, Work might be a list of courses that a school offers. WorkInstance might be a list of classes for each course (e.g., sections 101 and 102 of the course College Algebra), or it might be the College Algebra class taught in the fall term and again in the spring term. A course has multiple occurrences of class, just as Work has multiple occurrences of WorkInstance.
When one or more people (which the Employee entity represents) are assigned to a WorkInstance, Emp_WorkInstance represents the planned or completed work. A simpler model that relates Employee directly to WorkInstance in a one-to-many (1:M) relationship can't represent two people assigned to the same job. For example, if Work represents a moving company's jobs, WorkInstance represents the company's completed and pending jobs. If you were in charge of scheduling these jobs and your company had to prepack a small apartment the day before loading the truck, you might assign just one packer to the job. For this simple assignment, the arrangement Employee—Emp_WorkInstance—WorkInstance might look like overkill. However, the job for the next day—loading the truck—requires two or three people, each of whom is assigned to the same WorkInstance. Similarly, driving the truck to the destination might require only one person, but unloading the truck at the destination might require two or three people. So although you might assign only one person to some jobs, other jobs need two or more people to do the work, and your scheduling model needs the extra entity to accommodate them.
Creating meaningful attributes for a metamodel as generalized as this scheduling metamodel is challenging. Each case (e.g., class scheduling, mover scheduling) has different requirements for stored data. I minimized the attributes in this article's figures so that the metamodel is clear and easy to develop. Note, though, that I've included the attributes StartDateTime and EndDateTime in the entity WorkInstance and EstimatedTime in the entity Work. These attributes let you estimate the amount of time that each type of work might require, then record the actual time taken for each instance of completed work.
Now that you've examined the first two scheduling criteria, let's look at the third criterion, a time frame in which to do the work. Figure 3 shows the time element, which is a tricky dimension to map because you can store an event as a point in time or a range of time. Typically, if an event happens at a point in time, you store time as an attribute. For example, when a checker scans purchases at the grocery store, the transaction is a point-in-time event, and the checkout application records the date and time as data in the database. However, you store a range-of-time event as an entity because each range has a beginning and an end as well as additional attributes that define this range of time. Examples for a range of time include an academic semester, which has a beginning date and an ending date as well as additional attributes such as graduation date and last date for dropping classes. Another range-of-time example is a production shift, which has a beginning time and an end time (e.g., 7:00 a.m. to 3:00 p.m., 3:00 p.m. to 11:00 p.m., and 11:00 p.m. to 7:00 a.m.), a shift name, and a shift pay differential.
Each work instance needs to have a time reference, so Figure 3 shows a many-to-many (M:N) association between the WorkInstance entity and Time. Each time "slice" (which you define in the Time entity) can be related to zero or more WorkInstances, whereas each WorkInstance might take one or more slices of time. How you slice or allocate time is based on how you need to partition the time. A school might break time into semesters; a moving company might break time into days or even hours.