Users table database
CREATE TABLE users (
id int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
username varchar(255) NOT NULL,
email varchar(255) NOT NULL,
role enum('Author','Admin') DEFAULT NULL,
password varchar(255) NOT NULL,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
POSTS database
CREATE TABLE posts (
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id int(11) DEFAULT NULL,
title varchar(255) NOT NULL,
slug varchar(255) NOT NULL UNIQUE,
views int(11) NOT NULL DEFAULT '0',
image varchar(255) NOT NULL,
body text NOT NULL,
published tinyint(1) NOT NULL,
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
TOPICS
+----+-----------+------------------------+------------+ | field | type | specs | +----+-----------+------------------------+------------+ | id | INT(11) | | | name | VARCHAR(255) | | | slug | VARCHAR(255) | UNIQUE | +----------------+--------------+---------+------------+
POST_TOPICS
+----+-----------+------------------------+------------+ | field | type | specs | +----+-----------+------------------------+------------+ | id | INT(11) | | | post_id | INT(11) | UNIQUE | | topic_id | INT(11) | | +----------------+--------------+---------+------------+
ENTRIES
INSERT INTO topics (id, name, slug) VALUES
(1, 'Inspiration', 'inspiration'),
(2, 'Motivation', 'motivation'),
(3, 'Diary', 'diary')
INSERT INTO post_topic (id, post_id, topic_id) VALUES
(1, 1, 1),
(2, 2, 2)