[최종 프로젝트] 예약 시스템 데이터베이스 설계하기

타요의 예약 시스템

저희 프로젝트 타요에서 핵심 기능을 하나만 뽑으라면 뭐니 뭐니 해도 예약일 것입니다.
저희의 예약 시스템을 간략하게 설명드리면 아래와 같습니다.

• 차를 빌려주는 사람(이하 호스트)은 본인 차량의 예약 가능 기간을 설정할 수 있다.
• 차를 빌리는 사람(이하 게스트)은 해당 차량의 예약 가능 기간안에서 예약할 수 있다.
• 모든 예약은 해당 차량의 예약 가능 기간 중 한 구간에 완전히 포함되어야 하며 서로 겹치는 날짜가 있어서는 안 된다.

이렇게 적고 보니 알고리즘 문제 같네요. 에어비앤비의 예약 시스템과 굉장히 비슷합니다!
저희는 위 문제에 맞는 DB 구조에 대해서 비교적 오랜 시간 고민했고 결론을 내린 후에도 몇 번의 변경이 있었습니다. 개인적으로 정말 재밌었던 경험이었고 `진짜_최종_결론`을 내리고 프로젝트가 끝난 후에도 더 나은 방법 알게 되기까지의 여정을 기록해 보려 합니다.

 

그럼 저희 조의 일원이 되어 함께 어떻게 DB를 설계할지 고민해 봅시다!

 

날짜를 저장하는 방법

들어가기 앞서 구간으로 정의된 날짜를 저장하는 방법에 대해서 생각해 보겠습니다. 저희가 생각한 방법은 크게 낱개의 날짜로 저장하는 방법과 시작과 끝으로 이루어진 범위로 저장하는 방법입니다.
예시를 통해 두 방법의 차이를 비교해 봅시다. 예시는 2024년 3월을 기준으로 통일하겠습니다.
호스트는 평일에는 재택근무로 자동차를 빌려줄 수 있지만 주말에는 본인이 사용해야 하기에 평일에만 예약 가능하도록 설정했습니다. 넷째 주 주말에는 기차를 타고 놀러 가는 약속이 있어 주말에도 호스팅 하려 합니다.

