Database Schema

1. 테이블

quiz_set

Column Data Type Constraints Description
id INT PRIMARY KEY, AUTO_INCREMENT 퀴즈셋 ID
name VARCHAR(50) NOT NULL 퀴즈셋 이름
recommend BOOLEAN NOT NULL 기본으로 추천하는 quizSet
create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 레코드 생성 시간
update_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 레코드 수정 시간

quiz

Column Data Type Constraints Description
id INT PRIMARY KEY, AUTO_INCREMENT 퀴즈 ID
question VARCHAR(255) NOT NULL 퀴즈 질문
answer VARCHAR(50) NOT NULL 퀴즈 정답
play_time INT NOT NULL 퀴즈 풀이 시간
quiz_type VARCHAR(20) NOT NULL 퀴즈 타입 ex) 단답형, 객관식 등
quiz_set_id INT FOREIGN KEY REFERENCES quiz_set(id) 퀴즈셋 ID: 외래키
create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 레코드 생성 시간
update_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 레코드 수정 시간

https://www.erdcloud.com/d/t3diZy5EAFJYpAvoi

init.sql

-- 기존 테이블 삭제
DROP TABLE IF EXISTS quiz;
DROP TABLE IF EXISTS quiz_set;

SET NAMES utf8mb4;
SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_connection = utf8mb4;

ALTER DATABASE booquiz CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE quiz_set (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  recommended BOOLEAN DEFAULT FALSE,       -- 추천 여부 필드
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_update_at_name (updated_at DESC, name),
  INDEX idx_update_at_recommended (updated_at DESC, recommended)
);

-- quiz 테이블 생성
CREATE TABLE quiz (
  id INT AUTO_INCREMENT PRIMARY KEY,
  question VARCHAR(255) NOT NULL,
  answer VARCHAR(50) NOT NULL,
  play_time INT NOT NULL,
  quiz_type VARCHAR(20) NOT NULL,
  quiz_set_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (quiz_set_id) REFERENCES `quiz_set`(id)
);

-- 데이터 삽입
INSERT INTO quiz_set (name, recommended) VALUES ('데모 퀴즈', true);

INSERT INTO quiz (question, answer, play_time, quiz_type, quiz_set_id) VALUES
   ('포도가 자기소개하면?', '포도당', 30, 'SHORT_ANSWER', 1),
   ('고양이를 싫어하는 동물은?', '미어캣', 30, 'SHORT_ANSWER', 1),
   ('게를 냉동실에 넣으면?', '게으름', 30, 'SHORT_ANSWER', 1),
   ('오리를 생으로 먹으면?', '회오리', 30, 'SHORT_ANSWER', 1),
   ('네 사람이 동시에 오줌을 누면?', '포뇨', 30, 'SHORT_ANSWER', 1),
   ('지브리가 뭘로 돈 벌게요?', '토토로', 30, 'SHORT_ANSWER', 1);

→ DELETE CASCADE 조건이 있게 되면 퀴즈셋을 지우면 퀴즈가 사라지게됨

→ 대부분 개발 환경에서는 인덱스만 걸어두는 방식으로 처리

→ 퀴즈셋 아이디가 없는 퀴즈를 조회하지 못하도록

→ MySQL 8버전

→ 최신 순으로 정렬해서 받아오는 게 약간 정석에 가까움 → 시간에는 내림 차순, 이름에는 오름 차순

DB 환경


docker run \\
--name mysql-container \\
--env-file /home/booquiz/.env \\
-v /home/booquiz/init.sql:/docker-entrypoint-initdb.d/init.sql \\
-p 3306:3306 \\
-d mysql:8.0

# db 사용자 추가
CREATE USER ‘booquiz’@‘10.0.1.6’ IDENTIFIED BY ‘${password}’;

# db 사용자 권한 설정
GRANT SELECT, INSERT, UPDATE, DELETE ON booquiz.* TO 'booquiz'@'10.0.1.6'

# 초기 sql문 설정
SOURCE /home/booquiz/init.sql;