👷‍♀️ 화면 API 설계

회원 API

여행?지도? API

장소보관함 API

일정 API

↗️ ERD 다이어그램

• tool : dbdiagram.io
• code

📑 테이블 생성

// create schema
CREATE SCHEMA `Travel` DEFAULT CHARACTER SET utf8 ;

// users 테이블 생성
CREATE TABLE `Travel`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `login_id` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `salt` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `login_id_UNIQUE` (`login_id` ASC) VISIBLE);
  
// maps 테이블 생성
CREATE TABLE `Travel`.`maps` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `user_id` INT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `fk_maps_users_id_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `fk_maps_users_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `Travel`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

// schedules 테이블 생성
CREATE TABLE `Travel`.`schedules` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `day` INT NOT NULL,
  `start_time` TIMESTAMP NULL,
  `map_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_scheduls_maps_id_idx` (`map_id` ASC) VISIBLE,
  CONSTRAINT `fk_scheduls_maps_id`
    FOREIGN KEY (`map_id`)
    REFERENCES `Travel`.`maps` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

// places 테이블 생성
CREATE TABLE `Travel`.`places` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `content_id` VARCHAR(45) NULL,
  `content_type_id` VARCHAR(45) NULL,
  `add1` VARCHAR(100) NULL,
  `add2` VARCHAR(100) NULL,
  `first_image` VARCHAR(200) NULL,
  `first_image2` VARCHAR(200) NULL,
  `latitude` DECIMAL NOT NULL,
  `longitude` DECIMAL NOT NULL,
  `overview` VARCHAR(150) NULL,
  `tel` VARCHAR(15) NULL,
  `hmpg` VARCHAR(150) NULL,
  `map_id` INT NOT NULL,
  `state_code` TINYINT(1) NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `fk_places_maps_id_idx` (`map_id` ASC) VISIBLE,
  CONSTRAINT `fk_places_maps_id`
    FOREIGN KEY (`map_id`)
    REFERENCES `Travel`.`maps` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

// detail 테이블 생성
CREATE TABLE `Travel`.`schedule_detail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `place_id` INT NOT NULL,
  `schedule_id` INT NOT NULL,
  `order_by` INT NOT NULL,
  `play_time` INT NULL,
  `move_time` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_detail_schedules_id_idx` (`schedule_id` ASC) VISIBLE,
  INDEX `fk_detail_places_id_idx` (`place_id` ASC) VISIBLE,
  CONSTRAINT `fk_detail_schedules_id`
    FOREIGN KEY (`schedule_id`)
    REFERENCES `Travel`.`schedules` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_detail_places_id`
    FOREIGN KEY (`place_id`)
    REFERENCES `Travel`.`places` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

🏹 GitHub