ERD 설계

[zhy2on]

https://dbdiagram.io/d/PJT-690d8b7e6735e11170adcb5b

1차 설계

dbml

//////////////////////////////////////////////////////
// 🔐 권한 (Role)
//////////////////////////////////////////////////////
Table role {
 id          bigint       [pk, increment] // ✅ ID 추가
 code        varchar(20)  [not null, unique] // 'USER', 'ADMIN'
 name        varchar(50)  [not null]
}

//////////////////////////////////////////////////////
// 👤 유저 상태 (User Status)
//////////////////////////////////////////////////////
Table user_status {
 id          bigint       [pk, increment] // ✅ ID 추가
 code        varchar(20)  [not null, unique] // 'ACTIVE', 'BANNED'
 name        varchar(50)  [not null]
 description varchar(100)
}

Table user {
 id                bigint       [pk, increment]
 
 // ✅ 참조를 code -> id로 변경
 role_id           bigint       [not null, ref: > role.id] 
 status_id         bigint       [not null, ref: > user_status.id]

 email             varchar(100)
 password_hash     varchar(255) [not null]
 nickname          varchar(50)  [not null]
 profile_image_url varchar(255)
 
 created_at        datetime     [not null]
 updated_at        datetime     [not null]

 indexes {
   (email)
 }
}

//////////////////////////////////////////////////////
// 🌏 지역 (Region)
//////////////////////////////////////////////////////
Table region_type {
 id   bigint      [pk, increment]
 code varchar(20) [not null, unique] // 'CITY', 'DISTRICT'
 name varchar(50) [not null]
}

Table region {
 id           bigint       [pk, increment]
 parent_id    bigint       [ref: > region.id]
 
 // ✅ Type도 테이블로 분리하여 ID 참조
 type_id      bigint       [not null, ref: > region_type.id]
 
 name         varchar(100) [not null]
 code         varchar(50)
 slug         varchar(100)
 timezone     varchar(50)
 created_at   datetime     [not null]
 updated_at   datetime     [not null]
}

Table region_boundary {
 region_id        bigint [pk, ref: > region.id]
 boundary_geojson text   [not null]
 created_at       datetime [not null]
}

//////////////////////////////////////////////////////
// 📍 스팟 관련 (Spot)
//////////////////////////////////////////////////////
Table spot_status {
 id   bigint      [pk, increment] // ✅ ID 추가
 code varchar(20) [not null, unique] // 'ACTIVE', 'CLOSED'
 name varchar(50) [not null]
}

Table spot_category {
 id   bigint       [pk, increment]
 name varchar(100) [not null, unique]
}

Table spot {
 id                bigint       [pk, increment]
 region_id         bigint       [not null, ref: > region.id]
 category_id       bigint       [not null, ref: > spot_category.id]
 
 // ✅ 참조를 code -> id로 변경
 status_id         bigint       [not null, ref: > spot_status.id]

 name              varchar(255) [not null]
 lat               double       [not null]
 lon               double       [not null]
 summary           text
 address           text
 phone             varchar(50)
 primary_photo_url text
 source_provider   varchar(50)
 source_place_id   varchar(100)
 created_at        datetime     [not null]

 indexes {
   (lat, lon)
   (source_provider, source_place_id) [unique]
 }
}

Table spot_category_map {
 spot_id     bigint [not null, ref: > spot.id]
 category_id bigint [not null, ref: > spot_category.id]
 created_at  datetime [not null]
 
 indexes { (spot_id, category_id) [pk] }
}

Table spot_photo {
 id         bigint   [pk, increment]
 spot_id    bigint   [not null, ref: > spot.id]
 url        text     [not null]
 is_primary bool     [not null, default: false]
 width      int
 height     int
 created_at datetime [not null]
}

Table spot_hour {
 id          bigint   [pk, increment]
 spot_id     bigint   [not null, ref: > spot.id]
 dow         smallint [not null]
 segment_no  smallint [not null, default: 1]
 is_24h      bool     [not null, default: false]
 open_time   time
 close_time  time
 break_start time
 break_end   time
}

Table spot_hour_exception {
 id         bigint [pk, increment]
 spot_id    bigint [not null, ref: > spot.id]
 date       date   [not null]
 is_closed  bool   [not null, default: false]
 open_time  time
 close_time time
}

//////////////////////////////////////////////////////
// 🏷 태그 (Tag)
//////////////////////////////////////////////////////
Table tag_category {
 id   bigint      [pk, increment]
 code varchar(20) [not null, unique] // 'THEME', 'MOOD'
 name varchar(50) [not null]
}

Table tag {
 id          bigint [pk, increment]
 name        varchar(50) [not null, unique]
 
 // ✅ 카테고리도 ID 참조로 변경
 category_id bigint [ref: > tag_category.id] 
 
 created_at  datetime [not null]
}

Table spot_tag {
 spot_id bigint [not null, ref: > spot.id]
 tag_id  bigint [not null, ref: > tag.id]
 indexes { (spot_id, tag_id) [pk] }
}

Table travel_plan_tag {
 plan_id bigint [not null, ref: > travel_plan.id]
 tag_id  bigint [not null, ref: > tag.id]
 indexes { (plan_id, tag_id) [pk] }
}

//////////////////////////////////////////////////////
// 🗺 여행 계획 (Travel Plan)
//////////////////////////////////////////////////////
Table plan_status {
 id   bigint      [pk, increment] // ✅ ID 추가
 code varchar(20) [not null, unique] // 'PLANNED', 'DONE'
 name varchar(50) [not null]
}

Table travel_plan {
 id            bigint       [pk, increment]
 user_id       bigint       [not null, ref: > user.id]
 
 // ✅ 참조를 code -> id로 변경
 status_id     bigint       [not null, ref: > plan_status.id]

 title         varchar(100) [not null]
 summary       varchar(255)
 planned_date  date
 visited_date  date
 region_code   varchar(50)
 area_name     varchar(100)
 thumbnail_url varchar(255)
 is_public     bool         [not null, default: true]
 is_deleted    bool         [not null, default: false]
 created_at    datetime     [not null]
 updated_at    datetime     [not null]
}

Table travel_plan_item {
 id         bigint   [pk, increment]
 plan_id    bigint   [not null, ref: > travel_plan.id]
 spot_id    bigint   [not null, ref: > spot.id]
 order_no   int      [not null]
 memo       text
 created_at datetime [not null]

 indexes {
   (plan_id, order_no) [unique]
 }
}

//////////////////////////////////////////////////////
// 💬 채팅 (Chat)
//////////////////////////////////////////////////////
Table chat_room_type {
 id   bigint      [pk, increment] // ✅ ID 추가
 code varchar(20) [not null, unique] // 'PLAN', 'DM'
 name varchar(50) [not null]
}

Table chat_room {
 id         bigint       [pk, increment]
 plan_id    bigint       [ref: > travel_plan.id]
 
 // ✅ 참조를 code -> id로 변경
 type_id    bigint       [not null, ref: > chat_room_type.id]
 
 name       varchar(100)
 created_at datetime     [not null]
 updated_at datetime     [not null]
}

