2.2. Designing the Database

This project will make use of the WROX_USER table from the previous project, though you are required to add an additional column too. It will be used to store which permissions the user has been granted. The column should be an unsigned integer.

Two new tables are then needed to store forum data. The table WROX_FORUM stores the names and descriptions of the different forums under which messages are collected. The WROX_FORUM_MESSAGE stores each post and organizational information to keep everything in the right order. I choose not to add a column specifying an avatar since I can name the images the same as the username. As the username must be a unique alphanumeric value, storing the value in an extra column would be a redundant effort anyway.

+-------------------+-------------------------+------------+-----+---------------------+
| Field             | Type                    | Null       | Key | Default | Extra          |
+------------+------------------+------+-----+---------+------------------------------------+
| USER_ID           | int(10) unsigned        | NO         | PRI | NULL    | auto_increment |
| USERNAME          | varchar(20)             | NO         |     |         |                |
| PASSWORD          | char(40)                | NO         |     |         |                |
| EMAIL_ADDR        | varchar(100)            | NO         |     |         |                |
| IS_ACTIVE         | tinyint(1)              | YES        |     | 0       |                |
|PERMISSION  |int(10) unsigned  |NO   |     |0 |                |
+------------+------------------+------+-----+---------+------------------------------------+

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| FORUM_ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| FORUM_NAME  | varchar(50)      | NO   |     |         |                |
| DESCRIPTION | varchar(100)     | NO   |     |         |                |
+-------------+------------------+------+-----+---------+----------------+

+-----------+---------------------+------+-----+-----------------+----------------+
| Field     | Type                | Null | Key | Default         | Extra          |
+-----------+---------------------+------+-----+-----------------+----------------+
| MSG_ID    | bigint(20) unsigned | NO   | PRI | NULL            | auto_increment |
| PARENT_ID | bigint(20) unsigned | NO   | MUL | 0               |                |
| FORUM_ID  | int(10) unsigned    | NO   |     |                 |                |
| USER_ID   | int(10) unsigned    | NO   | MUL |                 |                |
| SUBJECT   | varchar(100)        | NO   |     |                 |                |
| MSG_TEXT  | text                | NO   |     |                 |                |
| MSG_DATE  | timestamp           | NO   |     |CURRENT_TIMESTAMP|                |
+-----------+---------------------+------+-----+-----------------+----------------+

Here is the SQL:

ALTER TABLE WROX_USER
    ADD PERMISSION INTEGER UNSIGNED NOT NULL DEFAULT 0
AFTER
    IS_ACTIVE;

CREATE TABLE WROX_FORUM (

FORUM_ID         INTEGER UNSIGNED  NOT NULL  AUTO_INCREMENT,
    FORUM_NAME       VARCHAR(50)       NOT NULL,
    DESCRIPTION      VARCHAR(100)      NOT NULL,

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

CREATE TABLE WROX_FORUM_MESSAGE (
    MESSAGE_ID         BIGINT UNSIGNED   NOT NULL  AUTO_INCREMENT,
    PARENT_MESSAGE_ID  BIGINT UNSIGNED   NOT NULL DEFAULT 0,
    FORUM_ID           INTEGER UNSIGNED  NOT NULL,
    USER_ID            INTEGER UNSIGNED  NOT NULL,
    SUBJECT            VARCHAR(100)      NOT NULL,
    MESSAGE_TEXT       TEXT              NOT NULL,
    MESSAGE_DATE       TIMESTAMP         NOT NULL  DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (MESSAGE_ID),

    FOREIGN KEY (PARENT_MESSAGE_ID)
        REFERENCES WROX_FORUM_MESSAGE(MESSAGE_ID),

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

Notice WROX_FORUM_MESSAGE references itself from the PARENT_MESSAGE_ID column to allow us to organize the messages in a thread — any post with a PARENT_MESSAGE_ID value of 0 will be considered the thread's start. Also note we store the user ID instead of the full username as the post's author. Storing the integer instead of the username saves space in the database and helps preserve the integrity of our data. We can join WROX_FORUM_MESSAGE and WROX_USER to retrieve the username or pass the id to User::getById() later.