[taka] db 설계, erd 작성

서론

프로젝트 소개

학생회, 동아리 등에서 대여사업을 진행할 때 주로 오픈카톡, 네이버 폼을 이용해서 진행하는 경우가 많은데 상당히 불편해보였다. taka는 여러 서비스를 동시에 이용하며 대여사업을 진행하는 대신 하나의 서비스를 이용해 대어 사업을 관리할 수 있는 모바일 웹 서비스이다. (* 원래는 프론트까지 만들어서 실제 사용을 해보고 싶었는데... 오랜만에 리액트 켜서 화면 하나 만들다가 일단 서버개발에 집중해야겠다는 생각이 ...;;) 이름은 왜 taka라고 지었냐고 물어보시면... 별 뜻 없다. 그냥 리포지토리 새로 팔 때 듣고있던 노래 제목이 taka였다. ㅋㅋ

 

굉장히 정신 없는 노래이다

 

요구사항 분석

 

db설계를 하려면 요구사항을 면밀하게 분석해야 한다. 

먼저 필요한 기능들을 글로 정리해보았다. 분석한 요구사항들을 바탕으로 엔티티간의 관계를 도출하고자 한다.  

 

 

좋은 db 설계란?

특히 db, 엔티티의 설계를 고치게 된다면 많은 부분에서 수정사항이 생길 것이기 때문에 신중하게 설계해야 했다. (실제로 개발 과정에서 조금씩 변경사항이 있었는데 테이블 드랍했다가 다시 만들어야되고 그 과정에서 꼬이고 하는 경우도 있어서 번거로웠다. 지금이야 토이프로젝트 단계에서 하는거라 괜찮지만 실제 서비스에서 일어난 일이라고 생각하면 아찔 ;;) 잘못된 db 설계는 문제 발생, db 수정에 많은 비용이 들어가게 된다. 

 

따라서 좋은 db 설계는 다음과 같은 세 가지 특징을 갖추어야 한다. : 

1) 무결성 - db 내 값이 정확한 값 유지해야 함   

2) 유연성 - 요구사항 변화에 대해 수정이 쉬워야 함 

3) 확장성 - 기능 확장이 쉬워야 함  

 

---

테이블 설계

자연키 vs 인조키

다른 테이블의 경우 기본키로 지정할 필드가 크게 없어서 인조키로 id를 생성하여 기본키를 설정해 주었지만, 

user 테이블의 경우 기본 아이디로 사용하고 있었던 이메일을 기본키로 설정해도 되지 않나라는 생각이 들었다. 

 

자연키를 기본키로 설정한다면 기본 키가 클러스터링 인덱스가 되므로, 자연키를 위해 추가적인 인덱스를 구성할 필요가 없다.
또한 자연키를 기본 키로 설정하면 해당 키와 관련된 쿼리가 커버링 인덱스를 활용할 가능성이 높아져서 쿼리 성능을 크게 향상시킬 수 있다.

 

하지만 이는 비즈니스 요구사항이 변경되거나 (ex 이메일이 더 이상 사용되지 않는다거나 등) 기본키 자체가 변하는 경우 (ex 교내 이메일 정책이 바뀌어서 주소가 달라지거나 등) 기본키 자체를 변경해야 하는 문제점이 생기기 때문에 인조키를 사용하여 db가 유연성을 갖출 수 있게 하였다. 

 

---

필드 설계

테이블 구성 이후에는 각 필드 별 데이터 종류, 데이터 길이, unique 특성, null / notnull 등을 설정해주어야 한다.

CHAR vs VARCHAR 

CHAR과 VARCHAR은 둘 다 문자를 저장할 수 있는 컬럼이지만, 길이의 유연성/ 가변성 에서 차이가 있다.

 

CHAR 타입은 고정 길이 문자열을 저장하기 때문에 테이블의 행 크기가 일정하고, 지정된 길이만큼의 공간을 항상 차지한다. 따라서 지정된 길이보다 짧은 문자열이 저장될 경우 나머지 공간은 공백으로 채워지는 형태인데 - 예를 들어  CHAR(10)인 필드가 있는 경우 여기에  abc라는 문자열을 저장하면 7자리가 그대로 남아있게 된다. 이는 따라서 실제 저장된 데이터보다 더 많은 공간을 차지할 수 있다는 문제점이 있다. 

반면 VARCHAR 타입은 가변 길이 문자열을 저장하고, 추가적인 1~2 바이트를 사용해 문자열의 길이를 저장하므로 저장 공간을 좀 더 효율적으로 사용할 수 있다. 예를 들어 VARCHAR(10) 필드에 abc를 저장한다면 글자수인 3을 포함하여 3abc 라고 저장해 4자리만 사용하게 되는 것이다. 

 

