1.2. Planning the Database

In addition to planning the directory layout, thought needs to be given to the database layout as well. The information you choose to collect from your users will depend on what type of service your site offers. In turn, this affects how your database tables will look. At the very least a unique user id, username, password hash, and e-mail address should be stored. You will also need a mechanism to track which accounts have been verified or are pending verification.

CREATE TABLE WROX_USER (
    USER_ID    INTEGER UNSIGNED  NOT NULL  AUTO_INCREMENT,
    USERNAME   VARCHAR(20)       NOT NULL,
    PASSWORD   CHAR(40)          NOT NULL,
    EMAIL_ADDR VARCHAR(100)      NOT NULL,
    IS_ACTIVE  TINYINT(1)        DEFAULT 0,

    PRIMARY KEY (USER_ID)
)
ENGINE=MyISAM DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs AUTO_INCREMENT=0;

CREATE TABLE WROX_PENDING (
    USER_ID       INTEGER UNSIGNED  NOT NULL,
    TOKEN         CHAR(10)          NOT NULL,
    CREATED_DATE  TIMESTAMP         DEFAULT  CURRENT_TIMESTAMP,

    FOREIGN KEY (USER_ID)
        REFERENCES WROX_USER(USER_ID)
)
ENGINE=MyISAM DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs;

I have allocated 40 characters of storage for the password hash in WROX_USER as I will use the sha1() function which returns a 40-character hexadecimal string. You should never store the original password in the database — a good security precaution. The idea here is that a hash is generated when the user provides his or her password for the first time. The password given subsequently is hashed using the same function and the result is compared with what's stored to see if they match.

I set the maximum storage length for an e-mail address at 100 characters. Technically the standards set the maximum length for an e-mail address at 320 (64 characters are allowed for the username, one for the @ symbol and then 255 for the hostname). I don't know anyone that has such a long e-mail address though and I have seen plenty of database schemas that use 100 and work fine.

Some other information you may want to store are first and last name, address, city, state/province, postal code, phone numbers, and the list goes on.

The WROX_PENDING table has an automatically initializing timestamp column, which lets you go back to the database and delete pending accounts that haven't been activated after a certain amount of time. The table's columns could be merged with WROX_USER, but I chose to separate them since the pending token is only used once. User data is considered more permanent and the WROX_USER table isn't cluttered with temporary data.