Skip to content

테이블 스키마

Jump to bottom
이예찬 edited this page Oct 22, 2024 · 11 revisions

create 문

CREATE TABLE `member` (
  `created_at` timestamp NOT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `updated_at` timestamp NULL DEFAULT NULL,
  `access_token` varchar(255) DEFAULT NULL,
  `profile_image` varchar(255) DEFAULT NULL,
  `provider_login_id` varchar(255) NOT NULL,
  `provider_user_id` varchar(255) DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `access_token` (`access_token`),
  UNIQUE KEY `provider_user_id` (`provider_user_id`),
  KEY `idx_provider_user_id` (`provider_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `pair_room` (
  `created_at` timestamp NOT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `updated_at` timestamp NULL DEFAULT NULL,
  `access_code` varchar(255) NOT NULL,
  `driver` varchar(255) NOT NULL,
  `navigator` varchar(255) NOT NULL,
  `status` enum('COMPLETED','IN_PROGRESS','DELETED') NOT NULL,
  `mission_url` varchar(255) NOT NULL,
  `easy_access_code` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_easy_access_code` (`easy_access_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `category` (
   `created_at` timestamp NOT NULL,
   `id` bigint NOT NULL AUTO_INCREMENT,
   `pair_room_id` bigint NOT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `category_name` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `FKiutv6bi9cv0h3v6qnabj67glo` (`pair_room_id`),
   CONSTRAINT `FKiutv6bi9cv0h3v6qnabj67glo` FOREIGN KEY (`pair_room_id`) REFERENCES `pair_room` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 reference_link | CREATE TABLE `reference_link` (
  `category_id` bigint DEFAULT NULL,
  `created_at` timestamp NOT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `pair_room_id` bigint NOT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `url` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKoe9tq04uydqu0v8uqfnlj7cui` (`category_id`),
  KEY `FKdweuwdoj8f0evadvcywgn8m2m` (`pair_room_id`),
  CONSTRAINT `FKdweuwdoj8f0evadvcywgn8m2m` FOREIGN KEY (`pair_room_id`) REFERENCES `pair_room` (`id`),
  CONSTRAINT `FKoe9tq04uydqu0v8uqfnlj7cui` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `open_graph` (
   `created_at` timestamp NOT NULL,
   `id` bigint NOT NULL AUTO_INCREMENT,
   `reference_link_id` bigint NOT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `description` varchar(255) NOT NULL,
   `head_title` varchar(255) NOT NULL,
   `image` varchar(1024) NOT NULL,
   `open_graph_title` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `UK3t0b7kmdcqhoxdq700er5ddtb` (`reference_link_id`),
   CONSTRAINT `FKlfob95i3tk96np0nvjbg6o1y2` FOREIGN KEY (`reference_link_id`) REFERENCES `reference_link` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `timer` (
   `created_at` timestamp NOT NULL,
   `duration` bigint NOT NULL,
   `id` bigint NOT NULL AUTO_INCREMENT,
   `pair_room_id` bigint NOT NULL,
   `remaining_time` bigint NOT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `UK7k056epe6xxy6meyvwkhmjr29` (`pair_room_id`),
   CONSTRAINT `FKmjafhb7ff8hdls3d1kw7xlpws` FOREIGN KEY (`pair_room_id`) REFERENCES `pair_room` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `todo` (
   `is_checked` bit(1) NOT NULL,
   `sort` double NOT NULL,
   `created_at` timestamp NOT NULL,
   `id` bigint NOT NULL AUTO_INCREMENT,
   `pair_room_id` bigint DEFAULT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `content` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `FKj66nbe7l84epw84krkh1ak6c4` (`pair_room_id`),
   CONSTRAINT `FKj66nbe7l84epw84krkh1ak6c4` FOREIGN KEY (`pair_room_id`) REFERENCES `pair_room` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `pair_room_member` (
   `id` bigint NOT NULL AUTO_INCREMENT,
   `member_id` bigint DEFAULT NULL,
   `pair_room_id` bigint DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `FK1vldt58pgignyxqcy49th2y0g` (`member_id`),
   KEY `FKbh7mwbv4mkl0bj32vpcpv3xkd` (`pair_room_id`),
   CONSTRAINT `FK1vldt58pgignyxqcy49th2y0g` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`),
   CONSTRAINT `FKbh7mwbv4mkl0bj32vpcpv3xkd` FOREIGN KEY (`pair_room_id`) REFERENCES `pair_room` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `member` (
  `created_at` timestamp NOT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `updated_at` timestamp NULL DEFAULT NULL,
  `access_token` varchar(255) DEFAULT NULL,
  `profile_image` varchar(255) DEFAULT NULL,
  `provider_login_id` varchar(255) NOT NULL,
  `provider_user_id` varchar(255) DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `access_token` (`access_token`),
  UNIQUE KEY `provider_user_id` (`provider_user_id`),
  KEY `idx_provider_user_id` (`provider_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `pair_room` (
  `created_at` timestamp NOT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `updated_at` timestamp NULL DEFAULT NULL,
  `access_code` varchar(255) NOT NULL,
  `driver` varchar(255) NOT NULL,
  `navigator` varchar(255) NOT NULL,
  `status` enum('COMPLETED','IN_PROGRESS','DELETED') NOT NULL,
  `mission_url` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `access_code` (`access_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `category` (
   `created_at` timestamp NOT NULL,
   `id` bigint NOT NULL AUTO_INCREMENT,
   `pair_room_id` bigint NOT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `category_name` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `FKiutv6bi9cv0h3v6qnabj67glo` (`pair_room_id`),
   CONSTRAINT `FKiutv6bi9cv0h3v6qnabj67glo` FOREIGN KEY (`pair_room_id`) REFERENCES `pair_room` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `reference_link` (
   `category_id` bigint DEFAULT NULL,
   `created_at` timestamp NOT NULL,
   `id` bigint NOT NULL AUTO_INCREMENT,
   `pair_room_id` bigint NOT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `url` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `FKoe9tq04uydqu0v8uqfnlj7cui` (`category_id`),
   KEY `FKdweuwdoj8f0evadvcywgn8m2m` (`pair_room_id`),
   CONSTRAINT `FKdweuwdoj8f0evadvcywgn8m2m` FOREIGN KEY (`pair_room_id`) REFERENCES `pair_room` (`id`),
   CONSTRAINT `FKoe9tq04uydqu0v8uqfnlj7cui` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `open_graph` (
   `created_at` timestamp NOT NULL,
   `id` bigint NOT NULL AUTO_INCREMENT,
   `reference_link_id` bigint NOT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `description` varchar(255) NOT NULL,
   `head_title` varchar(255) NOT NULL,
   `image` varchar(255) NOT NULL,
   `open_graph_title` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `UK3t0b7kmdcqhoxdq700er5ddtb` (`reference_link_id`),
   CONSTRAINT `FKlfob95i3tk96np0nvjbg6o1y2` FOREIGN KEY (`reference_link_id`) REFERENCES `reference_link` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `timer` (
   `created_at` timestamp NOT NULL,
   `duration` bigint NOT NULL,
   `id` bigint NOT NULL AUTO_INCREMENT,
   `pair_room_id` bigint NOT NULL,
   `remaining_time` bigint NOT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `UK7k056epe6xxy6meyvwkhmjr29` (`pair_room_id`),
   CONSTRAINT `FKmjafhb7ff8hdls3d1kw7xlpws` FOREIGN KEY (`pair_room_id`) REFERENCES `pair_room` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `todo` (
   `is_checked` bit(1) NOT NULL,
   `sort` double NOT NULL,
   `created_at` timestamp NOT NULL,
   `id` bigint NOT NULL AUTO_INCREMENT,
   `pair_room_id` bigint DEFAULT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `content` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `FKj66nbe7l84epw84krkh1ak6c4` (`pair_room_id`),
   CONSTRAINT `FKj66nbe7l84epw84krkh1ak6c4` FOREIGN KEY (`pair_room_id`) REFERENCES `pair_room` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `pair_room_member` (
   `id` bigint NOT NULL AUTO_INCREMENT,
   `member_id` bigint DEFAULT NULL,
   `pair_room_id` bigint DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `FK1vldt58pgignyxqcy49th2y0g` (`member_id`),
   KEY `FKbh7mwbv4mkl0bj32vpcpv3xkd` (`pair_room_id`),
   CONSTRAINT `FK1vldt58pgignyxqcy49th2y0g` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`),
   CONSTRAINT `FKbh7mwbv4mkl0bj32vpcpv3xkd` FOREIGN KEY (`pair_room_id`) REFERENCES `pair_room` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE retrospect (
    created_at TIMESTAMP NOT NULL,
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NULL,
    pair_room_id BIGINT NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (member_id) REFERENCES member(id),
    FOREIGN KEY (pair_room_id) REFERENCES pair_room(id)
);

CREATE TABLE retrospect_content (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    retrospect_id BIGINT NULL,
    content VARCHAR(1100) NOT NULL,
    question_type ENUM('FIRST', 'SECOND', 'THIRD', 'FOURTH') NOT NULL,
    FOREIGN KEY (retrospect_id) REFERENCES retrospect(id)
);

테이블 시각화

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

🍀 코딩해듀오

전체

정보

BE

FE

기술

컨벤션

테스트

성능 개선 & 접근성 개선 리포트

인프라

Clone this wiki locally