5.2 The Schedules Example

Schedules are one of the most used forms of temporal data. For our example used for the recipes in this chapter, we are going to create a database for a service company to use in coordinating contractors and maintaining their schedules. The core table in that database is as follows:

CREATE TABLE ContractorsSchedules(
   JobID CHAR(10),
   ContractorID CHAR(10),
   JobStart DATETIME,
   JobEnd DATETIME,
   JobType CHAR(1) CHECK(JobType in ('B','H')),
   PRIMARY KEY(ContractorId, JobStart)
)

Each contractor in our example database is identified by a ContractorID value. In a real-world application, this would likely be a numeric ID code, but, in our example, we'll simply use the contractor's name as the ID. The JobStart and JobEnd attributes define a period in the contractor's calendar during which he is unavailable. A contractor is unavailable if he is booked (JobType 'B') or if he is on holiday (JobType 'H').

The following data, which happens to be for the contractor named Alex, is an example of a contractor schedule:

JobId      ContractorId JobStart     JobEnd       JobType 
---------- ------------ ------------ ------------ ------- 
           Alex         Jan  1 2001  Jan 10 2001  H
RF10001    Alex         Jan 11 2001  Jan 20 2001  B
RF10002    Alex         Jan 21 2001  Jan 30 2001  B
RF10020    Alex         Feb  1 2001  Feb  5 2001  B
RF10034    Alex         Feb 11 2001  Feb 20 2001  B

Please note that there is no JobId for holidays. This violates the rule of database design of giving each record a unique identifier known as a primary key, but it gives us a nice playing field on which to demonstrate some tricks to use when you don't have a unique identifier for your data. Normally, you would have JobId as a unique identifier and simply assign unique JobId values for holidays.