회원 DB 설계 - hoewon DB seolgye

현재 저는 MarketGola라는 프로젝트를 진행하고 있고 프로젝트를 들어가기에 앞서 바로 DB 설계를 진행하였습니다. DB 설계는 ‘파워 오브 데이터베이스’라는 책을 참고하여 진행하였습니다. 처음 DB설계를 접하면서 어디서부터 시작해야할지, 어떤 식으로 진행해야할지 막막한 분들에게 도움이 되었으면 좋겠습니다.

프로젝트 소개

저희 프로젝트는 마켓컬리를 모티브로 한 이커머스 프로젝트이며 데이터베이스는 MySQL을 사용하고 있습니다. 회원, 주문/결제, 장바구니, 상품 검색, 마이 페이지 등의 기능이 있습니다.

요구사항

각 기능의 요구사항은 다음과 같습니다.

카카오 오븐으로 설계한 화면을 보시면 프로젝트를 더 명확하게 이해하실 수 있습니다.

들어가며

돈에 관하여

회사 입장에서 '돈'을 버는 방법은 여러가지가 있습니다. 서비스를 이용하는 고객을 통해 버는 '돈' 뿐만 아니라 '비용'을 줄이는 것도 따지고 보면 '돈'을 버는 것이라고 할 수 있습니다. '비용'은 실제 '돈' 뿐만 아니라 '시간' 또한 의미합니다. 개발자가 개발하는데 드는 시간, 커뮤니케이션을 하는데 드는 시간은 모두가 '비용'이라고 할 수 있죠.

갑자기 무슨 뚱딴지같이 돈을 얘기하나 싶을 수 있습니다.
하지만 제가 돈에 관해 이야기 하는 이유는 좋은 데이터베이스 설계가 돈과 관련이 있기 때문입니다.

질문을 던져보겠습니다.

왜 데이터베이스를 설계해야 할까요?

그것은 좋은 설계를 가진 데이터베이스가 '돈'을 벌어다주기 때문입니다.

좋지 않은 설계를 가진 데이터베이스의 문제

좋지 않은 설계를 가진 데이터베이스는 다음과 같은 문제를 갖게 됩니다.

1. 정확하지 않은 데이터를 포함하게 됩니다. 예를 들어 실제 상품의 가격은 40,000원인데 데이터베이스에는 50,000원이라고 기록되어 있는 경우입니다. 신뢰할 수 없는 데이터베이스를 복구하기 위해 무엇이 잘못되었는지 파악해야 하고, 잘못된 데이터들을 변경시켜주어야 하며, 이러한 일이 다시 발생하지 않게 하기 위해 테이블을 다시 설계해주어야 합니다. 이러한 작업을 하는 동안 서비스가 얼마간 정지될 수 있으며, 개발자들의 많은 시간을 소모시키게 됩니다.

   => 비용이 많이 든다.

1. 작은 요구사항 변경이 생겼는데 기존 데이터베이스에 많은 변경이 일어나야 합니다. 예를 들어 잘못된 데이터베이스 설계로 인해 이 변경을 수용하기 위해 기존 테이블을 쪼개고 기존 데이터들을 정제해서 다시 넣어야 합니다. 이러한 작업을 하는 동안 서비스가 얼마간 정지될 수 있으며, 개발자들의 많은 시간을 소모시키게 됩니다.

   => 비용이 많이 든다.

2. 비효율적인 데이터베이스 구조로 인해 고객이 서비스를 이용하는데 너무 많은 시간이 걸려 서비스를 이탈합니다.

   => 돈을 덜 벌게 된다.

모든 것을 '돈'과 관련짓는 것이 억지스럽고 굳이 이렇게 바라봐야하나? 생각하실 수 있습니다.
하지만 저는 이 과정이 중요하다고 생각합니다. 왜냐하면 모든 것을 하나의 통일된 기준으로 바라볼 수 있기 때문입니다.

A라는 설계안은 저장 공간을 아껴줍니다. 이외에도 수많은 장점과 단점이 있습니다.
B라는 설계안은 성능을 높혀줍니다. 이외에도 수많은 장점과 단점이 있습니다.

무엇을 선택해야 할까요?

이런 상황에서 기준을 통일한다면 선택은 마치 피자와 치킨 중 '더 맛있는' 것을 선택하는 것과 같이 간단해집니다.

두 설계안 중 '돈 많이 벌어주는' 것을 고르면 됩니다.

좋은 데이터베이스 설계란?

사실 각각이 대체 얼마를 벌어줄까? 혹은 얼마를 아껴줄까?를 생각하는 작업은 말처럼 간단하지 않습니다.

이것을 예상하는 능력은 경험이 많아질수록 더 정확해지는 것 같습니다.