따라서 저장되는 문자열의 크기가 고정적이거나 변동이 크지 않은 경우, 또한 특히 인덱스 된 컬럼에서 자주 변경이 일어나는 경우에는 CHAR을 사용하는 것이 더 효율적이며 이러한 경우 VARCHAR 사용 시 데이터 페이지 내부의 조각화 현상이 더욱 많이 일어나는데다가 공간 효율 또한 떨어진다. 하지만 문자열 길이의 변화 폭이 크거나 일반적인 경우에는 VARCHAR을 사용하는 것이 더 효율적이다. 즉, 이는 조회 성능과 수정, 삽입, 삭제 성능 중 무엇을 더 우선시 할까의 차이이며 수정보다는 조회가 훨씬 더 많이 일어날 것으로 예상되어 CHAR보다는 VARCHAR을 많이 사용하는 것이 더 나을 것이라고 판단하였다. 

 

VARCHAR vs TEXT

데이터의 크기가 10글자 이하인 경우, VARCHAR(10)과 VARCHAR(1000)의 차이는 무엇일까? 오히려 VARCHAR(1000)으로 하는 것이 db의 유연성 측면에서 더욱 효율적이라고 할 수 있지 않을까? 

 

다음과 같이 테이블을 생성한 경우를 가정해보자. 이러한 모델링은 좋은 모델링일까 나쁜 모델링일까? 

CREATE TABLE user (
  id       BIGINT NOT NULL,
  name     VARCHAR(1000),
  phone_number VARCHAR(1000),
  email  VARCHAR(1000),
  PRIMARY KEY(id)
);

 

이는 VARCHAR 컬럼이 큰 길이를 사용하는 경우 어떤 일이 일어나는지에 대해서 알아야 한다. 

mysql> CREATE TABLE tb_long_varchar (id INT PRIMARY KEY, fd1 VARCHAR(1000000));
ERROR 1074 (42000): Column length too big for column 'fd1' (max = 16383); use BLOB or TEXT instead

mysql> CREATE TABLE tb_long_varchar (id INT PRIMARY KEY, fd1 VARCHAR(16383));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE tb_long_varchar (id INT PRIMARY KEY, fd VARCHAR(16382));
Query OK, 0 rows affected (0.19 sec)

mysql> ALTER TABLE tb_long_varchar ADD fd2 VARCHAR(10);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

 

이는 컬럼들의 최대 길이가 65535 바이트를 추가한 경우 더 이상 새로운 컬럼 추가가 불가능하다는 것을 보여준다. (에러 1118)

즉, MySQL 서버에는 레코드 사이즈 한계가 존재하기 때문에 VARCHAR 타입의 최대 저장 길이를 설정할 때 공간을 무한정 크게 설정하면 안된다는 것을 보여준다. 

CREATE TABLE user (
  id       BIGINT NOT NULL,
  name     TEXT,
  phone_number TEXT,
  email    TEXT,
  PRIMARY KEY(id)
);

그렇다면 VARCHAR 대신 TEXT를 사용하면 저장 가능 길이도 훨씬 크고, 테이블 생성 시 길이 제한도 없으니 더 좋지 않을까? 

하지만 왜 주로 문자열 데이터 저장 시 TEXT 대신 VARCHAR을 쓸까?

 

VARCHAR 타입은 가변 길이 문자열을 저장하며, 작은 데이터는 기본 페이지에 인라인으로 저장된다. 인라인은 테이블의 기본 저장 페이지에 직접 데이터를 저장하는 것을 의미하며, 이는 기본 페이지의 크기 내에서 메모리를 효율적으로 사용할 수 있다. 데이터가 기본 페이지에 맞지 않을 정도로 커지면, VARCHAR 데이터는 off-page로 이동되어 저장된다. off-page 저장은 데이터가 테이블의 기본 페이지 외부에 별도의 페이지에 저장되는 방식으로, 기본 페이지에는 데이터가 저장된 위치를 가리키는 포인터만 남게 된다.

반면 TEXT 타입은 대용량 문자열 데이터를 저장하는 데 사용되며, 데이터가 작더라도 일반적으로 off-page로 저장이 된다. 즉 이는 TEXT 타입의 데이터 접근 시마다 메모리를 할당하고 해제해야 한다는 것을 뜻하며 빈번한 메모리 할당과 해제가 발생하여 성능 오버헤드가 생길 수 있다. 

