5.2 The Schedules ExampleSchedules 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. |