[예약 대기 시스템] 2. 어드민 시스템 데이터 모델링 (개체-관계 모델, ERD)

이전 포스팅에서 프로젝트를 시작하게 된 이유와 마일스톤들을 얘기했습니다.

이번 포스팅은 첫 번째 마일스톤인 어드민 시스템의 데이터 모델링에 대해 살펴보겠습니다.

(이전 포스팅: 2024.09.11 - [Project] - [예약 대기 시스템] 1. 답답하니까 직접 만들게요) 

[예약 대기 시스템] 1. 답답하니까 직접 만들게요

 

요구 사항 정의

예약 대기 시스템의 첫 번째 마일스톤인 어드민 시스템을 구현하기 전, 요구 사항 정의를 먼저 해보겠습니다.

먼저, 제가 예약에 도전하는 방탈출 카페 예약 사이트에서 예약 프로세스를 진행하며 분석해 봤습니다.

(관리자 코드 같은 부분은 예약할 때 입력하는 부분입니다. 이 코드의 정확한 용도를 알기 어려워 할인 쿠폰 번호로 간주했습니다.)

---

방탈출 카페의 규모

• 전국 13개 지점이 있고, 추가가 가능하다.
• 각 지점마다 일반적으로 3~5개 테마를 운영한다. (방탈출이라는 사업 특성상 공간적인 한계로 보입니다.)
• 테마마다 운영 시간(입장 시간)은 일반적으로 오전 9시 ~ 오후 10시 사이에 9개 정도 운영된다.
• 테마당 입장 가능 인원은 최소 1명부터 6명까지 제한된다.

지점이 전국에 100개로 확장됐다고 가정하면 일일 예약 최대 규모는 지점 100개 * 테마 5개 * 운영시간 9개 = 4,500건 정도로 추산할 수 있겠습니다.

실제로는 테마마다 예약이 열리는 시간이 모두 다르기 때문에 특정 시간에 트래픽이 스파이크처럼 몰린다고 가정하고 설계를 해야겠습니다.

관리자

• 로그인 아이디는 20글자 이내이며, 고유해야 한다.
• 비밀번호는 최소 8글자 이상을 만족해야 한다.
• 관리자는 이름과 연락처를 등록한다.
• 관리자는 쿠폰(관리자 코드)을 발행할 수 있다.
• 관리자는 역할로 권한을 구분한다.
  
  • 역할마다 관리자, 지점, 테마, 예약, 쿠폰 발행의 권한을 달리한다.

쿠폰

• 쿠폰은 발행될 때 누가 발행했는지 알 수 있어야 한다.
• 한 번 사용된 쿠폰은 다시 사용할 수 없다.
• 쿠폰 코드는 사용 여부를 알 수 있다.

지점

• 각 지점은 고유한 이름을 갖는다.
• 각 지점은 입금받을 계좌번호 여러 개를 가질 수 있다.
• 각 지점은 공지사항(지점 소개 포함)을 관리할 수 있다.
• 각 지점마다 고유한 테마를 운영할 수 있다.

테마

• 모든 테마는 고유한 테마 이름을 갖는다.
• 각 테마는 입장 가격을 갖는다.
• 각 테마는 예약 오픈 시간(예약이 열리는 시간)을 갖는다.
• 각 테마는 조조할인, 마감 할인에 대한 할인율(할인 정책)을 갖는다.
• 각 테마는 운영 여부 상태를 갖는다.

테마 운영

• 각 테마는 입장 시간(운영 시간)들을 갖는다.
• 각 테마는 예약 여부 상태를 갖는다.

예약

• 예약할 때 테마, 예약자 이름, 연락처, 입장 인원, 입장 시간을 필수로 입력해야 한다.
• 쿠폰 코드(관리자 코드)는 선택 사항이다.
• 예약마다 문자 알림 여부, 결제 완료 여부, 당일 입장 여부 상태를 갖는다.

---

개념적 모델링