Table chat_room_member {
 room_id    bigint   [not null, ref: > chat_room.id]
 user_id    bigint   [not null, ref: > user.id]
 joined_at  datetime [not null]
 is_active  bool     [not null, default: true]

 indexes { (room_id, user_id) [pk] }
}

Table chat_message_type {
 id   bigint      [pk, increment] // ✅ ID 추가
 code varchar(20) [not null, unique] // 'TEXT', 'IMAGE'
 name varchar(50) [not null]
}

Table chat_message {
 id              bigint       [pk, increment]
 room_id         bigint       [not null, ref: > chat_room.id]
 sender_id       bigint       [not null, ref: > user.id]
 
 // ✅ 참조를 code -> id로 변경
 message_type_id bigint       [not null, ref: > chat_message_type.id]
 
 content         text
 file_url        varchar(500)
 created_at      datetime     [not null]
 is_deleted      bool         [not null, default: false]

 indexes {
   (room_id, created_at)
 }
}

//////////////////////////////////////////////////////
// ⭐ 북마크, 저널, 프로필
//////////////////////////////////////////////////////
Table travel_plan_bookmark {
 user_id    bigint   [not null, ref: > user.id]
 plan_id    bigint   [not null, ref: > travel_plan.id]
 created_at datetime [not null]

 indexes {
   (user_id, plan_id) [pk]
 }
}

Table travel_journal_mood {
 id   bigint      [pk, increment]
 code varchar(20) [not null, unique] // 'HAPPY', 'TIRED'
 name varchar(50) [not null]
}

Table travel_journal {
 id           bigint       [pk, increment]
 plan_id      bigint       [not null, ref: > travel_plan.id]
 user_id      bigint       [not null, ref: > user.id]
 visited_date date         [not null]
 title        varchar(120) [not null]
 diary        text
 
 // ✅ 기분(Mood)도 테이블 분리 및 ID 참조
 mood_id      bigint       [ref: > travel_journal_mood.id]
 
 weather_note varchar(50)
 companion    varchar(100)
 rating       tinyint
 total_spent  int
 is_private   bool         [not null, default: false]
 is_deleted   bool         [not null, default: false]
 created_at   datetime     [not null]
 updated_at   datetime     [not null]
}

Table travel_journal_photo {
 id         bigint   [pk, increment]
 journal_id bigint   [not null, ref: > travel_journal.id]
 spot_id    bigint   [ref: > spot.id]
 url        text     [not null]
 taken_at   datetime
 caption    text
 order_no   int
 created_at datetime [not null]
}

Table travel_style {
 id   bigint      [pk, increment]
 code varchar(20) [not null, unique] // 'CAFE_LOVER'
 name varchar(50) [not null]
}

Table user_profile {
 user_id              bigint       [pk, ref: > user.id]
 bio                  varchar(255)
 intro                text
 home_region_id       bigint       [ref: > region.id]
 travel_style_summary text
 
 // ✅ 스타일도 ID 참조
 travel_style_id      bigint       [ref: > travel_style.id]

 profile_banner_url   varchar(255)
 is_profile_public    bool         [not null, default: true]
 created_at           datetime     [not null]
 updated_at           datetime     [not null]
}

Table badge {
 id          bigint       [pk, increment]
 code        varchar(50)  [not null, unique]
 name        varchar(100) [not null]
 description varchar(255)
 icon_url    varchar(255)
 category    varchar(30)
 level       int
 created_at  datetime     [not null]
 updated_at  datetime     [not null]
}

Table user_badge {
 user_id     bigint   [not null, ref: > user.id]
 badge_id    bigint   [not null, ref: > badge.id]
 obtained_at datetime [not null]
 is_pinned   bool     [not null, default: false]

 indexes {
   (user_id, badge_id) [pk]
 }
}

[zhy2on]

2차 설계

dbml

// ------------------------------------------
//  유저 및 권한 (User & Auth)
// ------------------------------------------

Table user {
 id bigint [pk, increment]
 role_id bigint [not null, note: "FK: role 테이블"]
 status_id bigint [not null, note: "FK: user_status 테이블"]
 email varchar(100) [unique]
 password_hash varchar(255)
 nickname varchar(50)
 profile_image_url varchar(255)
 created_at datetime [default: `now()`]
 updated_at datetime
}

Table role {
 id bigint [pk, increment]
 code varchar(20) [unique, note: "'USER', 'ADMIN'"]
 name varchar(50)
}

Table user_status {
 id bigint [pk, increment]
 code varchar(20) [unique, note: "'ACTIVE', 'DELETED'"]
 name varchar(50)
 description varchar(100)
}

// ------------------------------------------
//  장소 (Spot)
// ------------------------------------------

Table spot {
 id bigint [pk, increment]
 kakao_place_id varchar(50) [unique, not null, note: "카카오맵 고유 ID"]
 name varchar(100) [not null]
 address varchar(255)
 category varchar(50)
 lat decimal(10,8) [not null]
 lng decimal(11,8) [not null]
 place_url varchar(255)
 thumbnail_url varchar(255)
 review_count int [default: 0]
 average_rating decimal(3,2) [default: 0]
 created_at datetime [default: `now()`]
}

// ------------------------------------------
// ✈️ 여행 계획 (Trip Plan)
// ------------------------------------------

Table trip {
 id bigint [pk, increment]
 user_id bigint [not null]
 trip_status_id bigint [not null, note: "FK: trip_status 테이블"]
 title varchar(100) [not null]
 start_date date
 end_date date
 visibility varchar(20) [not null, default: 'PRIVATE', note: "'PUBLIC' 또는 'PRIVATE'"]
 created_at datetime [default: `now()`]
 updated_at datetime
}

// [신규] 여행 상태 마스터 테이블
Table trip_status {
 id bigint [pk, increment]
 code varchar(20) [unique, not null, note: "'DRAFT', 'PLANNED', 'COMPLETED'"]
 name varchar(50) [not null, note: "작성 중, 계획 완료, 여행 완료"]
 description varchar(100)
}

Table trip_item {
 id bigint [pk, increment]
 trip_id bigint [not null]
 spot_id bigint [not null]
 day_number int [not null, note: "1일차, 2일차..."]
 order_index int [not null, note: "방문 순서"]
 memo text
}

// ------------------------------------------
// 📖 여행 로그 (Trip Log) - [신규]
// ------------------------------------------

Table trip_log {
 id bigint [pk, increment]
 user_id bigint [not null]
 original_trip_id bigint [note: "어떤 여행 계획을 바탕으로 작성되었는지 (선택)"]
 title varchar(100) [not null]
 content text [not null]
 visibility varchar(20) [not null, default: 'PRIVATE', note: "'PUBLIC' 또는 'PRIVATE'"]
 created_at datetime [default: `now()`]
 updated_at datetime
}

// ------------------------------------------
//  리뷰 (Review)
// ------------------------------------------