'파워 오브 데이터베이스'의 저자이자 20년 이상의 경험을 가진 관계형 데이터베이스의 베테랑 개발자 마이클 J. 헤르난데즈는 다음과 같은 목적들에 초점을 맞추어 설계하라고 합니다.

1. 무결성 - 데이터베이스 내에 모든 값은 언제나 정확한 값을 유지해야 한다.

2. 유연성 - 데이터베이스 구조는 요구사항 변화에 대해 수정이 쉬워야 한다.

3. 확장성 - 데이터베이스 구조는 기능 확장에 대해서 수정이 쉬워야 한다.

그 이유는 바로 이러한 목적으로 설계된 데이터베이스가 돈을 많이 벌어다 주고 이것이 경험을 통해 증명됐기 때문입니다.

참고로 '성능' 또한 실무에서는 빼놓을 수 없는 고려사항 중 하나입니다. 하지만 성능은 가장 마지막에 고려해야 합니다. 그 이유는 성능 개선을 위한 설계안의 반영이 종종 가장 중요한 무결성을 해치는 결과를 낳기 때문입니다. 따라서 무결성과 유연성 및 확장성을 고려한 데이터베이스 설계를 진행한 후, 성능이 정말 문제가 될 때 성능 개선을 진행하는 것이 좋습니다.

따라서 좋은 데이터베이스 설계는 무결성, 유연성, 확장성을 위한 선택과 성능을 위한 선택이 적절한 조화된 설계라고 할 수 있습니다.

테이블 설계

예비 테이블 만들기

데이터베이스를 설계할 때 어디서부터 시작해야할까요?
'파워 오브 데이터베이스'에서는 필요한 필드들이 뭐가 있을지 예비 필드 목록을 뽑고, 이를 적절히 묶어서 예비 테이블을 만들라고 합니다. 만들고자 하는 서비스와 유사한 서비스를 찾아 어떤 데이터를 수집하는지 찾아보고 또 어떤 데이터가 필요할지 예상해보면 좋습니다.

저희 프로젝트의 예비 테이블은 다음과 같이 만들어졌습니다.

이상적인 테이블 구조에 따라 테이블 구성하기

'파워 오브 데이터베이스'에서는 이상적인 테이블 구조를 제시합니다. 이 구조에 따라 테이블을 구성하면 자연스럽게 '무결성'과 '유연성' 및 '확장성'이 있는 데이터베이스를 설계하게 됩니다.

개체 또는 사건일 수 있는 단일 주제를 나타낸다.

테이블이 단일 주제를 나타내는 것은 가장 중요합니다. 대부분의 문제가 테이블이 여러 주제를 나타낼 때 일어납니다.

저희 테이블에서는 ‘유저’, ‘상품’, ‘후기’ 등이 개체이고 ‘주문 기록’, ‘결제 기록’ 등이 사건이라고 할 수 있을 것 같습니다. 또한 모두 단일 주제를 나타낸다고 보여서 이 조건에 따른 추가적인 작업은 진행하지 않았습니다.

기본 키를 가진다.

기본키는 레코드를 식별하고 테이블 관계를 설정하기 위해 필요합니다.

이 작업에서는 다음과 같은 부분을 고민했습니다.

‘기본키를 자연키로 가져갈까 인조키로 가져갈까?’

사실 다른 테이블들은 기본키로 구성할만한 자연키가 마땅히 없어서 모두 id라는 인조 필드를 만들고 기본키로 설정해주었습니다. 하지만 회원 테이블에서는 아이디를 기본키로 가져가도 되지 않을까?라는 생각이 들어 각각의 장단점을 비교해보았습니다.

자연키 장점

1. MySQL의 InnoDB스토리지 엔진의 경우 기본키를 바탕으로 테이블 자체가 클러스터링 인덱스가 되기 때문에 자연키를 기본키로 한다면 이 자연키를 위해 따로 인덱스를 구성할 필요가 없습니다.

2. 커버링 인덱스가 작용할 가능성이 높습니다.

자연키 단점

1. 비즈니스 요구사항의 변화로 인해서 기본키로 사용하던 자연키를 변경해야 할 수 있습니다.
   예를 들어 회원 테이블에 주민번호를 기본키로 사용했는데 주민번호를 저장하지 못하도록 법이 개정되면 문제가 생길 수 있습니다.
   

인조키 장점

1. 비즈니스 요구사항 변화에 전혀 영향받지 않기 때문에 변경할 일이 없습니다.

인조키 단점

1. 인덱스를 추가로 구성해주어야 할 수 있습니다.

2. 인조키를 위한 필드가 추가되어 테이블이 커집니다.

자연키의 장점은 '성능', 인조키의 장점은 '유연성'이라고 할 수 있습니다.

'유연성'이 '성능'보다 우선순위가 높기 때문에 유저 테이블 또한 인조키를 기본키로 하기로 결정하였습니다.

다중값 또는 다중 부분 필드들을 포함하지 않는다.