(깨알 홍보: https://react-components.thecloer.com에서 이번 프로젝트를 진행하며 제가 만든 달력 컴포넌트를 직접 사용해 보실 수 있습니다.)

• 낱개로 저장한 경우: [4, 5, 6, 7, 8, 11, 12, 13, 14, 15, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29]
• 범위로 저장한 경우: [[4, 8], [11, 15], [18, 29]]

이렇게만 보면 범위로 저장하는 것이 저장공간 관점에서 이점이 있어 보입니다. 하지만 길이가 1인 낱개 구간이 많다면 오히려 낱개로 저장하는 방법이 더 적은 저장 공간을 차지할 수 있습니다.
극단적인 예로 홀수만 예약 가능하도록 설정한 경우를 생각해 볼 수 있습니다.

• 낱개로 저장한 경우: [1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31]
• 범위로 저장한 경우: [[1, 1], [3, 3], [5, 5], [7, 7], ..., [27, 27], [29, 29], [31, 31]]

날짜를 낱개로 저장한다면 날짜만 저장하는 테이블을 만들어 날짜를 정규화하여 사용하는 방법도 생각해 볼 수도 있을 것 같습니다.
저희가 저장하는 예약과 예약 가능 기간은 하루 씩 설정되는 경우보다는 구간으로 설정되는 경우가 더 많을 것입니다. 저희는 원본 데이터의 특성을 유지하여 저장하는 것이 더 유연할 것이라 생각해 구간으로 저장하는 방법에 조금 더 가중치를 두고 고민했습니다.

이제 ERD를 그려보며 테이블을 어떻게 나누고 어떤 방법으로 날짜를 저장할지 생각해 보겠습니다.

 

DB 설계 후 각각에 대해 쿼리 작성해 보기

데이터베이스 구조를 생각해 보고 해당 구조에서의 쿼리는 어떻게 작성될 수 있는지 생각해 보겠습니다.

1. 예약 가능 기간은 자동차의 속성으로 볼 수 있다!!

예약 가능 기간은 차종, 연비, 탑승 가능 인원 등과 같은 자동차의 속성으로 볼 수 있습니다. 따라서 자동차 테이블(이하 car)에 하나의 컬럼으로 예약 가능 기간을 저장하는 방법에 대해 고민해 봤습니다. 하나의 컬럼에 여러 데이터를 저장하는 것은 정규화를 위반합니다. 상황에 따라 비정규화가 더 나은 선택인 경우도 있으니 한번 고민해 봅시다. 저희는 비트로 저장하는 방법과 JSON으로 저장하는 방법을 고민해봤습니다.

 

1.1. 비트로 저장

가장 오른쪽 비트를 마지막 업데이트 날짜에 대응시키고 하루를 1비트로 오프셋 기반으로 계산하는 방법입니다.
예를 들어 호스트가 2024년 3월 1일에 아래와 같은 날짜로 예약 가능 날짜를 설정했다고 가정해 봅시다.

 

이때 car에는 위와 같이 저장될 것입니다. 예시는 31개의 비트이며 가장 오른쪽 비트는 updated_at인 2024-03-01일을 의미하고 가장 왼쪽 비트는 30일 후인 2024-03-31일을 의미합니다.
비트로 저장한다면 적은 저장공간과 범위 연산이 빠르다는 당점이 있습니다. 한 달은 4바이트 안에 저장할 수 있고 1년도 46 바이트면 충분합니다.
저장공간 절약 보다도 빠른 범위 연산이 더 큰 장점이라고 생각합니다. 예약 시스템에서 날짜와 관련된 연산은 대부분 범위 연산입니다.
[start, end] 기간에 예약 가능한 차량을 검색하거나 예약할 경우 두 번의 범위 연산이 필요합니다.

1. [start, end]를 포함하는 예약 가능 기간이 있는 차량을 찾는다. (예약 가능한가?)
2. [start, end]과 겹치는 기간에 예약이 있는지 확인한다. (중복 예약이 있는가?)

비트 연산을 사용하면 범위 연산을 빠르게 수행할 수 있습니다.
예를 들어 [2024-03-13, 2024-03-15]에 예약 가능한 차량을 검색하는 SQL 쿼리는 아래와 같습니다.

SET @start = DATE('2024-03-13');
SET @end = DATE('2024-03-15');

SET @mask = (1 << (DATEDIFF(@end, @start) + 1)) - 1;

SELECT car_id
FROM car
WHERE ((available_dates >> DATEDIFF(NOW(), updated_at) & @mask) = @mask;

하지만 비트로 저장하는 방법에는 데이터를 조합해야만 그 의미를 정확하게 알 수 있다는 단점이 있습니다. 예를 들어 데이터베이스 오류 혹은 잘못된 기능 구현으로 updated_at이 available_dates와 별개로 변경될 경우 복구가 불가능합니다.
즉, available_dates만 보고 데이터를 해석할 수 없고 항상 updated_at과 available_dates의 동시 업데이트가 보장되어야 합니다.

 

1.2. JSON 타입으로 저장

비트로 저장했을 경우 하나의 정보가 두 개의 데이터로 분리되어 저장되어 관리된다는 단점이 있었습니다. 이를 해결하기 위해 생각한 방법이 JSON 타입입니다.
저희가 사용하는 MySQL은 JSON 타입을 지원하며 InnoDB는 8.0.17 이상 버전에서는 Multi-Valued Indexes로 JSON 타입의 내부도 인덱싱이 가능합니다.
JSON 타입을 이용해 예약 가능 기간을 구간 형식으로 저장하면 저장된 모습과 SQL 쿼리는 아래와 같습니다.

SET @start = DATE('2024-03-13');
SET @end = DATE('2024-03-15');

SELECT car.car_id
FROM car, JSON_TABLE(
        car.available_dates,
        "$[*]" COLUMNS(
        start DATE PATH "$[0]",
        end DATE PATH "$[1]"
        )
    ) AS available_date
WHERE available_date.start <= @start AND @end <= available_date.end;

가장 처음 실제 DB를 구현하고 API개발을 진행했던 방법은 JSON 타입으로 저장하는 방법이었습니다. 하지만 JSON 타입으로 저장했을 때의 문제점이 있었습니다. 바로 쿼리 작성이 쉽지 않다는 점입니다.
위 쿼리는 네이티브 쿼리로 작성하면 문제가 없지만 다른 조건과 함께 JPA에서 사용될 때가 문제입니다. 검색 API의 경우 11개의 조건에 맞춰 동적으로 쿼리를 생성합니다. Spring Data JPA에서 동적 쿼리 생성을 위해 Specification을 사용했는데 Specification에서 JSON 쿼리를 작성하는 난도가 높았습니다. 또한 예약과 관련된 DB 작업에는 위와 같은 네이티브 쿼리가 추가되어야 합니다. JSON 쿼리를 JPA의 다른 조건들과 함께 작성 방법을 저를 포함한 조원 모두가 추가로 학습해야 했기에 도입하기엔 무리가 있다고 판단했습니다.

 

1.3. JSON 타입에 날짜를 비트로 저장

위 두 방법을 합쳐 JSON 타입에 날짜와 첫 번째 비트의 날짜를 저장하는 방법입니다. 비트로 저장하는 방법의 문제점인 정보가 나뉘어 저장된다는 점 보완한 것입니다. 여전히 쿼리에 대한 문제가 남아있지만 좋은 아이디어인 것 같습니다.
이 방법은 이 글을 작성하며 떠오른 생각이라 직접 실험해 보지는 못했습니다. 추후 실제 구현해 보고 위키를 업데이트하도록 하겠습니다.

2. 예약은 예약 가능 기간 중 하나의 구간에 종속된다!

가장 처음 DB는 JSON 타입으로 구현했었습니다. 범용적으로 쿼리를 작성하기 힘든 이유로 다른 구조를 고민했고 다음으로 나온 해결 책이 몇 가지 있습니다. 이 중 하나는 아래와 같은 ERD입니다.

예약은 예약 가능 기간 중 하나의 구간에 완전히 포함되어 있으므로 예약 가능 기간과 예약은 일대다 관계로 표현될 수 있습니다. 이 경우 예약 생성, 수정, 예약 가능 차량 조회 시 car와 join하지 않아도 됩니다.

`[start, end]` 기간에 예약 가능한 차량을 조회하는 쿼리는 아래와 같습니다.

SET @start;
SET @end;

SELECT CAD.car_id
FROM car_available_dates as CAD
LEFT JOIN booking as B -- [@start, @end]와 겹치며 활성화된 예약과 left join
    on CAD.id = B.car_available_dates_id 
    AND (B.start <= @end AND @start <= B.end)
    AND B.status IN (READY, USING)
WHERE CAD.start <= @start AND @end <= CAD.end
GROUP BY CAD.id
HAVING count(B.id) = 0;

위 ERD는 문제점은 예약 가능 날짜가 변경될 때 발생합니다. 바로 예약 가능 날짜가 변경될 때마다 이와 연결된 예약의 FK를 함께 변경해야 하는 문제입니다. 예시를 통해 이 문제에 대해 살펴보겠습니다. 예약은 READY, USING, DONE, CANCEL의 상태값을 갖습니다. 오늘이 3월 7일이라 가정해 보겠습니다.

예약 가능 날짜와 예약 가시화

예약 가능 날짜 변경 전

다음과 같이 `예약 가능 날짜`와 `예약`이 있다면 호스트 캘린더에는 위와 같은 모습으로 표시될 것입니다.

 

이때 호스트가 예약 가능일에 9일, 10일을 추가하여 아래와 같이 설정한다면 DB에는 어떻게 반영되어야 할까요??

수정된 예약 가능 날짜와 예약 가시화

예약 가능 날짜 변경 후

합쳐지는 두 구간에 해당하는 레코드를 삭제하고 새로운 레코드를 추가한다면 위와 같은 모습으로 저장될 것입니다. 이때 예약 테이블의 FK(Reservation.car_available_dates_id)가 변경되며 심지어 어떤 예약들은 FK를 잃어버리게 됩니다.

 

MySQL innoDB의 경우 FK에 기본으로 인덱스가 적용됩니다. 인덱스가 설정된 컬럼의 값을 빈번하게 변경하거나 카디널리티가 높은 Null 값을 갖게 된다면 성능 저하가 발생할 것입니다. 더 큰 문제는 `예약`과 `자동차`사이의 연결이 끊어져 해당 예약의 자동차에 대한 정보를 잃게 됩니다.

 

3. 예약 가능 기간과 예약을 독립적으로 관리하자!

가장 먼저 떠올릴 수 있는 ERD라고 생각합니다. 그럼에도 가장 나중에 고려했던 이유는 위 ERD의 경우 예약 가능 차량을 조회하기 위해 테이블 3개를 join해야 하기 때문입니다. 앞서 살펴본 테이블 관계들은 join을 한 번으로 줄일 수 있었기에 먼저 고려했지만 여러 이유로 돌고 돌아 가장 간단한 방법으로 회귀했습니다. Simple is the best!!

 

조금 생각해 보면 사실 위 방법은 join하지 않고 예약 가능한 차량을 검색할 수 있습니다. 예약 테이블과 예약 가능 날짜 테이블에 각각 select 쿼리를 날리면 됩니다. 데이터 특성에 따라 join 비용과 네트워킹 비용을 비교해봐야 할 것 같습니다.

 

최종 선택과 회고

저희는 JSON으로 저장하는 방법을 거쳐 최종적으로 마지막 방법(`예약` - `자동차` - `예약 가능 날짜`)으로 데이터베이스를 설계했습니다. 프로젝트 중간에 데이터베이스를 변경하며 많은 API를 수정하고 테스트 코드를 날려먹었지만 좋은 경험이었다고 생각합니다.

결과만 놓고 보면 과정에 비해 너무 간단해서 누군가에게 설명하기 민망하기도 합니다. 그럼에도 글로 작성한 이유는 이 경험을 통해 얻은 것이 최종 ERD가 아닌 끊임없는 고민과 토의를 통해 프로젝트 중간에도 데이터베이스를 수정한 경험이라고 생각하기 때문입니다.

저희는 정해진 시간 안에 프로젝트를 완수하기 위해 빠르게 최선이라 생각되는 방법을 선택해 프로젝트를 진행했고 더 나은 방법이 없는지 계속해서 고민하고 토의했습니다. ERD를 수정하자는 제안을 했을 때 이미 작성된 API와 테스트 코드에 대한 매몰 비용보다 더 나은 방법인지에 대해 생각하는 팀원들을 보며 팀원을 참 잘 만났다는 생각에 감사했습니다.

 

예약 동시성 문제를 해결하며 최종 선택한 ERD보다 더 좋은 방법을 알게 되었습니다. 이 방법은 예약 시스템 동시성 문제 해결 글에서 다루도록 하겠습니다.