정의한 요구 사항을 바탕으로 개체-관계 모델을 먼저 도출한 다음, 다시 이를 바탕으로 ERD를 작성해 보겠습니다. 먼저 개체-관계 모델입니다.

개체-관계 모델

개체-관계 모델

개체들은 핵심 개체인 지점, 테마, 예약 정보가 있고, 이를 관리할 수 있는 관리자와 쿠폰이 있습니다.

 

가장 고민됐던 부분은 테마 운영과 관련된 부분인데요, 각 테마마다 입장 가능한 시간들을 어느 개체가 갖고 있어야 할지 결정하기 어려웠습니다. 테마 입장 시간은 테마마다 10개 정도가 있는데, 각 입장 시간마다 예약이 되었는지 확인도 필요했습니다.

 

개체-관계 모델에서 '관계도 속성을 가질 수 있다'라는 특성을 활용해서 최종적으로 위와 같은 그림이 나왔는데요,
테마는 지점에서 관리되기 때문에 '지점이 테마를 관리한다'라는 관계에 '운영 시간(입장 시간)'과 '예약 여부' 속성을 포함시키게 되었습니다.

 

그리고 쿠폰과 예약 개체가 1:1 관계인 것을 제외하면 그 외엔 1:N 관계로 설계했습니다.

하나의 지점이 여러 테마를 관리하고, 하나의 테마는 여러 예약과 관계를 맺기 때문입니다.

마찬가지로 한 명의 관리자가 쿠폰을 여러 번 발행할 수 있다고 봤기 때문에 모두 1:N 관계가 됩니다.

 

이제 개체-관계 모델로부터 ERD를 도출해 보겠습니다.

ERD

ERD

• 개체-관계 모델에서 속성을 갖는 개체나 관계는 ERD에서 테이블로 변환되었습니다.
• 공통적인 부분으로 최소한의 감사(audit)를 위해 모든 테이블에 레코드의 생성 시각과 생성한 사람, 수정 시각과 수정한 사람에 대한 속성을 추가했습니다.
• 몇몇 테이블(지점, 테마, 테마 운영, 관리자 테이블)의 pk는 기본적으로 bigint 가 아닌 int 타입으로 설계했습니다.
  > 일일 예약 건수를 추산할 때 '지점 100개 * 테마 5개 * 운영 시간 9개 = 4,500 건 (일일 최대 예약 건수)'로 추산했고, 레코드 개수를 4바이트의 int 타입으로도 모두 나타낼 수 있기 때문입니다. (약 21억까지 표현)
  
  int 타입이어도 일일 예약 건수가 4,500건이라고 가정했을 때 대략 1,248년 동안 매일 4,500건씩 레코드를 저장할 수 있습니다. (1,248년 = 2,100,000,000 / 일일 4,500건 / 365일 (지점 100개, 지점별 테마 5개, 테마당 운영 시간 9개 기준))
  
  또한 pk를 UUID 타입 또는 varchar 타입으로 설계할 수도 있었지만, 추후 분석 등을 위해 id는 시간의 흐름에 따라 정렬이 돼야 하고 MySQL의 auto_increment 기능을 활용할 수 있기 때문에 int 타입으로 설계했습니다.
• 예약 테이블은 int 타입으로 표현할 수 있었지만 bigint 타입으로 설계했습니다.
  그 이유로 순간 트래픽을 견딜 수 있는 분산 환경을 고려했고, 분산 환경에서 유일 ID 생성할 때 트위터의 snowflake id 방식으로 생성하기 위함입니다.
  기본적으로 64비트의 ID 구조를 갖고, 타임스탬프 정보가 들어가 있기 때문에 시간에 따른 정렬이 가능하도록 설계할 예정입니다.
  따라서 64비트의 ID를 저장하기 적합한 8비트의 bigint 타입으로 설계했습니다.
• 연락처 필드는 국내 휴대전화의 일반적인 형태가 '010-xxxx-xxxx' 임을 반영해  '-'를 포함해 13자리로 제한했습니다.
  ('-' 를 포함한 이유는 관리자가 조회를 할 때 '-'를 포함한 숫자를 입력하는 것이 직접 숫자를 눌렀을 때 실수가 적고 편할 것이라 생각했습니다.)
