Designing the Database

The database for this application consists of two tables—tblUsers and tblUserStocks. The tblUsers table contains the usernames, passwords, and ZIP codes of the registered users of the system. The tblUserStocks table contains one row for each stock that any user wants to track. Because the application will be getting stock data for each stock individually, this table structure works out nicely for quickly looping through the table data. In the sample code, the database is named WSCh15 and was built in SQL Server 2000. When you've created your database, you can use the script shown in Listing 15.1 to create the tables.

Listing 15.1. Tables.sql—Tables for the Web Portal

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblUserStocks]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUserStocks]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblUsers]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUsers]
GO

CREATE TABLE [dbo].[tblUserStocks] (
      [pkStockID] [varchar] (10) NOT NULL ,
      [fkUserID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblUsers] (
      [pkUserID] [int] IDENTITY (1, 1) NOT NULL ,
      [UserName] [varchar] (20) NOT NULL ,
      [Password] [varchar] (20) NOT NULL ,
      [ZipCode] [varchar] (5) NOT NULL
) ON [PRIMARY]
GO
					

Besides the tables, there are also several stored procedures that cut down on the amount of raw SQL code required in the application. The first, sp_GetUser, returns the information for the user specified by a username and password. If this stored procedure doesn't return any records, it means that the user and/or password are incorrect, and an appropriate message is displayed to the user.

The sp_GetUserByID routine is used to retrieve a user's profile information, based on the primary numeric key value. This is used on the portal viewer page, as well as the profile editor page, to retrieve the key information from the database about this particular user.

The sp_GetUserStocks routine brings back all the stock records for a particular user ID. The primary key of the tblUserStocks table is the ticker symbol. No primary key was defined on this table because we are not checking for duplicate stock symbols. In all actuality, a duplicate symbol won't hurt anything in the system, so it's not a concern at this point.

The script to generate the stored procedures is shown in Listing 15.2.

Listing 15.2. StoredProcs.sql— Generating the Stored Procedures for the Web Portal

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[sp_GetUser]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUser]
GO

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[sp_GetUserByID]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUserByID]
GO
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[sp_GetUserStocks]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUserStocks]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetUser
@UserName varchar(20),
@Password varchar(20)
AS
SELECT *
FROM tblUsers
WHERE Upper(UserName) = Upper(@UserName)
AND Upper(Password) = Upper(@Password)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetUserByID
@UserID int
AS
SELECT *
FROM tblUsers
WHERE pkUserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_GetUserStocks
@UserID int
AS
SELECT * FROM tblUserStocks
WHERE fkUserID = @UserID
ORDER BY pkStockID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

You may want to create a user ID and password that the Web application will use to access the application database. If you're in a testing mode, you can use the sa username with a blank password, but be sure that your SQL Server is set up to allow mixed (or SQL) authentication. The next step is to set up the application configuration file.