PHP and MySQL®: Create-Modify-Reuse

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