Creating the JoBS Database Tables
The JoBS database now includes three tables, permitting the storage of customers, geographical areas and complaints. To provide for all of the functionality required in the application we need to add a further twelve tables. These tables are described in the following sections. As a learning exercise, you may want to create these tables yourself using the SSMS graphical user interface or with T-SQL commands. Alternatively, you can download the script using the link at the top of this article and run it to create the database and its fifteen tables.
CustomerAddresses Table
The CustomerAddresses table holds details of addresses. By holding the addresses in a separate table to the customers we can have multiple addresses for a single customer without unnecessary repetition of data. This is known as a "one-to-many" relationship.
Each address includes a reference to the customer number so that the two tables can later be linked together. The address also includes a region code that will link to the GeographicalAreas table. Each address includes a reference number.
| Column Name | Data Type | Nullable? |
|---|
| AddressId | Int | No |
| CustomerNumber | Int | No |
| Address1 | VarChar(50) | No |
| Address2 | VarChar(50) | Yes |
| Address3 | VarChar(50) | Yes |
| TownOrCity | VarChar(30) | No |
| AreaCode | Char(3) | No |
| Postcode | Char(8) | No |
Contracts Table
The Contracts table holds information relating to single jobs and annual contracts. Each contract is linked to a customer address; if a customer wishes to obtain an insurance policy for multiple premises, they will actually create several contracts. Recurring contracts include a renewal date and a flag that indicates if the customer wishes to automatically renew or whether contact must be made beforehand. Single jobs will hold a null value in the renewal columns. Both single jobs and contracts include a monetary value.
NB: The CustomerAddressId column will be used to provide a link to the CustomerAddresses table. Note that the name of the two matching columns need not be identical but that the data type should be.
| Column Name | Data Type | Nullable? |
|---|
| ContractNumber | Int | No |
| CustomerAddressId | Int | No |
| RenewalDate | SmallDateTime | Yes |
| RenewAutomatically | Bit | Yes |
| ContractValue | Money | No |
Skills Table
The Skills table will be used to store the various skills that may be required of an engineer. A unique code and a name for each skill will be held.
| Column Name | Data Type | Nullable? |
|---|
| SkillCode | Char(3) | No |
| SkillName | VarChar(30) | No |
StandardJobs Table
The JoBS system will hold a list of standard jobs that may be undertaken by engineers. For simplicity, custom jobs are not permitted; if an unexpected job must be undertaken it will be added to this table first. A standard job includes a unique reference number, a name for the job and a standard price to charge.
| Column Name | Data Type | Nullable? |
|---|
| StandardJobId | Int | No |
| JobName | VarChar(100) | No |
| StandardPrice | Money | No |
RequiredSkills Table
Each standard job will require one or more skills to complete successfully. In order that the correct engineer is despatched to do the work, the RequiredSkills table holds links between the StandardJobs and Skills tables, determining which skills are required. In a later article, we will create links between the tables to cement this "many-to-many" relationship.
| Column Name | Data Type | Nullable? |
|---|
| StandardJobId | Int | No |
| SkillCode | Char(3) | No |
Engineers Table
We need a table to hold a list of people that may be employed to undertake work on behalf of DisasterFix. This information will be stored in the Engineers table. The table will hold an optional photograph to be used on engineer ID cards.
| Column Name | Data Type | Nullable? |
|---|
| EngineerId | Int | No |
| EngineerName | VarChar(50) | No |
| HourlyRate | Money | No |
| Photograph | VarBinary(MAX) | Yes |
NB: You would probably need to hold more engineer information than this in a real-world solution. In this case the table has been simplified for the purposes of the tutorial.
20 July 2008