다중 값 필드와 다중 부분 필드는 그 안에 데이터를 수정, 삭제, 정렬, 그룹화하기가 어렵기 때문에 피하는 것이 좋습니다.

다중값 필드
저희 테이블에는 ‘후기’ 테이블에 ‘사진들’이라는 필드가 다중값을 갖고 있었습니다.
한 필드에 여러 사진이 들어가기 때문이죠.

후기 테이블에서 하나의 레코드를 사진에 따라 여러 레코드로 쪼갤 수 있지만 그럴 경우 많은 데이터가 중복이 됩니다.
따라서 이 부분은 후기 사진이라는 테이블을 따로 만들고 후기 테이블과 관계를 맺어주는 방식으로 다중값 필드를 해결하였습니다.

변경 전	변경 후

다중부분 필드

저희는 ‘유저’ 테이블에 ‘주소’라는 다중 부분 필드를 가지고 있었습니다.
왜냐하면 ‘주소’라는 하나의 필드가 서울시 / 성북구 / 길음동 등 여러가지 필드로 나뉠 수 있기 때문입니다.

하지만 저희 프로젝트에서 ‘주소’의 경우 수정은 전체를 한 번에 수정하거나 삭제하도록 기획되어 있고, 시, 구 ,동에 맞춰 정렬하거나 그룹화가 필요한 기능을 염두에 두고 있지 않기 때문에 주소를 따로 테이블로 빼내어 다중부분 필드를 해소해주지 않았습니다.

계산된 필드들을 포함하지 않는다.

계산된 필드는 계산에 참여하는 필드의 값이 바뀔 때 갱신되지 않고, 이러한 갱신 작업의 책임이 응용프로그램으로 넘겨지기 때문에 피하는 것이 좋습니다.

저희 테이블에는 ‘결제 기록’ 테이블에 ‘최종 금액’이라는 계산된 필드를 갖고 있었습니다. 왜냐하면 최종 금액은 전체 금액, 할인 금액, 배달금액, 적립금 사용금액에 의해 계산되기 때문입니다. 따라서 '최종 금액'은 '결제 기록' 테이블에서 제거하였습니다.

외래키 외에 불필요하게 중복되는 필드들을 포함하지 않는다.

중복되는 필드들은 불필요하게 저장공간을 차지할 뿐만 아니라 수정이 일어났을 때 같이 수정이 이뤄지지 않아 무결성을 무너뜨리기 쉽습니다.

저희 테이블에는 중복되는 필드가 없어 추가적인 작업은 진행하지 않았습니다.

단지 절대적으로 최소한의 중복 데이터만 포함한다.

저희 테이블에는 ‘유저’ 테이블에 ‘등급’이라는 필드의 값이 중복되서 나타나게 됩니다.
예를 들어 여러명의 유저는 '골드'라는 등급을 가질 수 있습니다. 이 때 '골드'라는 등급의 이름이 '블랙'이라는 이름으로 바뀌게 되면 '골드'라는 등급을 가진 모든 레코드가 '블랙' 등급을 갖도록 수정해주어야 합니다. 또한 새로운 등급을 추가하고 싶어도 그 등급에 맞는 회원이 회원 테이블에 존재하지 않으면 추가할 수가 없습니다.

따라서 등급 테이블을 따로 빼주었고 등급에 따른 적립률 또한 같이 관리하도록 구성하였습니다.

변경 전	변경 후

필드 명세 설정

테이블 구성이 어느 정도 완료가 되었다면 다음은 각 필드마다 필드 이름, 데이터 종류, 허용 가능한 데이터의 길이, 값의 범위, 유일성, NULL 지원, 기본값을 어떻게 할지 등을 정해주어야 합니다. 필드와 특성이 너무 많아서 특별하게 고민했던 포인트만 뽑아보았습니다.

데이터 타입, 길이, 값의 범위에 대한 고민

CHAR vs VARCHAR- 이름, 비밀번호, url, 내용 등

TEXT는 고정길이를 갖고 있습니다. 만약 데이터 타입이 TEXT이고 길이 제한이 10글자라면 TEXT 데이터 타입은 apple을 'apple '과 같이 저장합니다. 따라서 저장되는 데이터의 길이가 일정하지 않다면 많은 저장 공간이 낭비가 됩니다.

VARCHAR는 가변길이를 갖고 있습니다. 마찬가지로 10글자를 저장하는 VARCHAR는 apple을 그대로 'apple'로 저장합니다. 하지만 VARCHAR는 저장되는 데이터 크기에 딱 맞게 데이터 공간이 배정되기 때문에 더 긴 길이의 문자로 수정되야 한다면 레코드 전체를 다른 곳으로 옮겨서 새로 저장해야 합니다. 'apple'이라는 데이터 주위로 이미 다른 데이터가 채워져 있기 때문이죠.

