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 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.
Figure 4 shows the last criterion—a place where the work gets done—as Workplace and its associated entities. Workplaces have an associated code value (WorkplaceCode), such as classroom BMC201, warehouse W10, or store K33. For clarity, you can record a long description of these workplace locations (WorkplaceLocation) and the size of the usable space (WorkplaceSize). You can also record each Workplace's special features (WorkplaceSpecialFeatures), such as whiteboards for classroom BMC201, three loading docks for warehouse W10, and in-store banking services for store K33.
If you schedule workplace use, you need to include the entity WorkplaceAvailability, which tells you available days and times for each workplace. WorkplaceAvailability is a detail entity; the association between Workplace and WorkplaceAvailability is 1:M. A workplace such as store K33 or warehouse W10 might be available all day, every day. If the workplace is a shared facility like classroom BMC201, it might be available only on certain days and at certain times. You can plan workplace-dependent WorkInstances by programmatically checking WorkplaceAvailability's available days and times and comparing that schedule with the EstimatedTime that you need to accomplish a WorkInstance. After you find a workplace that's available for the length of time you need it and at an appropriate location, you can reserve the workplace by creating an entry in Workplace_WorkInstance. The attributes ReserveDateTimeStart and ReserveDateTimeEnd show that you can reserve by date, by time, or both, depending on your needs. WorkInstance to Workplace is a M:N relationship because one Workplace might be able to host zero or more WorkInstances, whereas a WorkInstance needs at least one—and maybe more than one—Workplace in which the employees get the work done.
Figure 4, despite its complexity, still shows the four basic components that you need for any successful scheduling system: work to be done, someone to do the work, a time frame in which to do the work, and a place in which to do the work. The attribute sets for each entity in the metamodel can vary widely, depending on the details of each situation. However, the concept and the entities that represent the concept remain the same in most cases. The next time you have to struggle with scheduling work, use this metamodel to make your job a little easier.