Table review {
 id bigint [pk, increment]
 spot_id bigint [not null]
 user_id bigint [not null]
 content text [not null]
 rating int [not null, note: "1 ~ 5 점"]
 image_url varchar(255)
 created_at datetime [default: `now()`]
}

// ------------------------------------------
//  스크랩 (Scrap) - [신규, 다형적 구조]
// ------------------------------------------

Table scrap {
 id bigint [pk, increment]
 user_id bigint [not null, note: "스크랩한 유저"]
 scrappable_id bigint [not null, note: "스크랩된 대상의 ID (spot.id, trip.id, trip_log.id 등)"]
 scrappable_type varchar(50) [not null, note: "스크랩된 대상의 타입 ('SPOT', 'TRIP', 'TRIP_LOG')"]
 created_at datetime [default: `now()`]

 indexes {
   (user_id, scrappable_id, scrappable_type) [unique]
 }
}

// ------------------------------------------
//  관계 정의 (Relationships)
// ------------------------------------------

// User & Auth
Ref: user.role_id > role.id
Ref: user.status_id > user_status.id

// Trip & Trip Item
Ref: trip.user_id > user.id [delete: cascade]
Ref: trip.trip_status_id > trip_status.id
Ref: trip_item.trip_id > trip.id [delete: cascade]
Ref: trip_item.spot_id > spot.id

// Trip Log
Ref: trip_log.user_id > user.id [delete: cascade]
Ref: trip_log.original_trip_id > trip.id [delete: set null]

// Review
Ref: review.user_id > user.id [delete: cascade]
Ref: review.spot_id > spot.id [delete: cascade]

// Scrap (논리적 관계)
Ref: scrap.user_id > user.id [delete: cascade]

setup-sql

-- =================================================================
-- 데이터베이스 설정
-- =================================================================
DROP DATABASE IF EXISTS trit_dev;
CREATE DATABASE trit_dev DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE trit_dev;

-- =================================================================
-- 1. 사용자 및 권한
-- =================================================================