정리해보면 TEXT는 저장공간이 낭비되고 VARCHAR는 수정에 대한 성능이 떨어진다고 할 수 있습니다.

'저장 공간의 낭비'는 앞에서 제시한 3가지 기준(무결성, 유연성, 확장성)에 없기 때문에 그 다음으로 중요한 성능이 좋은 TEXT를 선택해야 할까요?

조금만 더 들여다보면 저장공간 또한 성능과 관련이 있다는 것을 알 수 있습니다.
저장공간이 커진다는 것은 테이블의 크기가 커진다는 것과 같습니다. 테이블의 크기가 커질수록 데이터를 찾는데 드는 시간 또한 길어집니다. 또 이 필드가 만약 인덱스로 사용된다면 이 크기의 차이는 더 심한 성능차이를 가져올 수 있습니다.

웹 서비스에서 사용하는 데이터베이스는 조회, 수정, 삽입, 삭제 중 조회의 성능이 가장 중요합니다. 왜냐하면 일반적인 웹 서비스에서는 조회와 쓰기, 삽입, 삭제의 비율이 약 2:8 정도로 조회가 압도적으로 많이 요청되기 때문입니다. 하나의 게시글이 한 번 작성되고 많아봐야 2~3번 수정될 때 이 게시글이 최소 10번은 조회되는 것을 생각해보면 무슨 말인지 이해가 되실 거라고 생각합니다.

설명이 길었지만 이제 TEXT와 VARCHAR의 차이는 결국 수정 성능과 조회 성능 중 무엇을 택할 것인가? 정도로 비교적 간단해졌습니다.

저희는 대부분의 문자열 데이터에 조회 성능이 좋은 VARCHAR를 선택하였습니다.

VARCHAR 타입의 경우 어차피 저장되는 값의 크기만큼만 공간을 차지한다면 길이를 제한한 필요가 있을까? 라는 생각이 들 수 있습니다. 제약이라는 것은 DB단에서 유효하지 않는 데이터를 거를 수 있게해주고 이는 무결성을 높히는데 도움을 줍니다. 따라서 각 데이터들이 허용 가능한 값에 맞춰 길이 제약을 정해주는 것이 좋습니다. 예를 들어 저희는 상세 내용이 500자까지만 작성가능한 ‘후기’ 테이블에 ‘내용’ 필드는 VARCHAR(500)으로 정하였습니다.

상품 상세 내용을 어떤 타입으로 저장할지?

상품 상세 내용은 글과 사진이 동시에 들어갑니다.

저희는 이미지는 다른 저장소에 저장하고 저장된 url이 들어간 html 자체를 저장하기로 하였습니다. 마켈 컬리 상품의 html 길이를 파악해보니 상품 하나 당 html의 길이가 약 30만자 정도가 되었습니다. 따라서 1600만자 까지 저장 가능한 MEDUINTEXT로 데이터 타입을 결정하였습니다. (1600만 까지 저장 가능하지만 실제로 저장된 크기만큼 공간을 차지합니다.)

DATETIME vs TIMESTAMP - 생성일시, 수정일시

TIMESTAMP는 시간을 UTC로 저장하기 때문에 TIME ZONE에 따라 그 TIME ZONE에 맞는 시간으로 저장된 시간을 볼 수 있다는 장점이 있습니다. 예를 들어 한국에서 2022년 6월 23일 22시에 저장된 데이터가 있다고 하겠습니다. 이 때 한국보다 5시간이 느린 나라에서 이 데이터를 요청할 경우 2022년 6월 23일 17시에 저장된 데이터라고 나옵니다. TIMESTAMP는 이것이 UTC로 저장되기 때문에 MySQL에서 자동으로 변환해줄 수 있고 DATETIME은 수동으로 변환해주어야 합니다.

하지만 TIMESTAMP는 2038년1월19일까지 밖에 저장할 수 없다는 문제가 있어 DATETIME을 사용하기로 하였습니다.

ENUM 데이터타입 vs 참조테이블 - 성별, 등급, 결제 타입, 상품 상태, 주문 상태 등

둘 다 필드에 들어갈 수 있는 값을 제한해주는 역할을 합니다.

• 참조 테이블의 예 - 등급 테이블

'유저'는 '등급' 테이블에 존재하지 않는 등급을 가질 수 없습니다.

ENUM의 장점

1. 쿼리에 값을 명확하게 넣을 수 있다.

2. 조인이 필요없어 조회 속도가 빠르다.

3. 데이터 용량을 적게 차지한다.

4. 추가적인 테이블이 필요없다.

ENUM의 단점

1. ENUM 값들과 연관된 데이터를 저장할 수 없다.

2. ENUM 데이터의 변경이 일어나면 적용되는데 시간이 오래 걸린다.

참조테이블의 장점

1. 연관된 값을 같이 저장할 수 있다.

2. 새로운 값을 추가하는 것이 쉽다.

