DATABASE 구조

TABLE

USER

• id(pk)
• naver_id(unique)

ROOM

• id(pk)
• user_id(fk USER] id)
• title
• created_at(옵션)
• updated_at(옵션)

QUIZSET

• id(pk)
• room_id(fk ROOM] id)
• title
• order

QUIZ

• id(pk)
• quizset_id(fk QUIZSET] id)
• title
• order
• time_limit
• score
• image_path

ITEM

• id(pk)
• quiz_id(fk QUIZ] id)
• title
• order
• is_answer

ANALYSIS

• id(pk)
• quiz_id(fk QUIZ] id)
• response_count
• correct_count
• player_count

초기 관계도

SQL

CREATE TABLE IF NOT EXISTS `ANALYSIS` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `response_count` int(11) DEFAULT NULL,
  `correct_count` int(11) DEFAULT NULL,
  `player_count` int(11) DEFAULT NULL,
  `quiz_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ANALYSIS_QUIZ1_idx` (`quiz_id`),
  CONSTRAINT `fk_ANALYSIS_QUIZ1` FOREIGN KEY (`quiz_id`) REFERENCES `QUIZ` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ITEM` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) DEFAULT NULL,
  `item_order` int(11) DEFAULT NULL,
  `quiz_id` int(11) NOT NULL,
  `is_answer` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ITEM_QUIZ1_idx` (`quiz_id`),
  CONSTRAINT `fk_ITEM_QUIZ1` FOREIGN KEY (`quiz_id`) REFERENCES `QUIZ` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=589 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `QUIZ` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(240) DEFAULT NULL,
  `quiz_order` int(11) DEFAULT NULL,
  `quizset_id` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL,
  `time_limit` int(11) DEFAULT NULL,
  `image_path` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_QUIZ_PACKAGE1_idx` (`quizset_id`),
  CONSTRAINT `fk_QUIZ_PACKAGE1` FOREIGN KEY (`quizset_id`) REFERENCES `QUIZSET` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `QUIZSET` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(52) DEFAULT NULL,
  `quizset_order` int(11) DEFAULT NULL,
  `room_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_PACKAGE_ROOM1_idx` (`room_id`),
  CONSTRAINT `fk_PACKAGE_ROOM1` FOREIGN KEY (`room_id`) REFERENCES `ROOM` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ROOM` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(26) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_room` (`user_id`,`title`),
  KEY `fk_ROOM_USER_idx` (`user_id`),
  CONSTRAINT `fk_ROOM_USER` FOREIGN KEY (`user_id`) REFERENCES `USER` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=134 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `USER` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `naver_id` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_UNIQUE` (`naver_id`)
) ENGINE=InnoDB AUTO_INCREMENT=738 DEFAULT CHARSET=utf8;