Database

This project uses a MySQL database to store information. The database can be imported locally via the 'webproject.sql' file. The import command is:

mysql -u [user] -p webproject < webproject.sql

Database Tables

The following sections outline the tables in the database.

user

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| user_id  | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(16) | NO   |     | NULL    |                |
| password | varchar(16) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

post

+---------+----------------+------+-----+---------+----------------+
| Field   | Type           | Null | Key | Default | Extra          |
+---------+----------------+------+-----+---------+----------------+
| post_id | int            | NO   | PRI | NULL    | auto_increment |
| user_id | int            | NO   | MUL | NULL    |                |
| title   | varchar(200)   | YES  |     | NULL    |                |
| body    | varchar(16000) | YES  |     | NULL    |                |
+---------+----------------+------+-----+---------+----------------+

The post table has the following foreign key constraints:

CONSTRAINT `fk_post_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)

comment

+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| commment_id | int           | NO   | PRI | NULL    | auto_increment |
| body        | varchar(1000) | YES  |     | NULL    |                |
| user_id     | int           | NO   | MUL | NULL    |                |
| post_id     | int           | NO   | MUL | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

The comment table has the following foreign key constraints:

CONSTRAINT `fk_comment_post_id` FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`)
CONSTRAINT `fk_comment_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)