참조테이블의 단점

1. 쿼리에 값을 명확하게 넣을 수 없다.
2. 매번 조인을 통해 값을 가져와야 해서 조회 속도가 느려질 수 있다.

요약해보면 ENUM의 장점은 성능 및 간편함 참조테이블의 장점은 유연성이라고 할 수 있습니다.

기준에 따르면 유연성이 좋은 참조테이블로 모두 구성하는게 좋다고 판단되지만 아무래도 '성별' 테이블, '주문 상태' 테이블 등을 만드는 것은 직관적으로 와닿지 않고 데이터베이스 구조가 너무 복잡하다 느껴졌습니다.

따라서 자주 변경이 되는지, 필요한 데이터들을 같이 보관해야 할 필요가 있는지를 기준으로 삼고, 자주 변경될 수 있으면서 필요한 데이터들을 같이 보관해야 하는 ‘등급’과 ‘결제 타입’은 참조 테이블을 따로 만들었으며 그외의 ‘성별’, ‘상품 상태’, ‘주문 상태’ 등은 ENUM 데이터 타입을 선택했습니다.

이런 결정들은 추후 어떤 문제가 생기게 되는지 지켜볼 예정입니다.

관계 설정

두 테이블은 ‘일대일 관계’, ‘일대다 관계’, ‘다대다 관계’를 맺을 수 있으며 식별 관계 또는 비식별 관계가 될 수 있습니다. 요컨대 일대일 식별 관계, 일대다 비식별 관계 등으로 관계가 나타내어질 수 있습니다.

또한 하나의 테이블은 특별한 관계인 ‘자가 참조 관계’를 구성할 수 있습니다.

일대일 관계, 일대다 관계, 다대다 관계

두 테이블이 어떤 관계를 갖는지 알기 위해서는 다음과 같은 질문을 던져보면 좋습니다.

A라는 테이블과 B라는 테이블이 있다고 할 때, A라는 테이블의 하나의 레코드가 B라는 테이블의 몇 개의 레코드와 연관될 수 있을까?

답이 하나라면 일대일 관계, 여러개라면 일대다 관계, 여러개이면서 그 역도 성립한다면 다대다 관계로 정해주면 됩니다.

ex.

질문 : 한 명의 유저는 몇개의 후기와 연관될 수 있을까? (= 몇개의 후기를 쓸 수 있을까?)

답변 : 최소 0개부터 여러개와 연관될 수 있다. (= 안 써도 되고 여러개 쓸 수 있다)

→ 일대다 관계

관계를 복잡하게 만들고 불필요한 데이터를 추가시키기 때문에 불필요한 연관관계는 맺지 않았습니다. 예를 들어 ‘유저’와 ‘결제 기록’은 연관관계를 맺지 않았습니다. ‘유저’와 ‘결제 기록’이 동시에 필요할 경우 ‘유저’와 ‘주문 기록’의 관계를 이용할 수 있기 때문입니다.

다대다 관계 풀어주기

저희 테이블에는 '주문 기록'과 '상품'이 다대다 관계를 갖고 있었습니다. 왜냐하면 주문 기록 하나에는 여러가지 상품이 들어 있을 수 있고 하나의 상품은 여러 주문 기록에 들어 있을 수 있기 때문입니다.

다대다 관계는 다중 값 필드가 생기거나 테이블에 대량의 중복 데이터가 포함될 수 있기 때문에 반드시 풀어주어야 합니다. 앞에서 언급하지는 않았지만 ‘주문 기록’ 테이블에 ‘주문된 상품들’ 필드 또한 다중값을 갖고 있었는데 이 또한 두 테이블이 다대다 관계를 갖고 있기 때문입니다.

다대다 관계는 연결 테이블을 이용해 일대다 다대일 관계로 풀어주었습니다.

변경 전	변경 후

식별 관계 vs 비식별 관계

식별 관계는 자식 테이블이 기본키로 부모키의 기본키를 포함하는 경우를 가르킵니다.

식별 관계는 항상 부모 테이블에 데이터가 먼저 존재해야 하므로 만약 자식 테이블에 데이터가 먼저 생성이 가능하도록 비즈니스 요구사항이 변경된다면 대응하기가 힘듭니다.

즉 중요한 기준 중 하나인 '유연성'을 만족시키지 못하므로 모든 관계를 비식별 관계로 가져갔습니다.

중간 ERD

현재까지 내용을 바탕으로 작성된 ERD는 다음과 같습니다.

기호 사용

1대1 관계이면서 최소 0개의 자식과 연관되어야 하는 경우

1대1 관계이면서 최소 1개의 자식과 연관되어야 하는 경우

1대다 관계이면서 최소 0개의 자식과 연관되어야 하는 경우

1대다 관계이면서 최소 1개의 자식과 연관되어야 하는 경우

ERD

문제점