따라서 VARCHAR는 데이터 크기가 예측 가능하고 상대적으로 작은 경우, 해당 컬럼이 자주 필요한 경우에 적합하고, TEXT 컬럼은 데이터 크기가 크거나 변동이 심한 경우, 해당 컬럼이 많이 필요하지 않은 경우 적합하다. 

 

DATETIME VS TIMESTAMP

TIMESTAMP는 시간을 UTC로 저장하기 때문에 내가 어느 국가에 있느냐에 따라서 해당 국가에 맞는 시간으로 볼 수가 있다. 예를 들어 한국에서 7월 14일 16시에 작성된 데이터라고 하면 영국에서는 7월 14일 08시에 저장된 데이터로 조회가 되는 것이다. UTC로 저장이 되기 때문에 수동으로 시간을 변환해야 하는 DATETIME과 달리 MySQL에서 이를 자동으로 변환해 준다. 따라서 사용되는 모든 시간들은 TIMESTAMP를 이용하기로 했다. 

 

 

---

ERD 작성

다음은 위에서 정의한 관계를 바탕으로 각 엔티티의 필드와 자료형을 정의하여 ERD 다이어그램을 구성해야 한다. 

 

관계 설정

* 한 명의 회원은 여러 단체에 속할 수 있다. 또한 하나의 단체는 여러 회원을 포함할 수 있다. (다대다 관계)

* 하나의 단체는 여러 개의 대여 물품을 가질 수 있다. (일대다 관계)

* 한 명의 회원은 여러 개의 물품을 대여할 수 있다. 하나의 물품 또한 동시에는 아니지만 여러 명의 회원들에게 대여될 수 있다. (다대다 관계) 

 

다대다 관계 = 일대다 + 다대일 ?

하지만 다대다 관계를 @ManyToMany로 양방향 매핑 해주는 것은 옳지 않으며, 중간 테이블을 둬서 일대다 - 다대일 연관관계로 풀어주어야 한다. 왜냐하면 한 유저 A가 동아리 X에 가입을 하는 상황을 가정했을 때, A가 X에 가입한 정확한 시간과 같은 추가적인 컬럼을 다대다 매핑에서는 기록할 수 없기 때문이다. 

 

 

따라서 회원과 단체 사이에 멤버쉽 테이블을, 회원과 대여물품 사이에 대여기록 테이블을 두어서 다대다 관계를 일대다 - 다대일 관계로 풀어주었고 조회 시 더욱 용이하게 하였다. 

 

---

문제점

한 org에 대해 관리자가 여러 명인 경우 관리자 테이블을 따로 두어야 할까?

원래는 org entity에 대해 관리자 테이블을 따로 두어 해당 단체의 관리자 목록을 관리하려고 했는데, 그렇게 하면 테이블을 또 생성해서 중복되는 항목에 대해 관리를 해야된다는 단점이 있다. 

 

대신 멤버십 테이블에 isAdmin이라는 boolean 필드를 추가하여 해당 단체에 대해 관리자인지 아닌지를 판별하고자 했다. 

 

예를 들어 X라는 유저가 동아리 A,B의 회원이라고 하자. 이 때 X는 A에서는 관리자이지만 B에서는 일반 회원이다.

그러면 X는 user_entity에서는 권한이 admin으로 들어가지만, 

A-X의 멤버십 엔티티에서는 isAdmin이 true가 될 것이고 B-X의 멤버십 엔티티에서는 isAdmin이 false가 될 것이다.

이렇게 하면 추가적 테이블 생성 없이 특정 단체에 대한 관리자가 따로 설정 가능할 것이라고 생각했다. 

 

---

최종 ERD

 

생각보다 복잡했다 .... 빨리 다음학기에 db 수업을 들어야 할 것 같다 ;;

 

---

참고 자료

https://youtu.be/SdmQQPKnQRg?si=SVaAT1bMcM70qkGQ

 

https://medium.com/daangn/varchar-vs-text-230a718a22a1

VARCHAR vs TEXT

https://medium.com/daangn/json-vs-text-c2c1448b8b1f

JSON vs TEXT

https://velog.io/@sontulip/how-to-db-design#%EC%A0%95%EA%B7%9C%ED%99%94

DB 설계는 어떻게 해야 할까?

 

https://techblog.woowahan.com/2527/

Java Enum 활용기 | 우아한형제들 기술블로그

https://dev-rosiepoise.tistory.com/104

[springboot / 게시판 API] DB 설계 v0.5 - ERD 작성