• 그 외 bool 타입으로 사용할 수 있는 '결제 여부', '예약 여부' 등과 같은 속성은 MySQL 서버 특성상 tinyint(1) 타입으로 저장되기 때문에 이 부분을 제외하면 짚고 넘어갈 부분은 없습니다.

---

물리적 모델링

제가 작성한 dbdiagram.io 에선 ERD를 원하는 DBMS에 바로 사용할 수 있는 DDL로 변환해 주는 기능이 있습니다.

 

하지만 저는 인덱스 부분은 개발 과정 중에 필요할 때 추가할 예정이고, datetime 타입의 DEFAULT 값을 설정하는 부분을 적용하는 방법을 찾지 못해 이 부분은 수동으로 채워 넣었습니다.

 

지금까지 완성된 DDL 은 아래와 같습니다.

CREATE TABLE `admin` (
  `admin_id` int PRIMARY KEY AUTO_INCREMENT,
  `login_id` varchar(20) NOT NULL UNIQUE,
  `password` varchar NOT NULL,
  `name` varchar(100) NOT NULL,
  `phone_number` varchar(13),
  `role` varchar(100),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int
);

CREATE TABLE `coupon` (
  `code` varchar(8) PRIMARY KEY,
  `is_used` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int
);

CREATE TABLE `office` (
  `office_id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `welcome_message` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int
);

CREATE TABLE `account` (
  `office_id` int NOT NULL,
  `bank_name` varchar(100) NOT NULL,
  `account_number` varchar(100) NOT NULL
);

CREATE TABLE `theme` (
  `theme_id` int PRIMARY KEY AUTO_INCREMENT,
  `office_id` int NOT NULL,
  `name` varchar(100) NOT NULL UNIQUE,
  `price` int NOT NULL,
  `open_time` time NOT NULL,
  `discount` int DEFAULT 0,
  `capacity` tinyint(1) DEFAULT 2,
  `is_available` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int
);

CREATE TABLE `theme_management` (
  `management_id` int PRIMARY KEY AUTO_INCREMENT,
  `theme_id` int NOT NULL,
  `operating_time` datetime NOT NULL,
  `is_reserved` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int
);

CREATE TABLE `reservation` (
  `reservation_id` bigint PRIMARY KEY COMMENT 'snowflake id',
  `theme_id` int NOT NULL,
  `customer_name` varchar(100) NOT NULL,
  `phone_number` varchar(13) NOT NULL,
  `head_count` tinyint(1) NOT NULL,
  `reserved_time` datetime NOT NULL,
  `coupon_code` varchar(8),
  `is_paid` tinyint(1) NOT NULL DEFAULT 0,
  `is_notified` tinyint(1) NOT NULL DEFAULT 0,
  `is_attended` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int
);

ALTER TABLE `reservation` ADD FOREIGN KEY (`theme_id`) REFERENCES `theme` (`theme_id`);

ALTER TABLE `theme` ADD FOREIGN KEY (`office_id`) REFERENCES `office` (`office_id`);

ALTER TABLE `account` ADD FOREIGN KEY (`office_id`) REFERENCES `office` (`office_id`);

ALTER TABLE `theme_management` ADD FOREIGN KEY (`theme_id`) REFERENCES `theme` (`theme_id`);

ALTER TABLE `reservation` ADD FOREIGN KEY (`coupon_code`) REFERENCES `coupon` (`code`);

ALTER TABLE `coupon` ADD FOREIGN KEY (`created_by`) REFERENCES `admin` (`admin_id`);

이 스키마는 개발할 때 JPA가 자동으로 생성해 주는 스키마와 비교할 때 사용할 것이므로 테스트할 때를 제외하고 수동으로 생성하지는 않겠습니다.

 

다음 포스팅에선 프로젝트 설정에 대해 다뤄보겠습니다.

읽어주셔서 감사합니다.