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.

UsedParts Table

In order for DisasterFix to determine their costs, the parts used during each job must be recorded. As multiple parts may be used for a single job, the UsedParts table will be used in a one-to-many relationship. Each row in this table links a part number to a job and records the unit cost of the part number and the number of units used in each case. The unit cost is recorded at the time of the job so that the historical data is not lost if the current cost changes. The number of units used is held as a fixed-point number to allow the engineer to specify that they used a fraction of a unit, for example 1.2 metres of copper piping.

Column NameData TypeNullable?
JobIdUniqueIdentifierNo
PartNumberChar(8)No
UnitsUsedDecimal(6,2)No
CostPerUnitMoneyNo

EngineerStock Table

Each engineer is expected to hold a stock of parts that they will use during their day-to-day work. The current stock level for each part held will be stored in the final table so that replenishment can be made as required. This will also be used in a report that details the value of stock that an engineer has in their possession.

Column NameData TypeNullable?
EngineerIdIntNo
PartNumberChar(8)No
UnitsHeldDecimal(6,2)No
20 July 2008