Skip to content

Instantly share code, notes, and snippets.

@mrdaemon
Created December 8, 2011 03:43
Show Gist options
  • Save mrdaemon/1446020 to your computer and use it in GitHub Desktop.
Save mrdaemon/1446020 to your computer and use it in GitHub Desktop.
A simple MySQL schema for some sort of forums
/* Database Schema for simple Forums
* Author: Dee the great amateur RDBS wizard
* 07 Dec 2011 - Initial Version
*/
/* NOTE: You may want to alter this according to this setup,
* especially if you are not running the script as root and
* just need the tables.
*/
CREATE DATABASE `forum` DEFAULT CHARSET=utf8;
GRANT ALL PRIVILEGES ON forum.* TO 'fuser'@'localhost' IDENTIFIED BY 'pass';
USE forum;
/* NOTE: Tables use InnoDB as a table engine on MySQL, because lolrelations */
-- Users Table
-- Contains primary key, username, password hash and email address.
CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(45) NOT NULL ,
`password_hash` VARCHAR(75) NOT NULL ,
`email` VARCHAR(75) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
-- Categories table
-- Contains primary key, name and weight.
-- Weight would be used to soft-order the categories when displaying them.
-- Feel free to ignore/drop.
CREATE TABLE `categories` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(75) NOT NULL ,
`weight` INT UNSIGNED NOT NULL DEFAULT 1 ,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
-- Posts table
-- Stores Posts and the many-to-one relations with other tables/rows.
-- Here is a short rundown of the columns
-- id Primary key
-- title Post title
-- body Post body (the actual post). Feel free to change the type.
-- posted_on Post date. Shit MySQL DATETIME field, feel free to change
-- owner* Foreign key, references users.id
-- category* Foreign key, references categories.id
-- parent* Foreign key, references this table's primary key column.
-- The idea is to set this field to NULL to determine if
-- if the post is the first in a thread.
-- NOTE:
-- There are some constraints on the relations. Updating a referenced field
-- will automatically update the referer as well.
-- By default here ON DELETE does nothing, except for the categories,
-- mostly as an example. ON DELETE CASCADE means in this case, that if you
-- DELETE a Category, any Posts in this Category will also be DELETE'd.
-- Feel free to change/adapt, I mostly did it as an example.
--
CREATE TABLE `posts` (
`id` INT NOT NULL AUTO_INCREMENT ,
`title` VARCHAR(75) NOT NULL ,
`body` TEXT NOT NULL ,
`posted_on` DATETIME NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX `owner` (`id` ASC) ,
INDEX `category` () ,
CONSTRAINT `owner`
FOREIGN KEY (`id` )
REFERENCES `forum`.`users` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `parent_post`
FOREIGN KEY ()
REFERENCES `forum`.`posts` ()
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT `category`
FOREIGN KEY ()
REFERENCES `forum`.`categories` ()
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE = InnoDB;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment