Day 0.1
Phase: Planning
Decided to work on the potential database schema. I thought about what data I would absolutely need to record. I don’t want to get bogged down by other features so what you see below is what I would consider the bare-bones. This will likely change in the future but for now, this is my starting block. I did my best to make sure I was in third normal-form as well.
Overview
+-----------------+
| Tables_in_Forum |
+-----------------+
| Categories |
| Replies |
| Threads |
| Users |
+-----------------+
We have a total of four tables so far. A table to hold the different categories, users, threads, and replies.
Categories
+----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+----------------+
| category_id | int(11) | NO | PRI | NULL | auto_increment |
| category_name | varchar(255) | NO | | NULL | |
| category_description | varchar(255) | NO | | NULL | |
+----------------------+--------------+------+-----+---------+----------------+
Each Category will be uniquely identifiable by its category_id. The meaning full part that people will search by though is the category name. However, if a category name is every changed or removed it would break a lot of existing records and then each reference to that name would need to be manually altered to the new name. If the database gets hundreds of thousands of posts then that process could take hours for a simple change. This is a quality of life thing.
Users
+---------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+-------------------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| user_handle | varchar(255) | YES | | NULL | |
| user_password | varchar(255) | NO | | NULL | |
| user_signup | datetime | YES | | CURRENT_TIMESTAMP | |
+---------------+--------------+------+-----+-------------------+----------------+
A user is pretty simple. All that’s really needed is a way to uniquely identify them and their desired handle plus salted & hashed password. The only other meaningful bit is to track when the user signed up for things to determine the age of the account and such.
Threads
+-----------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+----------------+
| thread_id | int(11) | NO | PRI | NULL | auto_increment |
| thread_created | datetime | YES | | CURRENT_TIMESTAMP | |
| thread_title | varchar(255) | NO | | NULL | |
| thread_author | int(11) | NO | MUL | NULL | |
| thread_category | int(11) | NO | MUL | NULL | |
+-----------------+--------------+------+-----+-------------------+----------------+
A thread table only exists to link multiple users to multiple replies; referred to as an intermediary table. Since there is a many-to-many relationship. Many users can have many different replies in this table.
However, there is a one-to-many relationship between users to a thread. I.E a user can have many threads but each thread can only relate to one user.
Other meaningful bits are the thread title so users could search for something specific and when the thread was created for chronological searching.
Replies
+---------------+----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+-------------------+----------------+
| reply_id | int(11) | NO | PRI | NULL | auto_increment |
| reply_body | text | NO | | NULL | |
| reply_author | int(11) | NO | MUL | NULL | |
| reply_created | datetime | YES | | CURRENT_TIMESTAMP | |
| reply_thread | int(11) | NO | MUL | NULL | |
+---------------+----------+------+-----+-------------------+----------------+
Replies are interesting as they link back to both an author and a thread. This is a one-to-one relationship though.
A reply can only relate to a single user, in a single thread.
By design, a reply can be large. Up to 64K–over 65,535–characters. I figured this was a reasonable ceiling for any single post.
Flow
So after a new user joins, in a forum where the categories have been defined he will go to create a new thread. In the thread, he will define its title and its contents. Then thread will be created with the specified title, and the contents will become the first post in the newly created thread.
Source Code
I am working on cobbling a repository together, so, for now, the source will be provided below.
I am using MariaDB, so if you have that installed and want to check this out on your machine you can bootstrap the database with the source file below.
schema.sql
CREATE DATABASE IF NOT EXISTS Forum;
USE Forum;
CREATE TABLE IF NOT EXISTS Users (
user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_handle VARCHAR(255) DEFAULT NULL,
user_password VARCHAR(255) NOT NULL,
user_signup DATETIME DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS Categories (
category_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
category_name VARCHAR(255) NOT NULL,
category_description VARCHAR(255) NOT NULL
);
CREATE TABLE IF NOT EXISTS Threads (
thread_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
thread_created DATETIME DEFAULT NOW(),
thread_title VARCHAR(255) NOT NULL,
thread_author INT NOT NULL,
thread_category INT NOT NULL,
CONSTRAINT `fk_thread_author`
FOREIGN KEY (thread_author) REFERENCES Users (user_id)
ON DELETE CASCADE
ON UPDATE RESTRICT,
CONSTRAINT `fk_thread_category`
FOREIGN KEY (thread_category) REFERENCES Categories (category_id)
ON DELETE CASCADE
ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS Replies (
reply_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
reply_body TEXT NOT NULL,
reply_author INT NOT NULL,
reply_created DATETIME DEFAULT NOW(),
reply_thread INT NOT NULL,
CONSTRAINT `fk_reply_author`
FOREIGN KEY (reply_author) REFERENCES Users (user_id)
ON DELETE CASCADE
ON UPDATE RESTRICT,
CONSTRAINT `fk_reply_thread`
FOREIGN KEY (reply_thread) REFERENCES Threads (thread_id)
ON DELETE CASCADE
ON UPDATE RESTRICT
);