~외 4종과 같은 상품들은 어떤 구조를 만들어서 처리할 것인가?

문제

마켓컬리를 보면 다음과 같은 상품이 있습니다.

하나의 대표 상품에 여러 상품이 묶인 형태입니다.
현재 저희 ‘상품’ 테이블의 구조로는 위와 같은 기능을 구현할 수 없습니다.

해결

‘전시용 상품’이라는 테이블을 새로 만들고 기존 ‘상품’ 테이블은 '실제 상품'이라는 이름으로 변경하였습니다. 그 후 '전시용 상품'과 '실제 상품' 일대다 관계를 맺어주었습니다. 유저에게 보이는 상품은 '전시용 상품'에 들어 있는 상품들이며 실제로 구매하게 되는 상품은 '실제 상품'에 있는 상품이 됩니다.

변경 전	변경 후

만약 카테고리가 3,4,5뎁스로 이어진다면 어떻게 할 것인가?

문제

현재 카테고리는 ‘전시용 상품’ 테이블에 ‘메인 카테고리’와 ‘서브 카테고리’로 2개가 존재합니다. 만약 상품이 늘어난다면 상품 하나당 카테고리가 3개 이상이 필요할 수 있습니다.

이 경우 현재 저희 테이블의 구조는 뎁스가 늘어날 때마다 상품 테이블에 새로운 필드가 추가되어야 하는 상황입니다. 또한 1차 설계에서 발견하지 못했지만 카테고리 데이터들이 상품 테이블에서 중복적으로 나타날 수 있었습니다.

해결

카테고리 테이블을 따로 뺀 후 하나의 카테고리가 부모 카테고리와 연관되도록 자가참조 관계를 맺어주었습니다.

이제 새로운 카테고리가 추가된다면 '카테고리' 테이블에 새로운 카테고리를 추가하기만 하면 됩니다. ‘전시용 상품’ 테이블에 있는 각각의 상품은 뎁스 최하위의 카테고리만 갖고 있으면 '부모 카테고리 id' 필드를 통하여 연관된 모든 카테고리를 찾아낼 수 있습니다.

‘1년 이상된 적립금은 만료’ 기능은 어떻게 구현할 것인가?

문제

현재 적립금에 관한 데이터는 '유저' 테이블과 '결제 기록' 테이블 안에 있습니다.

위 테이블을 이용하여 남아 있는 적립금과 적립금 내역은 어떻게든 알아낼 수 있지만 1년 이상된 적립금을 구분할 수가 없습니다.

해결

‘적립된 금액 기록’ 테이블을 따로 만들고 ‘적립된 양’, ‘적립금 잔액’, ‘적립금 만료 시점’를 기록하도록 하였습니다.

이제 1년 이상된 포인트를 만료시키는 것은 다음과 같이 구현할 수 있습니다.

• 적립금을 사용하면 가장 먼저 적립된 적립금부터 ‘적립금 잔액’에서 차감하게 됩니다.

• 매일마다 만료 일시를 넘어간 포인트의 ‘적립금 잔액’을 0원으로 만들어 줍니다.

• 유저가 사용 가능한 적립금은 ‘적립된 금액 기록’테이블에 있는 모든 레코드에 ‘적립금 잔액’ 값을 더해주면 됩니다.

예를 들어 2022년 6월 17일에 어떤 유저에게 500원이 적립되면 ‘적립된 금액 기록’ 테이블에는 다음과 같이 기록됩니다.

amount   balance   expired_at 

500      500       2023년 6월 17일

그리고 이 유저가 적립금 250원을 사용하면 이 레코드가 다음과 같이 업데이트 됩니다.

amount   balance   expired_at 

500      250       2023년 6월 17일

또한 적립금 사용 내역을 위해 '차감한 금액 기록' 테이블과 '차감한 금액 상세 기록' 테이블을 만들어 사용한 적립금을 기록해주었으며, '유저' 테이블에 있던 '적립금' 필드는 '적립된 금액 기록' 테이블을 이용하여 계산해 낼 수 있기 때문에 제거해주었습니다.

이 부분은 이 글을 참고하여 구성하였습니다.

부분 환불 기능이 생긴다면 어떻게 구현할 것인가?

문제

현재 저희 기획은 주문 취소만 가능하도록 되어 있습니다. 그리고 주문 취소는 배달이 시작되기 전에만 가능합니다.

이 경우 주문 취소가 일어날 경우 단순히 ‘주문 기록’ 테이블에 '주문 상태' 필드를 주문취소로 바꿔주면 됩니다. 취소 금액 또한 ‘결제 기록’ 테이블에서 이 주문에 맞는 ‘최종 금액’을 찾아주면 됩니다.

하지만 부분 환불 기능이 필요한 경우 문제는 복잡해집니다.

