In this section, we will design our MySQL database. Since we learned how to create a database connection, new database, new table, and how to run a MySQL query inside NetBeans in Chapter 3, Building a Facebook-like Status Poster Using NetBeans, we won't discuss them again, but we'll have a look at the database schema definition.
CREATE TABLE 'users' ( 'id' bigint(20) NOT NULL AUTO_INCREMENT, 'useremail' varchar(50) NOT NULL, 'password' char(32) NOT NULL, 'userhash' char(32) NOT NULL, 'userlevel' tinyint(4) NOT NULL, 'username' varchar(100) NOT NULL, 'phone' varchar(20) NULL, 'timestamp' int(11) unsigned NOT NULL, PRIMARY KEY ('id'), UNIQUE KEY 'useremail' ('useremail') ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
As you can see, we have id
(auto incremented with each entry) as the primary key and useremail
as the unique key in the users
table. We have a password
field to store the user's password to up to 32 characters; userhash
of 32 characters will store the user's login...