We will need to set up a sample "userdemo" database. The database will have a single table named user
for
storing data associated with users of our website (see figure 16.2 below).
The user
table contains the following fields:
id
is an auto-incremented integer field (primary key).email
is a string field containing user's E-mail address. Each user will have a unique E-mail address, so this field is a unique key, too.full_name
string field will contain the full name of a user (like "John Doe").status
integer field will contain user's status (either "active" or "retired"). Retired users are not allowed to log in.date_created
contains date and time when the user was created.pwd_reset_token
and pwd_reset_token_creation_date
fields are used for password resetting (when the user forgets his/her password and needs to reset it). In your own website, you will likely want to add more fields to the
user
table. In this sample, we only define some minimum set of fields.
You can create the user
table with the following SQL statement:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(128) NOT NULL,
`full_name` varchar(512) NOT NULL,
`password` varchar(256) NOT NULL,
`status` int(11) NOT NULL,
`date_created` datetime NOT NULL,
`pwd_reset_token` varchar(32) DEFAULT NULL,
`pwd_reset_token_creation_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email_idx` (`email`)
);
You can find a migration, which creates the user
table, in the User Demo sample application.
If you are new to migrations, refer to chapter Database Migrations.