オンライン試験システムをサポートする MySQL テーブル構造をどのように設計するか?

CREATE TABLE `user` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `role` ENUM('student', 'teacher') NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username_UNIQUE` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `exam` (
  `exam_id` INT(11) NOT NULL AUTO_INCREMENT,
  `exam_name` VARCHAR(255) NOT NULL,
  `exam_time` DATETIME NOT NULL,
  `duration` INT(11) NOT NULL,
  PRIMARY KEY (`exam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `question` (
  `question_id` INT(11) NOT NULL AUTO_INCREMENT,
  `exam_id` INT(11) NOT NULL,
  `content` TEXT NOT NULL,
  `options` TEXT NOT NULL,
  `answer` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`question_id`),
  FOREIGN KEY (`exam_id`) REFERENCES `exam`(`exam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `answer_record` (
  `record_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `question_id` INT(11) NOT NULL,
  `selected_answer` VARCHAR(255) NOT NULL,
  `answer_time` DATETIME NOT NULL,
  PRIMARY KEY (`record_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`),
  FOREIGN KEY (`question_id`) REFERENCES `question`(`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `score` (
  `score_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `exam_id` INT(11) NOT NULL,
  `score` INT(11) NOT NULL,
  PRIMARY KEY (`score_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`),
  FOREIGN KEY (`exam_id`) REFERENCES `exam`(`exam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;