Inside Technique : Adding Site Registration : Defining the User Table

We store all user information in a single table, t_user. This table stores the user name, password, e-mail address, and other user-specific information. We set-up our membership table as follows:

Column Name	Data Type (length)	Special Notes
u_id	Integer	Auto-incremented Primary Key
u_name	String (50)	Unique Index
u_password	String (12)
u_firstname	String (50)
u_lastname	String (50)
u_email	String (50)	Indexed (non-unique)
u_logcount	Integer	The number of log-ons
u_createDate	DateTime	Defaults to user creation date
u_lastDate	DateTime	The date and time of the last log-on

For the SQL guru's, below is the SQL queries that generates the user table.

Create Table dbo.t_user
 (
 u_id int Not Null Identity (1, 1),
 u_name varchar(50) Not Null,
 u_password varchar(12) Not Null,
 u_firstname varchar(50) Not Null,
 u_lastname varchar(50) Not Null,
 u_email varchar(50) Not Null,
 u_logcount int Not Null Constraint DF_t_user_u_logcount Default (1),
 u_createDate datetime Not Null Constraint DF_t_user_u_createDate Default (getdate()),
 u_lastDate datetime Not Null Constraint DF_t_user_u_lastDate Default (getdate())
 )
Go
Alter Table dbo.t_user Add Constraint
 PK_t_user_name Primary Key Nonclustered
 (
 u_id
 )
Go
Alter Table dbo.t_user Add Constraint
 IX_t_user_name Unique Nonclustered
 (
 u_name
 )
Go
Create Nonclustered Index IX_t_user_email On dbo.t_user
 (
 u_email
 )
Go

Depending upon your needs, this table can also be extended with additional information. For example, you can have fields for the user's address, phone number, etc.

Now that this table is created, the next step is to write the necessary scripts that interact with the user table. Our approach to building a web-based database application is to encapsulate all the database manipulations and queries into stored procedures and use ASP scripts to enumerate and process query results. This separation allows us to build and test our database interactions separate from the testing the web-site. Next we explain the stored procedures used to add, edit, log-in, and find users.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.