Expedia Server - Booking Schema

[shbada]

BOOKING : 예약

CREATE TABLE BOOKING (
                               ID INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                               BOOKING_NO VARCHAR(100) NOT NULL COMMENT '예약번호',
                               ACCOMMODATION_ROOM_ID INT(11) NOT NULL COMMENT '숙박업체 상품 인덱스',
                               BOOKING_USER_ID BIGINT NOT NULL COMMENT '예약유저 인덱스',
                               BOOKING_LAST_NAME  VARCHAR(50) NOT NULL COMMENT '예약자 성', 
                               BOOKING_FIRST_NAME VARCHAR(100) NOT NULL COMMENT '예약자 이름',
                               BOOKING_HPNO VARCHAR(20) NOT NULL COMMENT '예약자 휴대폰번호',
                               BOOKING_EMAIL VARCHAR(255) NOT NULL COMMENT '예약자 이메일',
                               CHECKIN_START_DATE_TIME DATETIME  NOT NULL COMMENT '체크인 시작 시간',
                               CHECKOUT_END_DATE_TIME DATETIME NOT NULL COMMENT '체크아웃 종료 시간',
                               BOOKING_TOTAL_FEE DECIMAL NOT NULL COMMENT '예약 총 요금',
                               BOOKING_STAT VARCHAR(100) NOT NULL COMMENT '예약진행상태'
                               LAST_MODIFIED_AT DATETIME(6) NOT NULL COMMENT '수정일자',
                               LAST_MODIFIED_BY VARCHAR(255) NOT NULL COMMENT '수정자',
                               CREATED_AT DATETIME(6) NOT NULL COMMENT '등록일자',
                               CREATED_BY VARCHAR(255) NOT NULL COMMENT '등록자',
                               FOREIGN KEY (BOOKING_USER_ID) REFERENCES MEMBER(ID)
                               FOREIGN KEY (ACCOMMODATION_ROOM_ID) REFERENCES ACCOMMODATION_ROOM(ID)
) DEFAULT character set utf8mb4 collate utf8mb4_unicode_ci;

BOOKING_GUESTS : 예약 게스트

CREATE TABLE BOOKING_GUESTS (
                               ID INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                               BOOKING_ID INT(11) NOT NULL COMMENT '예약 인덱스',
                               GUEST_TYPE VARCHAR(100) NOT NULL COMMENT '예약자 유형 코드(성인, 청소년, 유아 등)'
                               GUEST_LAST_NAME  VARCHAR(50) NOT NULL COMMENT '예약자 성', 
                               GUEST_FIRST_NAME VARCHAR(100) NOT NULL COMMENT '예약자 이름',
                               GUEST_HPNO VARCHAR(20) NOT NULL COMMENT '예약자 휴대폰번호',
                               GUEST_EMAIL VARCHAR(255) NOT NULL COMMENT '예약자 이메일',
                               GUEST_AGE INT(3) NOT NULL COMMENT '예약자 나이',
                               GUEST_FEE DECIMAL NOT NULL COMMENT '예약자 요금',
                               LAST_MODIFIED_AT DATETIME(6) NOT NULL COMMENT '수정일자',
                               LAST_MODIFIED_BY VARCHAR(255) NOT NULL COMMENT '수정자',
                               CREATED_AT DATETIME(6) NOT NULL COMMENT '등록일자',
                               CREATED_BY VARCHAR(255) NOT NULL COMMENT '등록자',
                               FOREIGN KEY (BOOKING_ID) REFERENCES BOOKING(ID)
) DEFAULT character set utf8mb4 collate utf8mb4_unicode_ci;

테이블 관계

BOOKING : BOOKING_GUESTS = 1: N

Aggregate

1) Aggregate Root : BOOKING 1-1) BOOKING_GUESTS