-- 역할 (Role)
CREATE TABLE `role` (
 `id`   BIGINT NOT NULL AUTO_INCREMENT,
 `code` VARCHAR(20) NOT NULL UNIQUE, -- 'USER', 'ADMIN'
 `name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 사용자 상태 (User Status)
CREATE TABLE `user_status` (
 `id`          BIGINT NOT NULL AUTO_INCREMENT,
 `code`        VARCHAR(20) NOT NULL UNIQUE, -- 'ACTIVE', 'DELETED'
 `name`        VARCHAR(50) NOT NULL,
 `description` VARCHAR(100),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 사용자 (User)
CREATE TABLE `user` (
 `id`                BIGINT NOT NULL AUTO_INCREMENT,
 `role_id`           BIGINT NOT NULL,
 `status_id`         BIGINT NOT NULL,
 `email`             VARCHAR(100) NOT NULL UNIQUE,
 `password_hash`     VARCHAR(255) NOT NULL,
 `nickname`          VARCHAR(50) NOT NULL,
 `profile_image_url` VARCHAR(255),
 `created_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `idx_user_email` (`email`),
 CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
 CONSTRAINT `fk_user_status` FOREIGN KEY (`status_id`) REFERENCES `user_status` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 사용자 프로필 (User Profile)
CREATE TABLE `user_profile` (
 `user_id`                 BIGINT NOT NULL,
 `bio`                     VARCHAR(255),
 `intro`                   TEXT,
 `home_region_id`          BIGINT,
 `travel_style_summary`    TEXT,
 `travel_style_id`         BIGINT,
 `profile_banner_url`      VARCHAR(255),
 `is_profile_public`       BOOLEAN NOT NULL DEFAULT TRUE,
 `friends_count`           INT NOT NULL DEFAULT 0, -- Added friends_count column
 `created_at`              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at`              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`user_id`),
 CONSTRAINT `fk_user_profile_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 친구 관계 (Friendship)
CREATE TABLE `friendship` (
 `user_id_a`  BIGINT NOT NULL,
 `user_id_b`  BIGINT NOT NULL,
 `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`user_id_a`, `user_id_b`),
 CONSTRAINT `fk_friendship_user_a` FOREIGN KEY (`user_id_a`) REFERENCES `user` (`id`) ON DELETE CASCADE,
 CONSTRAINT `fk_friendship_user_b` FOREIGN KEY (`user_id_b`) REFERENCES `user` (`id`) ON DELETE CASCADE,
 CONSTRAINT `chk_friendship_order` CHECK (`user_id_a` < `user_id_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 여행 스타일 (Travel Style)
CREATE TABLE `travel_style` (
 `id`   BIGINT NOT NULL AUTO_INCREMENT,
 `code` VARCHAR(20) NOT NULL UNIQUE, -- 'CAFE_LOVER'
 `name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 배지 (Badge)
CREATE TABLE `badge` (
 `id`          BIGINT NOT NULL AUTO_INCREMENT,
 `code`        VARCHAR(50) NOT NULL UNIQUE,
 `name`        VARCHAR(100) NOT NULL,
 `description` VARCHAR(255),
 `icon_url`    VARCHAR(255),
 `category`    VARCHAR(30),
 `level`       INT,
 `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 사용자 배지 (User Badge)
CREATE TABLE `user_badge` (
 `user_id`     BIGINT NOT NULL,
 `badge_id`    BIGINT NOT NULL,
 `obtained_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `is_pinned`   BOOLEAN NOT NULL DEFAULT FALSE,
 PRIMARY KEY (`user_id`, `badge_id`),
 CONSTRAINT `fk_user_badge_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
 CONSTRAINT `fk_user_badge_badge` FOREIGN KEY (`badge_id`) REFERENCES `badge` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==========================================
-- 🏢 2. 장소 (Spot Domain)
-- ==========================================

-- 장소 (Spot)
CREATE TABLE `spot` (
 `id`              BIGINT NOT NULL AUTO_INCREMENT,
 `kakao_place_id`  VARCHAR(50) NOT NULL,
 `name`            VARCHAR(100) NOT NULL,
 `address`         VARCHAR(255),
 `category`        VARCHAR(50),
 `lat`             DECIMAL(10, 8) NOT NULL,
 `lng`             DECIMAL(11, 8) NOT NULL,
 `place_url`       VARCHAR(255),
 `thumbnail_url`   VARCHAR(255),
 `review_count`    INT DEFAULT 0,
 `average_rating`  DECIMAL(3, 2) DEFAULT 0.0,
 `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_kakao_place_id` (`kakao_place_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==========================================
-- ✈️ 3. 여행 계획 (Trip Domain)
-- ==========================================

-- 여행 상태 마스터 (Trip Status)
CREATE TABLE `trip_status` (
 `id`          BIGINT NOT NULL AUTO_INCREMENT,
 `code`        VARCHAR(20) NOT NULL UNIQUE, -- 'DRAFT', 'PLANNED', 'COMPLETED'
 `name`        VARCHAR(50) NOT NULL,        -- '작성 중', '계획 완료', '여행 완료'
 `description` VARCHAR(100),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 여행 (Trip)
CREATE TABLE `trip` (
 `id`             BIGINT NOT NULL AUTO_INCREMENT,
 `user_id`        BIGINT NOT NULL,
 `trip_status_id` BIGINT NOT NULL,
 `title`          VARCHAR(100) NOT NULL,
 `start_date`     DATE,
 `end_date`       DATE,
 `visibility`     VARCHAR(20) NOT NULL DEFAULT 'PRIVATE' COMMENT "'PUBLIC' 또는 'PRIVATE'",
 `created_at`     DATETIME DEFAULT NOW(),
 `updated_at`     DATETIME,
 PRIMARY KEY (`id`),
 CONSTRAINT `fk_trip_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
 CONSTRAINT `fk_trip_status` FOREIGN KEY (`trip_status_id`) REFERENCES `trip_status` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 여행 상세 아이템 (Trip Item)
CREATE TABLE `trip_item` (
 `id`          BIGINT NOT NULL AUTO_INCREMENT,
 `trip_id`     BIGINT NOT NULL,
 `spot_id`     BIGINT NOT NULL,
 `day_number`  INT NOT NULL,              -- 1일차, 2일차...
 `order_index` INT NOT NULL,              -- 방문 순서
 `memo`        TEXT,
 `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),

 INDEX `idx_trip_day_order` (`trip_id`, `day_number`, `order_index`),
 UNIQUE KEY `uk_trip_day_order` (`trip_id`, `day_number`, `order_index`),

 CONSTRAINT `fk_item_trip` FOREIGN KEY (`trip_id`) REFERENCES `trip` (`id`) ON DELETE CASCADE,
 CONSTRAINT `fk_item_spot` FOREIGN KEY (`spot_id`) REFERENCES `spot` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =================================================================
-- 8. 시드 데이터 (초기 데이터)
-- =================================================================

-- 기본 역할
INSERT INTO `role` (id, code, name) VALUES
(1, 'USER',  '일반 사용자'),
(2, 'ADMIN', '관리자');

-- 기본 사용자 상태
INSERT INTO `user_status` (id, code, name, description) VALUES
(1, 'ACTIVE',  '활성', '일반 활성 사용자'),
(2, 'DELETED', '탈퇴', '탈퇴 또는 비활성 사용자');

-- 기본 Trip Status
INSERT INTO `trip_status` (id, code, name, description) VALUES
(1, 'DRAFT',     '작성 중',   '작성 중인 여행 계획'),
(2, 'PLANNED',   '계획 완료', '여행 계획 완료 상태'),
(3, 'COMPLETED', '여행 완료', '여행이 실제로 완료된 상태');

-- 여행 스타일 (최소 데이터)
INSERT INTO `travel_style` (id, code, name) VALUES
(1, 'CAFE_HOPPER', '카페 탐방가'),
(2, 'ADVENTURER', '모험가'),
(3, 'FOODIE', '미식가');

-- 배지 (최소 데이터)
INSERT INTO `badge` (id, code, name, description, icon_url, category, level) VALUES
(1, 'FIRST_TRIP', '첫 여행', '첫 여행 계획 완료', NULL, 'TRIP', 1),
(2, 'PHOTO_MASTER', '사진 장인', '사진 100장 업로드', NULL, 'PHOTO', 2);

-- 더미 사용자 (사용자 제공 계정)
INSERT INTO `user` (id, role_id, status_id, email, password_hash, nickname) VALUES
(1, 1, 1, 'hi@hi.hi', '$2a$10$uyMhCnceQ3ORnCNk.wvfOeZt3EqtJNKzlD0OYZ.veOJYa2SgPFszu', '테스트 계정1'),
(2, 1, 1, 'hi1@hi.hi', '$2a$10$uB2MdvuEvR460eGyC/H8w.j3ghCsBzLFRN7FOXpbG0vjCTx6o9n2K', '테스트 계정2'),
(3, 1, 1, 'hi2@hi.hi', '$2a$10$1cA1Jc5KIeCCfBGAPKBbH.pt5dDiSKTgRX/j8aixQa1Pk8xB2Dreq', '테스트 계정3');

-- 더미 사용자 프로필 (사용자 제공 계정)
INSERT INTO `user_profile` (user_id, bio, intro, friends_count) VALUES
(1, '안녕하세요! 테스트 계정1입니다.', '테스트 계정 1의 자기소개', 2),
(2, '안녕하세요! 테스트 계정2입니다.', '테스트 계정 2의 자기소개', 2),
(3, '안녕하세요! 테스트 계정3입니다.', '테스트 계정 3의 자기소개', 2);

-- 더미 친구 관계
INSERT INTO `friendship` (user_id_a, user_id_b) VALUES (1, 2);
INSERT INTO `friendship` (user_id_a, user_id_b) VALUES (1, 3);
INSERT INTO `friendship` (user_id_a, user_id_b) VALUES (2, 3);
INSERT INTO `spot` (kakao_place_id, name, address, category, lat, lng, place_url) VALUES
('27392064', '아쿠아플라넷 제주', '제주 서귀포시 성산읍 섭지코지로 95', '테마파크', 33.43041, 126.9242, 'http://place.map.kakao.com/27392064'),
('8035229', '성산일출봉', '제주 서귀포시 성산읍 성산리 1', '명소', 33.45806, 126.9425, 'http://place.map.kakao.com/8035229'),
('7948366', '카멜리아힐', '제주 서귀포시 안덕면 병악로 166', '공원', 33.2989, 126.3939, 'http://place.map.kakao.com/7948366');

-- 더미 여행 계획
INSERT INTO `trip` (user_id, trip_status_id, title, start_date, end_date, visibility) VALUES
(1, 2, '제주도 2박 3일 여행', '2024-03-10', '2024-03-12', 'PUBLIC');

-- 더미 여행 아이템
INSERT INTO `trip_item` (trip_id, spot_id, day_number, order_index, memo) VALUES
(1, 1, 1, 1, '오전 10시 도착 예정'),
(1, 2, 2, 1, '일출 보러 가기'),
(1, 3, 2, 2, '점심 먹고 산책');

[zhy2on]

3차 설계

dbml

// ------------------------------------------
//  유저 및 권한 (User & Auth)
// ------------------------------------------

Table role {
 id   bigint [pk, increment]
 code varchar(20) [not null, unique, note: "'USER', 'ADMIN'"]
 name varchar(50) [not null]
}

Table user_status {
 id          bigint [pk, increment]
 code        varchar(20) [not null, unique, note: "'ACTIVE', 'DELETED'"]
 name        varchar(50) [not null]
 description varchar(100)
}

Table user {
 id                bigint [pk, increment]
 role_id           bigint [not null, note: "FK: role 테이블"]
 status_id         bigint [not null, note: "FK: user_status 테이블"]
 email             varchar(100) [not null, unique]
 password_hash     varchar(255) [not null]
 nickname          varchar(50) [not null]
 profile_image_url varchar(255)
 created_at        datetime [not null, default: `now()`]
 updated_at        datetime [not null, default: `now()`]
}

Table user_profile {
 user_id              bigint [pk, note: "FK: user.id (1:1)"]
 bio                  varchar(255)
 intro                text
 home_region_id       bigint
 travel_style_summary text
 travel_style_id        bigint
 profile_banner_url   varchar(255)
 is_profile_public    boolean [not null, default: true]
 friends_count        int [not null, default: 0]
 created_at           datetime [not null, default: `now()`]
 updated_at           datetime [not null, default: `now()`]
}

Table friendship {
 user_id_a  bigint [not null, note: "항상 user_id_a < user_id_b"]
 user_id_b  bigint [not null]
 created_at datetime [not null, default: `now()`]

 indexes {
   (user_id_a, user_id_b) [pk]
 }
}

// ------------------------------------------
// 🏢 장소 (Spot)
// ------------------------------------------

Table spot {
 id             bigint [pk, increment]
 kakao_place_id varchar(50) [unique, not null, note: "카카오맵 고유 ID"]
 name           varchar(100) [not null]
 address        varchar(255)
 category       varchar(50)
 lat            decimal(10,8) [not null]
 lng            decimal(11,8) [not null]
 place_url      varchar(255)
 thumbnail_url  varchar(255)
 review_count   int [default: 0]
 average_rating decimal(3,2) [default: 0]
 created_at     datetime [default: `now()`]
}

// ------------------------------------------
// ✈️ 여행 계획 (Trip Domain)
// ------------------------------------------

Table trip_status {
 id          bigint [pk, increment]
 code        varchar(20) [unique, not null, note: "'DRAFT', 'PLANNED', 'COMPLETED'"]
 name        varchar(50) [not null, note: "작성 중, 계획 완료, 여행 완료"]
 description varchar(100)
}

Table trip {
 id             bigint [pk, increment]
 user_id        bigint [not null, note: "FK: user 테이블"]
 trip_status_id bigint [not null, note: "FK: trip_status 테이블"]
 title          varchar(100) [not null]
 start_date     date
 end_date       date
 visibility     varchar(20) [not null, default: 'PRIVATE', note: "'PUBLIC' 또는 'PRIVATE'"]
 created_at     datetime [default: `now()`]
 updated_at     datetime
}

Table trip_item {
 id          bigint [pk, increment]
 trip_id     bigint [not null]
 spot_id     bigint [not null]
 day_number  int [not null, note: "1일차, 2일차..."]
 order_index int [not null, note: "방문 순서"]
 memo        text
 created_at  datetime [not null, default: `now()`]

 indexes {
   (trip_id, day_number, order_index) [unique, name: 'uk_trip_day_order']
 }
}

// ------------------------------------------
// ✍️ 여행 기록 (Trip Log Domain)
// ------------------------------------------

Table trip_log {
 id               bigint [pk, increment]
 trip_id          bigint [not null, note: "FK: trip.id, Trip과 1:1"]
 title            varchar(255) [not null]
 content          text [note: "마크다운 형식 본문. 이미지는 {{img_key}} 형태의 참조 키 사용"]
 location_summary varchar(255) [note: "예: 서울시 / 서울시 강남구 / 서울시 강남구 역삼동"]
 like_count       int [not null, default: 0, note: "좋아요 수 캐시"]
 comment_count    int [not null, default: 0, note: "댓글 수 캐시"]
 created_at       datetime [not null, default: `now()`]
 updated_at       datetime [not null, default: `now()`]

 indexes {
   trip_id [unique, name: 'uk_trip_log_trip_id']
 }
}

Table log_like {
 user_id    bigint [not null]
 log_id     bigint [not null, note: "FK: trip_log.id"]
 created_at datetime [not null, default: `now()`]

 indexes {
   (user_id, log_id) [pk]
 }
}

Table log_image {
 id            bigint [pk, increment]
 log_id        bigint [note: "FK: trip_log.id"]
 user_id       bigint [not null]
 image_url     varchar(255) [not null]
 order_index   int [not null, note: "인스타 피드 뷰용 순서"] 
 image_ref_key varchar(50) [not null, note: "블로그 뷰 본문 매핑용 고유 키 (예: img_abc1)"]
 created_at    datetime [not null, default: `now()`]
}

Table log_comment {
 id         bigint [pk, increment]
 log_id     bigint [not null, note: "FK: trip_log.id"]
 user_id    bigint [not null]
 content    text [not null]
 created_at datetime [not null, default: `now()`]
 updated_at datetime [not null, default: `now()`]
}

// ------------------------------------------
//  관계 정의 (Relationships)
// ------------------------------------------

// User & Auth
Ref: user.role_id > role.id
Ref: user.status_id > user_status.id
Ref: user_profile.user_id > user.id [delete: cascade]

// Friendship
Ref: friendship.user_id_a > user.id [delete: cascade]
Ref: friendship.user_id_b > user.id [delete: cascade]

// Trip & Trip Item
Ref: trip.user_id > user.id [delete: cascade]
Ref: trip.trip_status_id > trip_status.id
Ref: trip_item.trip_id > trip.id [delete: cascade]
Ref: trip_item.spot_id > spot.id

// Trip Log Domain
Ref: trip_log.trip_id > trip.id [delete: cascade]
Ref: log_like.user_id > user.id [delete: cascade]
Ref: log_like.log_id > trip_log.id [delete: cascade]
Ref: log_image.user_id > user.id [delete: cascade]
Ref: log_image.log_id > trip_log.id [delete: set null]
Ref: log_comment.user_id > user.id [delete: cascade]
Ref: log_comment.log_id > trip_log.id [delete: cascade]

setup-sql

-- =================================================================
-- 데이터베이스 설정
-- =================================================================
DROP DATABASE IF EXISTS trit_dev;
CREATE DATABASE trit_dev DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE trit_dev;

-- =================================================================
-- 1. 사용자 및 권한
-- =================================================================

-- 역할 (Role)
CREATE TABLE `role` (
 `id`   BIGINT NOT NULL AUTO_INCREMENT,
 `code` VARCHAR(20) NOT NULL UNIQUE, -- 'USER', 'ADMIN'
 `name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 사용자 상태 (User Status)
CREATE TABLE `user_status` (
 `id`          BIGINT NOT NULL AUTO_INCREMENT,
 `code`        VARCHAR(20) NOT NULL UNIQUE, -- 'ACTIVE', 'DELETED'
 `name`        VARCHAR(50) NOT NULL,
 `description` VARCHAR(100),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 사용자 (User)
CREATE TABLE `user` (
 `id`                BIGINT NOT NULL AUTO_INCREMENT,
 `role_id`           BIGINT NOT NULL,
 `status_id`         BIGINT NOT NULL,
 `email`             VARCHAR(100) NOT NULL UNIQUE,
 `password_hash`     VARCHAR(255) NOT NULL,
 `nickname`          VARCHAR(50) NOT NULL,
 `profile_image_url` VARCHAR(255),
 `created_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `idx_user_email` (`email`),
 CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
 CONSTRAINT `fk_user_status` FOREIGN KEY (`status_id`) REFERENCES `user_status` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 사용자 프로필 (User Profile)
CREATE TABLE `user_profile` (
 `user_id`                 BIGINT NOT NULL,
 `bio`                     VARCHAR(255),
 `intro`                   TEXT,
 `home_region_id`          BIGINT,
 `travel_style_summary`    TEXT,
 `travel_style_id`         BIGINT,
 `profile_banner_url`      VARCHAR(255),
 `is_profile_public`       BOOLEAN NOT NULL DEFAULT TRUE,
 `friends_count`           INT NOT NULL DEFAULT 0, -- Added friends_count column
 `created_at`              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at`              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`user_id`),
 CONSTRAINT `fk_user_profile_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 친구 관계 (Friendship)
CREATE TABLE `friendship` (
 `user_id_a`  BIGINT NOT NULL,
 `user_id_b`  BIGINT NOT NULL,
 `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`user_id_a`, `user_id_b`),
 CONSTRAINT `fk_friendship_user_a` FOREIGN KEY (`user_id_a`) REFERENCES `user` (`id`) ON DELETE CASCADE,
 CONSTRAINT `fk_friendship_user_b` FOREIGN KEY (`user_id_b`) REFERENCES `user` (`id`) ON DELETE CASCADE,
 CONSTRAINT `chk_friendship_order` CHECK (`user_id_a` < `user_id_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==========================================
-- 🏢 2. 장소 (Spot Domain)
-- ==========================================

-- 장소 (Spot)
CREATE TABLE `spot` (
 `id`              BIGINT NOT NULL AUTO_INCREMENT,
 `kakao_place_id`  VARCHAR(50) NOT NULL,
 `name`            VARCHAR(100) NOT NULL,
 `address`         VARCHAR(255),
 `category`        VARCHAR(50),
 `lat`             DECIMAL(10, 8) NOT NULL,
 `lng`             DECIMAL(11, 8) NOT NULL,
 `place_url`       VARCHAR(255),
 `thumbnail_url`   VARCHAR(255),
 `review_count`    INT DEFAULT 0,
 `average_rating`  DECIMAL(3, 2) DEFAULT 0.0,
 `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_kakao_place_id` (`kakao_place_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==========================================
-- ✈️ 3. 여행 계획 (Trip Domain)
-- ==========================================

-- 여행 상태 마스터 (Trip Status)
CREATE TABLE `trip_status` (
 `id`          BIGINT NOT NULL AUTO_INCREMENT,
 `code`        VARCHAR(20) NOT NULL UNIQUE, -- 'DRAFT', 'PLANNED', 'COMPLETED'
 `name`        VARCHAR(50) NOT NULL,        -- '작성 중', '계획 완료', '여행 완료'
 `description` VARCHAR(100),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 여행 (Trip)
CREATE TABLE `trip` (
 `id`             BIGINT NOT NULL AUTO_INCREMENT,
 `user_id`        BIGINT NOT NULL,
 `trip_status_id` BIGINT NOT NULL,
 `title`          VARCHAR(100) NOT NULL,
 `start_date`     DATE,
 `end_date`       DATE,
 `visibility`     VARCHAR(20) NOT NULL DEFAULT 'PRIVATE' COMMENT "'PUBLIC' 또는 'PRIVATE'",
 `created_at`     DATETIME DEFAULT NOW(),
 `updated_at`     DATETIME,
 PRIMARY KEY (`id`),
 CONSTRAINT `fk_trip_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
 CONSTRAINT `fk_trip_status` FOREIGN KEY (`trip_status_id`) REFERENCES `trip_status` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 여행 상세 아이템 (Trip Item)
CREATE TABLE `trip_item` (
 `id`          BIGINT NOT NULL AUTO_INCREMENT,
 `trip_id`     BIGINT NOT NULL,
 `spot_id`     BIGINT NOT NULL,
 `day_number`  INT NOT NULL,              -- 1일차, 2일차...
 `order_index` INT NOT NULL,              -- 방문 순서
 `memo`        TEXT,
 `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),

 INDEX `idx_trip_day_order` (`trip_id`, `day_number`, `order_index`),
 UNIQUE KEY `uk_trip_day_order` (`trip_id`, `day_number`, `order_index`),

 CONSTRAINT `fk_item_trip` FOREIGN KEY (`trip_id`) REFERENCES `trip` (`id`) ON DELETE CASCADE,
 CONSTRAINT `fk_item_spot` FOREIGN KEY (`spot_id`) REFERENCES `spot` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ==========================================
-- ✍️ 4. 여행 기록 (Trip Log Domain)
-- ==========================================

-- 여행 기록 (Trip Log)
CREATE TABLE `trip_log` (
 `id`               BIGINT NOT NULL AUTO_INCREMENT,
 `trip_id`          BIGINT NOT NULL,
 `title`            VARCHAR(255) NOT NULL,
 `content`          TEXT COMMENT '마크다운 형식 본문. 이미지는 {{img_key}} 형태의 참조 키 사용',
 `location_summary` VARCHAR(255) COMMENT '장소 요약 (예: 서울시 or 서울시 강남구 or 서울시 강남구 역삼동)',
 `like_count`       INT NOT NULL DEFAULT 0 COMMENT '좋아요 수',
 `comment_count`    INT NOT NULL DEFAULT 0 COMMENT '댓글 수',
 `created_at`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_trip_log_trip_id` (`trip_id`), -- trip과 1:1 관계를 위해 UNIQUE 제약조건 추가
 CONSTRAINT `fk_trip_log_trip` FOREIGN KEY (`trip_id`) REFERENCES `trip` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 여행 기록 좋아요 (Log Like)
CREATE TABLE `log_like` (
   `user_id`    BIGINT NOT NULL,
   `log_id`     BIGINT NOT NULL,
   `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`user_id`, `log_id`), -- 한 사용자가 한 게시물에 좋아요를 한 번만 누를 수 있도록 복합키 설정
   CONSTRAINT `fk_log_like_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
   CONSTRAINT `fk_log_like_log` FOREIGN KEY (`log_id`) REFERENCES `trip_log` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 여행 기록 이미지 (Log Image)
CREATE TABLE `log_image` (
   `id`            BIGINT NOT NULL AUTO_INCREMENT,
   `log_id`        BIGINT, -- 로그 저장 전 임시 업로드 상태일 수 있으므로 NULL 허용
   `user_id`       BIGINT NOT NULL,
   `image_url`     VARCHAR(255) NOT NULL,
   `order_index`   INT NOT NULL COMMENT '인스타 피드 뷰에서의 표시 순서',
   `image_ref_key` VARCHAR(50) NOT NULL COMMENT '본문 {{key}}와 매핑되는 고유 키 (프론트 생성)',
   `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   CONSTRAINT `fk_log_image_log` FOREIGN KEY (`log_id`) REFERENCES `trip_log` (`id`) ON DELETE SET NULL,
   CONSTRAINT `fk_log_image_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 여행 기록 댓글 (Log Comment)
CREATE TABLE `log_comment` (
 `id`          BIGINT NOT NULL AUTO_INCREMENT,
 `log_id`      BIGINT NOT NULL,
 `user_id`     BIGINT NOT NULL,
 `content`     TEXT NOT NULL,
 `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 CONSTRAINT `fk_comment_log` FOREIGN KEY (`log_id`) REFERENCES `trip_log` (`id`) ON DELETE CASCADE,
 CONSTRAINT `fk_comment_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =================================================================
-- 8. 시드 데이터 (초기 데이터)
-- =================================================================

-- 기본 역할
INSERT INTO `role` (id, code, name) VALUES
(1, 'USER',  '일반 사용자'),
(2, 'ADMIN', '관리자');

-- 기본 사용자 상태
INSERT INTO `user_status` (id, code, name, description) VALUES
(1, 'ACTIVE',  '활성', '일반 활성 사용자'),
(2, 'DELETED', '탈퇴', '탈퇴 또는 비활성 사용자');

-- 기본 Trip Status
INSERT INTO `trip_status` (id, code, name, description) VALUES
(1, 'DRAFT',     '작성 중',   '작성 중인 여행 계획'),
(2, 'PLANNED',   '계획 완료', '여행 계획 완료 상태'),
(3, 'COMPLETED', '여행 완료', '여행이 실제로 완료된 상태');

-- 더미 사용자 (사용자 제공 계정)
INSERT INTO `user` (id, role_id, status_id, email, password_hash, nickname, profile_image_url) VALUES
(1, 1, 1, 'hi@hi.hi', '$2a$10$uyMhCnceQ3ORnCNk.wvfOeZt3EqtJNKzlD0OYZ.veOJYa2SgPFszu', '테스트 계정1', 'https://dh.aks.ac.kr/Edu/wiki/images/b/b7/%ED%95%91%EA%B5%AC.jpg'),
(2, 1, 1, 'hi1@hi.hi', '$2a$10$uB2MdvuEvR460eGyC/H8w.j3ghCsBzLFRN7FOXpbG0vjCTx6o9n2K', '테스트 계정2', 'https://i.namu.wiki/i/w4Vkm_EuVM_FV8-VDjLVJPWazkrT1YnkSFLVASCh4YM8QUebla94cM8j42z8hQPzJQCyVcDlm71EeKgPzLyMfg.webp'),
(3, 1, 1, 'hi2@hi.hi', '$2a$10$1cA1Jc5KIeCCfBGAPKBbH.pt5dDiSKTgRX/j8aixQa1Pk8xB2Dreq', '테스트 계정3', 'https://i.namu.wiki/i/2Vk0cSYgfHODE4-SrICeOk7qaQCz09wqivf27QgdZawQ5lg3YKo-XjL9BvyvkBeQ-JGE_dV83cYnsd5urD65aw.webp');

-- 더미 사용자 프로필 (사용자 제공 계정)
INSERT INTO `user_profile` (user_id, bio, intro, friends_count) VALUES
(1, '안녕하세요! 테스트 계정1입니다.', '테스트 계정 1의 자기소개', 2),
(2, '안녕하세요! 테스트 계정2입니다.', '테스트 계정 2의 자기소개', 2),
(3, '안녕하세요! 테스트 계정3입니다.', '테스트 계정 3의 자기소개', 2);

-- 더미 친구 관계
INSERT INTO `friendship` (user_id_a, user_id_b) VALUES (1, 2);
INSERT INTO `friendship` (user_id_a, user_id_b) VALUES (1, 3);
INSERT INTO `friendship` (user_id_a, user_id_b) VALUES (2, 3);

-- 더미 장소
INSERT INTO `spot` (kakao_place_id, name, address, category, lat, lng, place_url) VALUES
('27392064', '아쿠아플라넷 제주', '제주 서귀포시 성산읍 섭지코지로 95', '테마파크', 33.43041, 126.9242, 'http://place.map.kakao.com/27392064'),
('8035229', '성산일출봉', '제주 서귀포시 성산읍 성산리 1', '명소', 33.45806, 126.9425, 'http://place.map.kakao.com/8035229'),
('7948366', '카멜리아힐', '제주 서귀포시 안덕면 병악로 166', '공원', 33.2989, 126.3939, 'http://place.map.kakao.com/7948366');

-- 더미 여행 계획
INSERT INTO `trip` (id, user_id, trip_status_id, title, start_date, end_date, visibility) VALUES
(1, 1, 2, '제주도 2박 3일 여행', '2024-03-10', '2024-03-12', 'PUBLIC');

-- 더미 여행 아이템
INSERT INTO `trip_item` (trip_id, spot_id, day_number, order_index, memo) VALUES
(1, 1, 1, 1, '오전 10시 도착 예정'),
(1, 2, 2, 1, '일출 보러 가기'),
(1, 3, 2, 2, '점심 먹고 산책');

-- 더미 여행 로그 (이미지 플레이스홀더 적용: 고유 키 방식)
-- {{img_a1b2}} 처럼 프론트가 생성한 고유 키를 사용
INSERT INTO `trip_log` (id, trip_id, title, content, location_summary, like_count, comment_count) VALUES
(1, 1, '제주도 2박 3일 여행기',
'이번 제주도 여행의 시작은 아쿠아플라넷이었습니다.\n\n{{img_key_1}}\n\n수족관 규모가 정말 커서 놀랐어요. 상어도 보고 가오리도 봤네요.\n그 다음날 아침에는 일출을 보러 갔습니다.\n\n{{img_key_2}}\n\n날씨가 좋아서 해 뜨는 게 아주 잘 보였습니다. 정말 잊지 못할 추억이에요.',
'제주 서귀포시', 0, 0);

-- 더미 여행 기록 이미지 (image_ref_key 포함)
-- order_index: 피드 뷰 정렬용
-- image_ref_key: 블로그 뷰 본문 매핑용
INSERT INTO `log_image` (log_id, user_id, image_url, order_index, image_ref_key) VALUES
(1, 1, 'https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcQvytJIR9t4OyaATlDacK5xwsF7rd3yiMgWGQ&s', 0, 'img_key_1'),
(1, 1, 'https://img1.daumcdn.net/thumb/R1280x0.fjpg/?fname=http://t1.daumcdn.net/brunch/service/user/1lcG/image/ATFUCOF_RrI4V7UWgZCF_g139sY.jpg', 1, 'img_key_2');

-- 더미 로그 댓글
INSERT INTO `log_comment` (log_id, user_id, content) VALUES
(1, 2, '와 여행 너무 좋아보여요! 사진 멋지네요.'),
(1, 3, '다음엔 저도 같이 가요 ㅎㅎ');

-- 더미 로그 좋아요
INSERT INTO `log_like` (user_id, log_id) VALUES
(2, 1),
(3, 1);

[zhy2on]

4차 설계

dbml

Project trit_dev {
 database_type: "MySQL"
}

Table role {
 id bigint [pk, increment]
 code varchar(20) [not null, unique]
 name varchar(50) [not null]

 Indexes {
   (code) [unique, name: "code"]
 }
}

Table spot {
 id bigint [pk, increment]
 kakao_place_id varchar(50) [not null, unique, note: "uk_kakao_place_id"]
 name varchar(100) [not null]
 address varchar(255)
 category varchar(50)
 lat decimal(10,8) [not null]
 lng decimal(11,8) [not null]
 place_url varchar(255)
 thumbnail_url varchar(255)
 review_count int [default: 0]
 average_rating decimal(3,2) [default: 0.00]
 created_at datetime [not null, default: `CURRENT_TIMESTAMP`]

 Indexes {
   (kakao_place_id) [unique, name: "uk_kakao_place_id"]
 }
}

Table trip_status {
 id bigint [pk, increment]
 code varchar(20) [not null, unique]
 name varchar(50) [not null]
 description varchar(100)

 Indexes {
   (code) [unique, name: "code"]
 }
}

Table user_status {
 id bigint [pk, increment]
 code varchar(20) [not null, unique]
 name varchar(50) [not null]
 description varchar(100)

 Indexes {
   (code) [unique, name: "code"]
 }
}

Table user {
 id bigint [pk, increment]
 role_id bigint [not null]
 status_id bigint [not null]
 email varchar(100) [not null, unique]
 password_hash varchar(255) [not null]
 nickname varchar(50) [not null]
 profile_image_url varchar(255)
 created_at datetime [not null, default: `CURRENT_TIMESTAMP`]
 updated_at datetime [not null, default: `CURRENT_TIMESTAMP`, note: "ON UPDATE CURRENT_TIMESTAMP"]

 Indexes {
   (email) [unique, name: "email"]
   (email) [name: "idx_user_email"]
   (role_id) [name: "fk_user_role"]
   (status_id) [name: "fk_user_status"]
 }
}

Table user_profile {
 user_id bigint [pk, not null]
 bio varchar(255)
 intro text
 home_region_id bigint
 travel_style_summary text
 travel_style_id bigint
 profile_banner_url varchar(255)
 is_profile_public boolean [not null, default: 1]
 friends_count int [not null, default: 0]
 created_at datetime [not null, default: `CURRENT_TIMESTAMP`]
 updated_at datetime [not null, default: `CURRENT_TIMESTAMP`, note: "ON UPDATE CURRENT_TIMESTAMP"]
}

Table friendship {
 user_id_a bigint [not null]
 user_id_b bigint [not null]
 created_at datetime [not null, default: `CURRENT_TIMESTAMP`]

 Indexes {
   (user_id_a, user_id_b) [pk]
   (user_id_b) [name: "fk_friendship_user_b"]
 }

 Note: "CHECK (user_id_a < user_id_b)"
}

Table trip {
 id bigint [pk, increment]
 user_id bigint [not null]
 trip_status_id bigint [not null]
 title varchar(100) [not null]
 start_date date
 end_date date
 visibility varchar(20) [not null, default: "PRIVATE", note: "'PUBLIC' 또는 'PRIVATE'"]
 location_summary varchar(255) [note: "장소 요약 (예: 서울시 or 서울시 강남구 or 서울시 강남구 역삼동)"]
 created_at datetime [default: `CURRENT_TIMESTAMP`]
 updated_at datetime

 Indexes {
   (user_id) [name: "fk_trip_user"]
   (trip_status_id) [name: "fk_trip_status"]
 }
}

Table trip_day_memo {
 trip_id bigint [not null]
 day_number int [not null]
 memo text
 created_at datetime [not null, default: `CURRENT_TIMESTAMP`]
 updated_at datetime [not null, default: `CURRENT_TIMESTAMP`, note: "ON UPDATE CURRENT_TIMESTAMP"]

 Indexes {
   (trip_id, day_number) [pk]
 }
}

Table trip_item {
 id bigint [pk, increment]
 trip_id bigint [not null]
 spot_id bigint [not null]
 day_number int [not null]
 order_index int [not null]
 created_at datetime [not null, default: `CURRENT_TIMESTAMP`]

 Indexes {
   (trip_id, day_number, order_index) [unique, name: "uk_trip_day_order"]
   (trip_id, day_number, order_index) [name: "idx_trip_day_order"]
   (spot_id) [name: "fk_item_spot"]
 }
}

Table trip_log {
 id bigint [pk, increment]
 trip_id bigint [not null, unique]
 title varchar(255) [not null]
 content text [note: "마크다운 형식 본문. 이미지는 {{img_key}} 형태의 참조 키 사용"]
 created_at datetime [not null, default: `CURRENT_TIMESTAMP`]
 updated_at datetime [not null, default: `CURRENT_TIMESTAMP`, note: "ON UPDATE CURRENT_TIMESTAMP"]

 Indexes {
   (trip_id) [unique, name: "uk_trip_log_trip_id"]
 }
}

Table log_comment {
 id bigint [pk, increment]
 log_id bigint [not null]
 user_id bigint [not null]
 content text [not null]
 created_at datetime [not null, default: `CURRENT_TIMESTAMP`]
 updated_at datetime [not null, default: `CURRENT_TIMESTAMP`, note: "ON UPDATE CURRENT_TIMESTAMP"]

 Indexes {
   (log_id) [name: "fk_comment_log"]
   (user_id) [name: "fk_comment_user"]
 }
}

Table log_image {
 id bigint [pk, increment]
 log_id bigint
 user_id bigint [not null]
 image_url varchar(255) [not null]
 order_index int [not null, note: "인스타 피드 뷰에서의 표시 순서"]
 image_ref_key varchar(50) [not null, note: "본문 {{key}}와 매핑되는 고유 키 (프론트 생성)"]
 created_at datetime [not null, default: `CURRENT_TIMESTAMP`]

 Indexes {
   (log_id) [name: "fk_log_image_log"]
   (user_id) [name: "fk_log_image_user"]
 }
}

Table log_like {
 user_id bigint [not null]
 log_id bigint [not null]
 created_at datetime [not null, default: `CURRENT_TIMESTAMP`]

 Indexes {
   (user_id, log_id) [pk]
   (log_id) [name: "fk_log_like_log"]
 }
}

/* =====================
  Relationships (FK)
  ===================== */

Ref: user.role_id > role.id
Ref: user.status_id > user_status.id

Ref: user_profile.user_id > user.id [delete: cascade]

Ref: friendship.user_id_a > user.id [delete: cascade]
Ref: friendship.user_id_b > user.id [delete: cascade]

Ref: trip.user_id > user.id [delete: cascade]
Ref: trip.trip_status_id > trip_status.id

Ref: trip_day_memo.trip_id > trip.id [delete: cascade]

Ref: trip_item.trip_id > trip.id [delete: cascade]
Ref: trip_item.spot_id > spot.id

Ref: trip_log.trip_id > trip.id [delete: cascade]

Ref: log_comment.log_id > trip_log.id [delete: cascade]
Ref: log_comment.user_id > user.id [delete: cascade]

Ref: log_image.log_id > trip_log.id [delete: set null]
Ref: log_image.user_id > user.id [delete: cascade]

Ref: log_like.log_id > trip_log.id [delete: cascade]
Ref: log_like.user_id > user.id [delete: cascade]