왜냐하면 부분 환불이 될 경우 결제한 금액과 환불 금액이 달라지게 되어 위에서 설명한 방법이 통하지 않고, 모든 상품이 환불되는 것이 아니기 때문에 어떤 상품이 실제로 얼마나 팔렸는지 알 수가 없습니다.

해결

‘환불 기록’이라는 테이블과 ‘환불된 상품’이라는 테이블을 따로 만들었습니다.

이제 주문 취소 또는 환불이 일어나면 '환불 기록' 테이블에 ‘총 환불 금액’을 기록하면 되고, 그에 따라 취소된 상품은 '환불된 상품'에 기록해주면 됩니다. 실제로 특정 상품이 팔린 상품 갯수를 확인하고 싶다면 ‘주문된 상품’에 있는 특정 상품에 ‘주문 수량’을 모두 더한 후에 ‘환불된 상품’ 테이블에서 이 상품에 대한 모든 ‘환불 수량’을 빼주는 식으로 구현할 수 있습니다.

정액 할인 정책에 대한 요구 사항이 생긴다면 어떻게 할 것인가?

문제

현재는 ‘실제 상품’ 테이블에 할인율이 그대로 포함되어 있습니다.

만약 정액 할인 정책이 생겨 특정 상품은 할인율이 아니라 할인 금액을 적어줄 필요가 있다면 ‘할인 금액’이라는 필드를 새로 추가해주어야 합니다. 할인 방식이 새롭게 추가될수록 '실제 상품' 테이블에 할인에 관한 필드가 늘어나고 테이블은 점점 '상품'과 '할인' 두 주제로 나뉘게 됩니다. 이는 이상적인 테이블 구조의 조건 중 하나인 '단일 주제를 나타낸다'는 특성을 위반하게 됩니다.

해결

'할인율' 필드를 '실제 상품' 테이블에서 제거하고 '할인'라는 테이블을 새로 만들었습니다. 이제 '실제 상품'테이블은 하나의 주제만을 갖게 되었으며 추가되는 여러가지 할인 정책은 '할인' 테이블을 통해 관리할 수 있습니다. 또한 '할인 금액'과 '할인율' 필드를 하나의 필드로 합쳐 NULL 데이터가 생길 수 있는 가능성을 차단했습니다. 최대한 NULLABLE한 필드를 만들지 않는 것은 데이터 무결성에 도움을 줄 수 있습니다.

정규화

테이블을 어느 정도 완성이 되면 정규화를 진행합니다.

제 1 정규형

테이블에 속한 모든 필드의 값이 원자 값으로만 이루어져야 한다.

→ 저희 테이블은 이상적인 테이블 구조에 따라 다중 값을 가진 테이블을 모두 해결해주었기 때문에 제 1 정규형을 만족합니다.

제 2 정규형

테이블이 제 1 정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제 2 정규형에 속한다.

테이블이 복합키를 기본키로 가질 때 기본키가 아닌 필드가 복합키 전체에 의존하는게 아니라 복합키를 구성하는 부분 필드에 의존하면 제 2 정규형을 만족하지 않습니다.

즉 제 2 정규형은 기본키가 복합키로 구성될 때 위반되는 경우가 나타나는데 저희 테이블은 모든 기본키가 인조키로써 단 하나의 필드만 포함하고 있기 때문에 이미 제2 정규형을 만족합니다.

보이스/코드 정규형

테이블의 함수 종속 관계에서 모든 결정자가 후보키이면 보이스/코드 정규형에 속한다.

보이스/코드 정규형을 만족하면 자연스럽게 제 3정규형을 만족합니다.

저 같은 경우에 제 3 정규형은 이해하기가 어렵고 또 테이블이 제 3 정규형을 위반하는지 구분이 어려워서 보이스/코드 정규형에 대한 체크를 바로 진행했습니다.

저희 테이블은 모든 테이블이 보이스/코드 정규형을 만족하고 있었습니다.

예를 들어 '유저' 테이블에 결정자는 'id', '로그인 아이디', '이메일', '전화번호'등이 있습니다.
이들은 모두 유일성과 최소성을 만족하기 때문에 후보키입니다.

규칙을 완화하거나 파괴할 시기

여기서 규칙이란 앞서 말한 이상적인 테이블 구조 또는 정규화 등을 말합니다.

테이블이 '사건'형 테이블인 경우

'사건'형 테이블은 역사적이고 시간 의존적인 데이터를 저장 및 기록하는 테이블을 말합니다.
저희는 '주문 기록', '결제 기록' 등이 '사건'형 테이블이라고 할 수 있습니다.
이 테이블의 특징은 다음과 같습니다.

각각의 레코드가 모두 독립적이다.

예를 들어 '주문 기록'테이블에 다음과 같은 레코드가 있다고 가정해보겠습니다.

payment_id   total_price   discount_price   final_price
1            10000         2000             8000
2            10000         2000             8000

