DBのスキーマ設計

[tocoteron]

https://dbdiagram.io/d/5f4a2c5588d052352cb53eed

CREATE TABLE `engineer_users` (
  `id` int unsigned PRIMARY KEY AUTO_INCREMENT,
  `login_name` varchar(255) UNIQUE NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `photo_url` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `hr_users` (
  `id` int unsigned PRIMARY KEY AUTO_INCREMENT,
  `firebase_uid` varchar(255) UNIQUE,
  `email` varchar(255),
  `first_name` varchar(255),
  `last_name` varchar(255),
  `company_name` varchar(255),
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `engineer_users_hr_users` (
  `id` int unsigned PRIMARY KEY AUTO_INCREMENT,
  `engineer_users_id` int unsigned,
  `hr_users_id` int unsigned,
  UNIQUE (`engineer_users_id`, `hr_users_id`),
  FOREIGN KEY (`engineer_users_id`) REFERENCES `engineer_users` (`id`) ON UPDATE CASCADE,
  FOREIGN KEY (`hr_users_id`) REFERENCES `hr_users` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `engineer_users_ability_reports` (
  `id` int unsigned PRIMARY KEY AUTO_INCREMENT,
  `engineer_users_id` int unsigned NOT NULL,
  `project_score` int unsigned NOT NULL,
  `repository_score` int unsigned NOT NULL,
  `commit_score` int unsigned NOT NULL,
  `pullreq_score` int unsigned NOT NULL,
  `issue_score` int unsigned NOT NULL,
  `speed_score` int unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`engineer_users_id`) REFERENCES `engineer_users` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;