Building a User Management Application - The database (
Page 2 of 4 )
I chose to use a database over a file-based storage system because of the security it offers. A file can basically be opened and read by anyone who manages to access your root. Information about a database is a bit harder to come by.
For our system I've created a database called "user" that contains a table called "users." This table has six fields, each of which takes a different kind of information about a user. Below is a list of fields that are available in the table:
-
uid - This field creates a unique user id for each new user.
-
uname - This field stores the user name.
-
upass - This field stores a forty-character-long password for the user.
-
Level - This field stores the user's access level.
-
Email - This field stores the user's email address.
-
Active - This field stores information about the state of the user's account activation.
Below is the SQL code for the database and table, as well as sample data. Simply copy and paste it into your MYSQL client:
# Database : `user`
#
# --------------------------------------------------------
#
# Table structure for table `users`
#
CREATE TABLE `users` (
`uid` int(4) NOT NULL auto_increment,
`uname` char(50) default NULL,
`upass` char(40) default NULL,
`level` char(8) NOT NULL,
`email` char(50) NOT NULL,
`active` char(32) default NULL,
PRIMARY KEY (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
#
# Dumping data for table `users`
#
INSERT INTO `users` VALUES (1, 'david', '9d4e1e23bd5b727046a9e3b4b7db57bd8d6ee684', 'admin', 'david@dweb.com', '1');
INSERT INTO `users` VALUES (2, 'joe', '1390470c09daf4c6179c197e6aebe9821c9ca92d', 'normal', 'joe@smith.com', '0');
INSERT INTO `users` VALUES (3, 'jack', 'monday', 'normal', 'jack@smith.com', '0');
To give extra protection to our user management system, I've included certain restrictions on the fields of the table. For example, the password can have up to forty characters, the username up to fifty, etc.
Also, to make the retrieval of data faster, I've optimized the fields by, for example, using the char type instead of the varchar type (the latter is slower):
`uname` char(50) default NULL,
`upass` char(40) default NULL,
`level` char(8) NOT NULL,
`email` char(50) NOT NULL,
`active` char(32) default NULL,
