CMS Database SQL with MariaDB / MySQL

db.sql

CREATE DATABASE IF NOT EXISTS `kerjarodi_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `kerjarodi_db`;
CREATE TABLE IF NOT EXISTS `role` (
  `id` INT NOT NULL AUTO_INCREMENT, 
  `role_name` VARCHAR(15) NOT NULL, 
  `description` VARCHAR(255) NULL, 
  PRIMARY KEY(id)
);
CREATE TABLE IF NOT EXISTS `user` (
  `id` INT NOT NULL AUTO_INCREMENT, 
  `role_id` INT, 
  `first_name` VARCHAR(50) NOT NULL, 
  `last_name` VARCHAR(50) NULL, 
  `mobile_phone` VARCHAR(15) NULL, 
  `username` VARCHAR(15) NOT NULL, 
  `email` VARCHAR(60) NOT NULL, 
  `password` VARCHAR(255) NOT NULL, 
  `bio` TEXT NULL, 
  `profile_picture` BLOB NULL, 
  `created_at` DATETIME NULL, 
  `updated_at` DATETIME NULL, 
  PRIMARY KEY (id), 
  FOREIGN KEY (`role_id`) REFERENCES role(id)
);
CREATE INDEX `idx_user` ON user(`username`, `email`);
CREATE TABLE IF NOT EXISTS `jwt_token` (
  `id` INT NOT NULL AUTO_INCREMENT, 
  `user_id` INT, 
  `name` VARCHAR(30) NOT NULL, 
  `token` VARCHAR(64) NOT NULL, 
  `abilities` VARCHAR(30) NOT NULL, 
  PRIMARY KEY(`id`), 
  FOREIGN KEY (`user_id`) REFERENCES user(`id`)
);
CREATE TABLE IF NOT EXISTS `post` (
  `id` INT NOT NULL AUTO_INCREMENT, 
  `author` INT, 
  `parent_post_id` INT NULL, 
  `title` VARCHAR(75) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, 
  `meta_title` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, 
  `slug` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, 
  `sumary` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, 
  `content` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  `published` ENUM('public', 'draft', 'trash') DEFAULT 'draft' NOT NULL, 
  `created_at` DATETIME NULL, 
  `updated_at` DATETIME NULL, 
  PRIMARY KEY(`id`), 
  FOREIGN KEY(`author`) REFERENCES user(`id`)
);
CREATE TABLE IF NOT EXISTS `post_meta`(
  `id` INT NOT NULL AUTO_INCREMENT, 
  `post_id` INT, 
  `key` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  `content` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  PRIMARY KEY(`id`), 
  FOREIGN KEY(`post_id`) REFERENCES post(`id`) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS `post_comment`(
  `id` INT NOT NULL AUTO_INCREMENT, 
  `post_id` INT, 
  `parent_comment_id` INT NULL, 
  `title` VARCHAR(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  `name` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  `content` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  `published` ENUM('public', 'draft', 'trash') DEFAULT 'draft' NOT NULL, 
  `profile_picture` BLOB NULL, 
  `created_at` DATETIME NULL, 
  `updated_at` DATETIME NULL, 
  PRIMARY KEY(`id`), 
  FOREIGN KEY(`post_id`) REFERENCES post(id)
);
CREATE TABLE IF NOT EXISTS `category`(
  `id` INT NOT NULL AUTO_INCREMENT, 
  `parent_category_id` INT NULL, 
  `title` VARCHAR(75) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  `meta_title` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, 
  `slug` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  `content` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  `created_at` DATETIME NULL, 
  `updated_at` DATETIME NULL, 
  PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `tag`(
  `id` INT NOT NULL AUTO_INCREMENT, 
  `title` VARCHAR(75) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  `meta_title` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, 
  `slug` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  `content` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  `created_at` DATETIME NULL, 
  `updated_at` DATETIME NULL, 
  PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `post_category`(`post_id` INT, `category_id` INT);
CREATE INDEX `idx_post_category` ON `post_category`(`post_id`, `category_id`);
CREATE TABLE IF NOT EXISTS `post_tag`(`post_id` INT, `tag_id` INT);
CREATE INDEX `idx_post_tag` ON `post_tag`(`post_id`, `tag_id`);