PHP and MySQL®: Create-Modify-Reuse

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 ...