Scripts to Create Database Tables

To create the application tables, we have two options: we can use Visual Studio to create one table at a time, or we can use a script that contains all the instructions to create all the tables in one go. Creating a script is the recommended best practice. That way, you have documented your work and if you discover an error or if you want to tweak the database, you can simply change the script and reapply it or better yet, create a new script with the modifications to document what has happened in the database.

Listing 1 includes all the necessary script commands to create the application tables (it is included in the book resources and is available to download so that you don’t have to type it).

Listing 1. T-SQL Script to create the application-specific tables in our application

CREATE TABLE [dbo].[Setting]
(
   [Id] INT NOT NULL IDENTITY(1,1),
   [Key] VARCHAR(50) NOT NULL,
   [Value] VARCHAR(500) NULL,
   CONSTRAINT [PK_Setting] PRIMARY KEY ([Id])
);

CREATE TABLE [dbo].[PetType]
(
   [PetTypeID] INT NOT NULL IDENTITY(1,1),
   [PetTypeDescription] VARCHAR(50) NULL,
   CONSTRAINT [PK_PetType] PRIMARY KEY ([PetTypeID])
);

CREATE TABLE [dbo].[Status]
(
   [StatusID] INT NOT NULL IDENTITY(1,1),
   [Description] VARCHAR (50) NOT NULL,
   CONSTRAINT [PK_Status] PRIMARY KEY ([StatusID])
);
CREATE TABLE [dbo].[Pet]
(
   [PetID] INT NOT NULL IDENTITY(1,1),
   [PetName] VARCHAR(100) NOT NULL,
   [PetAgeYears] INT NULL,
   [PetAgeMonths] INT NULL,
   [StatusID] INT NOT NULL,
   [LastSeenOn] DATE NULL,
   [LastSeenWhere] VARCHAR(500) NULL,
   [Notes] VARCHAR(1500) NULL,
   [UserId] INT NOT NULL,
   CONSTRAINT [PK_Pet] PRIMARY KEY ([PetID]),
   CONSTRAINT [FK_Pet_Status] FOREIGN KEY ([StatusID])
   REFERENCES [Status] ([StatusID]),
   CONSTRAINT [FK_Pet_User] FOREIGN KEY ([UserId])
   REFERENCES [UserProfile] ([UserId])
);

CREATE TABLE [dbo].[PetPhoto]
(
   [PhotoID] INT NOT NULL IDENTITY(1,1),
   [PetID] INT NOT NULL,
   [Photo] VARCHAR(500) NOT NULL
   CONSTRAINT [DF_PhotoFile] DEFAULT '/content/pets/no-image.png',
   [Notes] VARCHAR(500) NULL,
   CONSTRAINT [PK_PetPhoto] PRIMARY KEY ([PhotoID]),
   CONSTRAINT [FK_PetPhoto_Pet] FOREIGN KEY ([PetID])
   REFERENCES [Pet] ([PetID])
);

CREATE TABLE [dbo].[Message]
(
   [MessageID] INT NOT NULL,
   [UserId] INT NOT NULL,
   [MessageDate] DATETIME NOT NULL,
   [From] VARCHAR(150) NOT NULL,
   [Email] VARCHAR(150) NOT NULL,
   [Subject] VARCHAR(150) NULL,
   [Message] VARCHAR(1500) NOT NULL,
   CONSTRAINT [PK_Message] PRIMARY KEY ([MessageID]),
   CONSTRAINT [FK_Message_User] FOREIGN KEY ([UserId])
   REFERENCES [UserProfile] ([UserId])
);

Listing 2 includes a modification to the ASP.NET SimpleMembership UserProfile table that enables the application to store more details about the registered user.

Listing 2. T-SQL script to modify the UserProfile table with new columns

ALTER TABLE [dbo].[UserProfile]
ADD
   [FirstName] VARCHAR(150) NOT NULL,
   [LastName] VARCHAR(150) NOT NULL,
   [Email] VARCHAR(150) NOT NULL;


The scripts in listings 1 and 2 will be executed once the database is created.

The scripts in listings 1 and 2 are created using standard T-SQL commands. T-SQL is the language the database (Microsoft SQL Server) uses to execute commands. LocalDB is a reduced version of SQL Server, so it understands most T-SQL syntax. Also, the scripts can be used when deploying the application to a production environment, which will most likely use a different version of SQL Server.