1번과 2번 데이터는 정확하게 동일한 데이터를 갖고 있습니다. 하지만 이 데이터들이 동일한 데이터라고할 수 없는 이유는 모두 각각 다른 결제건에 해당하는 데이터이기 때문입니다. 이 경우 1번 데이터의 total_price가 8000원으로 수정된다고 해서 2번 테이블의 total_price를 8000원으로 수정해줄 필요가 없습니다. 즉 정규화를 수행해야하는 이유 중 하나인 수정 이상이 발생하지 않습니다.

테이블에 수정 또는 삭제가 거의 일어나지 않는다.

'유저'와 같은 '개체'형 테이블은 '비밀번호 변경', '회원 탈퇴' 등의 기능에 의해 수시로 수정 또는 삭제가 일어납니다.
반면 '주문 기록'과 같은 테이블의 레코드는 한 번 기록되면 수정 또는 삭제가 거의 일어나지 않습니다.
5000원에 팔렸던 상품의 가격이 3000원으로 내려갔다고 해서 5000원에 팔린 기록을 3000원에 팔린 기록으로 고치지 않는 것을 떠올려보면 됩니다.

앞서 저희는 이상적인 테이블 구조의 조건 중 하나인 '계산된 필드들을 포함하지 않는다'에 따라 '결제 기록'테이블에서 '최종 금액' 필드를 제거하였습니다. 하지만 이 '결제 기록' 테이블은 말씀드렸듯이 '사건'형 테이블이므로 규칙을 완화하는 것이 허용됩니다. 저희는 '50,000원 이상 결제한 내역을 찾기'와 같이 '최종 금액'이 필터의 조건으로 많이 활용되기 때문에 '최종 금액' 필드는 다시 '결제 기록'테이블로 복귀시켰습니다.

성능이 문제가 되는 경우

성능은 규칙을 깰 수 밖에 없는 되는 가장 일반적인 경우입니다. 이 경우는 테이블의 특성 때문에 반정규화 등이 크게 문제 일으키지 않는다는 '사건'형 테이블의 경우와는 다릅니다. 성능을 위한 반정규화가 진행되는 경우 반정규화가 진행됨에 따라 '일관성 없는 데이터', '중복 데이터', '부정확한 정보' 등 무결성에 관한 문제가 도입될 수 있습니다. 따라서 줄어든 무결성보다 성능 향상이 정말 가치있는지, 쉽게 말해서 '돈'을 더 벌어다 주는지 충분히 생각해서 진행해야 합니다.

저희는 '적립된 금액 기록' 테이블에서 이 문제를 마주하였습니다. 바로 남은 적립금을 매번 '적립금 잔액' 필드의 값들을 모두 더해서 보여주어야 하기 때문에 성능적으로 문제가 생길 수 있다는 것입니다. '유저'테이블에 '적립금' 필드를 다시 추가해서 남은 적립금을 기록하면 이 문제를 해결할 수 있습니다. 하지만 이 경우 이상적인 테이블 구조의 조건 중 하나인 '외래키 외에 불필요하게 중복되는 필드들을 포함하지 않는다.'라는 지킬 수 없게 됩니다. 이것은 적립금 사용이 일어날 때마다 '유저' 테이블의 '적립금'필드와 '적립된 금액 기록' 테이블의 '적립금 잔액'필드를 같이 업데이트해야 한다는 것을 의미합니다. 둘 중 하나라도 같이 업데이트가 안되면 데이터는 일관성이 없게 됩니다.

일단 이 문제는 저희의 예상이고 실제로 얼마나 이 성능이 문제가 될지는 테스트해봐야 알 수 있습니다. 따라서 이 부분은 추후 성능테스트를 진행하면서 실제로 문제가 될 때 고쳐볼 수 있는 포인트로 남겨두기로 하였습니다.

최종 ERD

여기로 오시면 더 자세히 보실 수 있습니다.

마치며

DB를 설계하는 것은 정말 어려웠습니다. DB 뿐만이 아니라 모든 설계가 다 마찬가지로 어려운 것 같습니다. 하지만 이 '돈'이라는 관점에서 모든 것을 바라본다면 설계에 대한 결정이 나름 수월해지지 않나 싶습니다.

설계는 실제로 운영을 시작하기 전까지 언제든 변경될 수 있으며 이 때까지는 변경에 대한 비용 또한 크지 않습니다. 지금 진행된 이 설계 또한 많이 변경될 것입니다.

그러므로 처음부터 완벽하게 설계하기 보다는 어느정도 설계해놓고 개발과 동시에 차근차근 진행하는 것도 좋은 방법이라고 생각합니다.

참고 자료

마이클 J. 헤르난데즈, 파워 오브 데이터베이스, 송현호, 황규용, 비제이퍼블릭, 2019
백은빈, 이성욱, Real MySQL 8.0 2권, 2쇄, 위키북스, 356-357, 2022