This appendix shows how to structure the create scripts for building the IssueTracker database, which is the sample application used throughout the book.
The Dat_Company table maintains a list of available reports to produce (see Listing A-1).
Listing A-1: Dat_Company CREATE TABLE [dbo].[Dat_Company] ( [CompanyID] [int] NOT NULL , [UserID] [int] NULL , [CompanyName] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Address1] [char] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Address2] [char] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [City] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [State] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Zipcode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Country] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CreateDate] [datetime] NULL ) ON [PRIMARY] GO
The Dat_Issue table contains data records related to individual issues logged in the database (see Listing A-2). Each issue will have a unique identifier, some attribute data, and references to look up values in other tables.
Listing A-2: Dat_Issue CREATE TABLE [dbo].[Dat_Issue] ( [IssueID] [int] NOT NULL , [TypeID] [int] NULL , [UserID] [int] NULL , [EntryDate] [datetime] NULL , [StatusID] [int] NULL , [Summary] [char] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PriorityID] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
The Dat_User table contains data records related to the individual users who enter issues into the database (see Listing A-3).
Listing A-3: Dat_User CREATE TABLE [dbo].[Dat_User] ( [UserID] [int] NOT NULL , [Password] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Firstname] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Lastname] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EmailAddress] [char] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UserType] [int] NULL , [CreateDate] [datetime] NULL ) ON [PRIMARY] GO
The Val_IssueType table contains records that maintain a list of issue types (see Listing A-4). These types might include values such as Hardware, Software, or Networking. By storing lookup values within a database table rather than embedded within the application code, you can make product customizations for specific customers more quickly.
Listing A-4: Val_IssueType CREATE TABLE [dbo].[Val_IssueType] ( [TypeID] uniqueidentifier ROWGUIDCOL NOT NULL , [TypeLabel] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO
The Val_MailMessages table maintains a list of available reports to produce (see Listing A-5).
Listing A-5: Val_MailMessages CREATE TABLE [dbo].[Val_MailMessage] ( [MailMessageID] [int] NOT NULL , [Format] [int] NULL , [Priority] [int] NULL , [Subject] [char] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Body] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
The Val_Priority table maintains a list of issue priorities (see Listing A-6). These priorities might include values such as High, Medium, or Low.
Listing A-6: Val_Priority CREATE TABLE [dbo].[Val_Priority] ( [PriorityID] uniqueidentifier ROWGUIDCOL NOT NULL , [PriorityLabel] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO
The Val_Reports table maintains a list of available reports to produce (see Listing A-7).
Listing A-7: Val_Reports CREATE TABLE [dbo].[Val_Reports] ( [ReportID] [uniqueidentifier] NOT NULL , [ReportLabel] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ReportFilePath] [char] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO
The Val_Status table maintains a list of issue status conditions (see Listing A-8). These conditions might include values such as Open, Closed, or In Progress.
Listing A-8: Val_Status CREATE TABLE [dbo].[Val_Status] ( [StatusID] uniqueidentifier ROWGUIDCOL NOT NULL , [StatusLabel] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO
