DataBase 생성
이전 글에서 아래 명령어를 이용하여 mysql 데이터베이스에 권한을 부여해줬었다.
grant all privileges on mysql.* to 'boardadm'@'%';
하지만 board라는 데이터 베이스를 생성할 것이며 거기에 맞는 권한을 부여해줘야 한다.
아래 명령어를 이용하여 database 생성 후 권한 부여
CREATE DATABASE board;
GRANT ALL PRIVILEGES ON board.* TO 'boardadm'@'%';
테이블 생성
User 테이블
CREATE TABLE User (
user_id INT PRIMARY KEY AUTO_INCREMENT,
user_pw VARCHAR(255) NOT NULL,
user_name VARCHAR(100) NOT NULL,
user_email VARCHAR(100) NOT NULL UNIQUE,
user_regDate DATETIME DEFAULT CURRENT_TIMESTAMP
);
Roll 테이블
CREATE TABLE Roll (
roll_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
User_Roll 테이블
CREATE TABLE User_Roll (
user_id INT,
roll_id INT,
PRIMARY KEY (user_id, roll_id),
FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE,
FOREIGN KEY (roll_id) REFERENCES Roll(roll_id) ON DELETE CASCADE
);
Category 테이블
CREATE TABLE Category (
category_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL
);
Post 테이블
CREATE TABLE Post (
post_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
category_id INT,
view_count INT DEFAULT 0,
created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES Category(category_id) ON DELETE SET NULL
);
Post_Category 테이블
CREATE TABLE Post_Category (
post_id INT,
category_id INT,
PRIMARY KEY (post_id, category_id),
FOREIGN KEY (post_id) REFERENCES Post(post_id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES Category(category_id) ON DELETE CASCADE
);
Comment 테이블
CREATE TABLE Comment (
comment_id INT PRIMARY KEY AUTO_INCREMENT,
post_id INT,
user_id INT,
content TEXT NOT NULL,
created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (post_id) REFERENCES Post(post_id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE
);
