BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Creating SQL Server Tables Part 2

The sixth part of the SQL Server Programming Fundamentals tutorial returns to the subject of creating SQL Server database tables. In this article we will use Transact-SQL commands to create the remainder of the tables for the JoBS tutorial database.

EngineerWorkingAreas Table

The EngineerWorkingAreas table will provide a many-to-many link between engineers and geographical areas. This information will determine which areas the engineer is willing to work in. This data is necessary when matching customer work to engineers.

Column NameData TypeNullable?
EngineerIdIntNo
AreaCodeChar(3)No

EngineerSkills Table

In a similar manner to the EngineerWorkingAreas table, this table will provide a link between the engineers and the skills they have. This will be used to ensure that the engineer sent to a job is equipped to undertake the work.

Column NameData TypeNullable?
EngineerIdIntNo
SkillCodeChar(3)No

Jobs Table

The Jobs table will hold information relating to all jobs that have been performed or that are booked as future work. This table is central to the JoBS application. Each job will be given a unique identifier. There will be links to the standard job type and the contract under which the work will take place. The contract number will be used to find the customer's address and other details.

Once a job has been booked with a customer, the engineer and the date of visit will be recorded. These columns are nullable because they may not be known when the job has been requested but not yet scheduled.

On completion of a job, the number of hours taken by the engineer will be recorded along with the charge rate for the time This information will be copied from the engineer's daily rate but will be stored in this table for historical purposes, as daily rates may change.

Sometimes a job will not be completed in a single visit. This may be due to a lack of parts or because the job takes longer than anticipated. In these cases the job will be marked as requiring a follow up. Once a follow up visit is booked as a new Job row in the table, a reference to the new JobId will be created so that the history of a job may be tracked.

Column NameData TypeNullable?
JobIdUniqueIdentifierNo
StandardJobIdIntNo
ContractNumberIntNo
EngineerIdIntYes
VisitDateDateTimeYes
HoursTakenRealYes
HourlyRateMoneyYes
FollowUpRequiredBitYes
FollowUpJobIdUniqueIdentifierYes
NotesVarChar(MAX)Yes

Parts Table

The Parts table will hold the list of standard parts that may be used by an engineer. Each part has a cost that will be used when calculating the total cost of a job and the profit made. Parts that are not included in the list are not reimbursed by DisasterFix to the engineer.

Column NameData TypeNullable?
PartNumberChar(8)No
PartNameVarChar(50)No
CostMoneyNo
20 July 2008