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.

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 NameData TypeNullable?

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 NameData TypeNullable?

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 NameData TypeNullable?

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 NameData TypeNullable?

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 NameData TypeNullable?

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 NameData TypeNullable?

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