4-1. DB모델링 변화

이전 Posting에서 기존에 있던 sql문을 JPQL또는 JPA를 활용해서 변화하고자 하였는데, 진행하면서 DB모델링에 의구심이 들었고, 이를 해결하고자 했다.

1. 이전 설계의 문제점

구현하고자 하는 기능은 검색 기능이였고, 어떤식으로 검색을 하든 나와서 보여주어야 하는 결과값이 항상 같기 대문에 join문이 너무 많이 나갔다.

어떤 옵션으로 검색을 하든 나와야 하는 값은 json 형태로 표현하자면, 밑과 같다.

{	  
	"flowerName" : "장미",
	"season " : [ 봄 , 여름],
	"feature" :[
    		{
                    "color" : "빨강"
                    "description" : "사랑과 정열"
                    "keyword" : ["사랑", "정열"]
                    "url" : {red_rose.jpg}
                   },
                  {
                    "color" : "노랑"
                    "description" : "증오하는 사랑"
                    "keyword" : ["사랑", "증오"]
                    "url" : {yellow_rose.jpg}
                   }]
}

그래서 위의 테이블 모델을 사용하게 되면 모든 data를 가져오기 위해서Flower와 Season, Keyword, Color를 모두 join 하여야 하고, join하기 위한 연결테이블들도 모두 사용해야하기 때문에 매우 비효율적이라고 생각했다. 

'Join이 너무 많으니 이를 줄여보자'

Join을 줄이기 전에 과연 내가 한 정규화가 제대로된 정규화였을까 고민하면서 다시 진행해보았다.

2. 새로운 설계

정규화란 무엇이고 왜하는걸까?

이상현상 : 삽입 이상, 삭제 이상, 갱신 이상 과 같은 현상을 해결하기 위한 정규화

정규화란?

 중복요소를 찾아 제거해 나가는 과정.

 중복을 최소화하지만, 완전히 없애는 게 아니라 어느 정도 인정하겠다.

2-1. 다시 정규화해보기

(예시를 위한 데이터로 올바르지 않을 수 있습니다.)

- 비정규화

꽃 이름	계절	색상	url	꽃말	키워드
장미	봄,여름	빨강	red_rose.jpg	열렬한 사랑	사랑
장미	봄, 여름	노랑	yellow_rose.jpg	질투, 시기, 완벽한 성공,	증오, 성공
장미	봄, 여름	파랑	blue_rose.jpg	불가능의 극복, 포기하지 않는 사랑	성공,사랑
튤립	여름,가을,겨울	빨강	red_tulip.jpg	정렬적인 사랑	사랑

- 1 정규화

1정규화는 데이터베이스에서 atomic(원자성)을 유지시켜주어야 합니다.

테이블의 컬럼이 원자값을 갖도록 테이블을 분해해야합니다.

분해하기 위해서 같은 성격과 내용이 연속적으로 존재하는 컬럼에 대해 해당 컬럼을 제거하고 기본테이블의 PK를 추가하여 기존의 테이블과 1:N의 관계를 형성하는 방법으로 진행합니다.

 

반복적으로 나타나는 컬럼인 "계절"과 "키워드"를 볼 수 있습니다.

이 때 계절은 "꽃 이름"을 키로 가지고 있습니다.

키워드의 경우 꽃말에 종속적인데, 꽃말은 { "꽃 이름", "색상"}으로 정해지고 있습니다.

 

하나의 계절은 여러개의 계절을 가진다. 여러개의 계절은 여러 꽃을 가진다. 

꽃이름 <- > 계절 간에 M:N관계가 되고, 이를 표현하기 위해 연결 테이블을 둡니다.

 

키워드는 여러 꽃말을 가지고, 꽃말을 여러 키워드를 가진다.

꽃말을 특정하는 {꽃 이름 , 색상} <-> 키워드 간 M:N관계가 되고 이를 표현하기 위해 연결테이블을 둡니다.

중복 된 Season ,Keyword table의 분리

남겨진 테이블

- 2 정규화

2 정규화는 PK가 여러개로 구성된 복합키(Composite Key)로 구성된 경우 2차 정규화의 대상이 됩니다.

복합키 전체에 의존하지 않고 복합키 일부분에만 종속되는 속성들이 존재할 경우(부분적 종속 관계) 이를 분리 하는 것입니다. 

위의 테이블에서는 그러한 부분이 없어 2정규화는 진행하지 않습니다. 

2정규화의 예시는

https://mangkyu.tistory.com/110 2정규화 예시

{학생번호, 강좌이름}을 복합키로 가지는 "수강 강좌" 테이블에서 강의실은 {강좌 이름}에만 종속적입니다.

복합키 일부분인 강좌이름으로 결정되는 강의실을 분리해냅니다.

- 3 정규화

제 2정규화를 진행한 테이블에 대해 이행적 종속을 없애도록 테이블을 분해하는 것입니다.

이행적 종속이란 A->B, B->C가 성립될때 A->C가 성립되는 것을 의미합니다.

즉, 테이블의 키가 아닌 컬럼들은 기본키에 의존하여야 하는데 겉으로 그런 것 처럼 보이지만 실제로 기본키가 아닌 다른 일반 컬럼에 의존하는 컬럼 (이행적 함수 종속 관계)을 분해해냅니다.

 

해당 테이블에는 그러한 점이 없기 때문에 넘어갑니다.

3정규화의 예시는 다음과 같습니다.

 2정규화를 마친 위의 테이블에서 노란색으로 색칠된 영역을 보게되면, 

author_id, name, profile은 모두 author_id에 의존하고 있습니다.

title -> author id -> {author name, author_profile} 과 같이 A->B B->C를 가리키는 형태를 이행적 종속관계라고 합니다.

저 레코드들은 TOPIC에서 정한 PK title에 의존적이지 않기 때문에 

Author라는 테이블을 두고 분리해 냅니다.

 

Q. 분리된 Topic 테이블의 author_id가 없다면?
topic의 테이블은 
[ title, description, created, author_name, author_profile]
과 같이 이루어지고, id가 없다면, author_name과, author_profile이 중복적으로 계속 해서 나타나게 될 것입니다.
결국 중복은 그대로 존재하기 때문에, author id를 따로 두고, 외래키로 가져오는 방법과 같이 테이블을 분리할 수 있습니다.

 

- BNCF 정규화 는 접은 글을 통해서 확인

BCNF 정규화란 제 3정규화를 진행한 테이블에 대해 모든 결정자가 후보키가 되도록 테이블을 분해하는 것.

 

다음과 같은 테이블에서 기본키는 {학생번호, 특강이름}으로 구성된 복합키로 이루어져 있다.

여기서 {학생 번호, 특강 이름}으로 교수를 특정할 수 있지만, 

교수 또한 특강이름을 결정하고 있다

(여기서 잠깐, 여기서 교수가 특강을 결정할 수 있어야 하기 때문에 교수는 특강을 하나만 강의한다는 정책또는 가정하여야 한다.)

교수를 결정자로 하는 테이블로 하나를 분리해 낸다.

 

비슷한데 그런데 왜..?

정규화를 진행하고 나서도 생각이 많아졌습니다. 사실상 1정규화를 통해서 거의 정규화가 완전히 된 상태 였지만, 기본적으로 테이블이 5개나 되었으니까요. 

또한 테이블의 개수와 상관없이도 나타나는 여러 중복들과

DB 모델링과 Entity 모델링 사이에서의 괴리에서 고민이 많았습니다.

 

생각해볼 문제

0. Entity 와 Value 그리고 기능(정책상)

먼저 기능적으로 보았을 때 옵션을 버튼을 통해 제공해주고, 정해진 범위에서의 버튼이 눌렸을 때 이를 활용하여 검색을 하기 때문에 옵션이 정해져있습니다.

Color나 Keyword나 FIX된 값들입니다. 어떤 코드 테이블처럼 정해져 있는 값으로 구분이 되는 값들입니다.

결론) 이 고정된 값들에 대해서 테이블을 빼내어서 코드 테이블처럼 활용할 것인가? 아니면 그냥 값으로만 생각하고 사용할 것인가에 대한 문제 

 

1. Flower

Flower라는 객체 하나를 두고 보았을 때 위의 json형태처럼 Color나 Keyword와 같은 값들은 Flower에 List로써 특성으로 나타내야한다. 라는 생각이 들었습니다. 

public class Flower{
    private String flowerName;
    private List<FlowerFeature> features;
}

public class FlowerFeature{
    private String color;
    private string url;
    private string description; //꽃말
    private List<String> keywords; //키워드
}

하지만, DB에서는 FlowerName과 Color가 결정자다 보니 JPA로 이를 매핑하는 과정에서 어려움이 있었습니다.

 

예를들어 만약에 꽃 이름으로 결정지어지는 부분이 있다고 가정해봅시다.

빨강 장미든, 노랑 장미든 장미 하면 기본적으로 키우는 난이도가 같다. 라고 가정하고 테이블을 추가하게 되면

객체에서는 Flower class에 추가하기만 하면 끝이나지만, DB의 경우 중복적으로 들어가고, 복합키에서 일부 키가 결정하는 완전 함수 종속을 위해 정규화를 진행해야하는 문제점이 생깁니다.

 

결론) 객체 모델링과 DB모델링에서 오는 괴리감.

 

 

해결 방법에 대한 고찰

해결방법을 생각하면서 고려해야할 점은 

1. JPA와 mapping을 어떻게하지를 생각

2. Join이 최대한 들어가지 않도록 설계

3. 분리되어 있는 옵션 검색을 하나의 검색 페이지로 변환해보자.

 

 

1. Entity 와 Value타입  (feat. 검색)

먼저 검색을 할 때 사용할 sql에 대해서 생각을 해 보았을 때 각 조건에 따라 다음과 같은 형식을 취하면 좋겠다고 생각하면서 고려했습니다.

select * from [ MODEL] 
where name LIKE %searchName%
 AND Color IN ('red', 'white' ..)
 AND keyword in('keyword1', 'keyword2')

 

이 고정된 값들에 대해서 테이블을 빼내어서 코드 테이블처럼 활용할 것인가? 아니면 그냥 값으로만 생각하고 사용할 것인가에 대한 문제를 해결해보고자 검색을 하면서 몇가지 생각하고 알게 된 정보들이 있었습니다.

 

 - 코드 테이블

    코드 테이블의 경우 코드를 통해 어떠한 상태를 특정할 수 있고 반복적으로 나타나는 값들에 대해서 테이블을 두고 활용합니다. 배송이라는 테이블에서 배송 상태를 나타내기 위한 0:배송 전 1 :배송 중 2 : 배송 완료 와 같이 어떠한 상태를 하나로 특정할 수 있어야 하는데, Color와 Keyword의 경우 상태를 특정한다기 보다는, 하나의 특징으로써 역할을 하고 또 여러 특징을 가질 수 있기 때문에 코드 테이블은 맞지 않다고 생각했습니다.

 

 - hashTag활용 테이블 설계

  검색을 할 때 어떠한 옵션들이 줄줄이 들어오기 때문에 이들을 hashTag를 통해 검색하는 방법에 대해 알아보았습니다.

어떠한 글이 있을 때 #을 통해 다중 Tag를 가지게 되고 이를 활용해 검색하기 위해서 Tag 테이블을 설계하는 방식이 있습니다. 

SELECT * FROM items WHERE id IN  
    (SELECT DISTINCT item_id FROM item_tag WHERE  
    tag_id = tag1 OR tag_id = tag2 OR ...) 

오래된 article이지만 해당 글을 보며 고려해보았습니다.

http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/

Tags: Database schemas

hasthTag를 이용한 설계도 여러 방법이 존재할 수 있었습니다.

 1. 역정규화를 활용하여 Tag를 안에 다 넣는 방법 

     Keyword에 delimitor를 두고 검색 시 활용 하는 방법으로 

     위의 FlowerTable에 keyword 컬럼을 추가하고 거기다가 [ 사랑 | 정열 ] 과 같이 넣는 방법

 2. Entity 마다 entity PK 를 FK로 하는 tag 테이블을 추가하는 방법

 

 

3. 태그 맵을 활용하는 방법

 

  나도 모르게 3번 태그맵을 활용하는 방법으로 이전의 모델링을 했었었다는 것을 알았고,

 tag의 종류를 나누는 flag 를 추가하여 통합 tag를 만들 수 있었지만, 

객체의 입장에서 자신의 속성을 Tag로 가지는 것이 말이 안될 뿐더러 Color는 복합키였기 때문에 Tag를 사용하는 방법에 대해서도 꺼려졌습니다.

 

- 쇼핑몰에서는 옵션을 어떻게 설정하지?

쇼핑몰에서는 옵션테이블을 쓰지않는 것이 대부분이라고 하고 상품테이블에 옵션 필드를 추가하라고 되어있었다.

옵션1 > 색상 옵션2 > 노랑/파랑/빨강/보라 과 같은 형태를 가지고 있고, 이를 컬럼으로 넣으라는 것인데..

테이블 자체가 거의 불변이기 때문에 역정규화하여 사용하여도 충분히 괜찮다고 생각했지만,

Color의 경우 복합키로써 사용되기 때문에 되지 않고, Keyword의 경우 몇개가 올지 모르기 떄문에 컬럼을 많이 추가한다는 것이 데이터 낭비가 심할 것이라고 생각했다.

 

비슷한 아티클로 https://yangeok.github.io/node.js/2019/01/22/sql-array.html 해당 글을 참조하였었는데,

속성마다 테이블을 따로 분리한 후에 각 테이블에 그냥 값을 집어넣었다

 

 - Enum

문제는 M:N의 문제를 연결테이블을 통해 해결한다는 점에서 join이 많이 이루어진다는 점과

내가 지금 다루려고 하는 color나 keyword의 크기가 크지 않음에도 굳이 table을 하나 만들어야 하나 하는 고민이 생기면서 여러 글들을 읽으면서 생각했을 때 , https://tech.socarcorp.kr/dev/2021/08/17/common-code-management.html#db%EA%B0%80-%EC%82%AC%EB%9D%BC%EC%A1%8C%EC%9C%BC%EB%8B%88-kotlin-%EC%BD%94%EB%93%9C%EB%A5%BC-%EB%B6%84%EC%84%9D%ED%95%B4%EC%84%9C-javascript-%EC%BD%94%EB%93%9C%EB%A5%BC-%EC%83%9D%EC%84%B1%ED%95%B4-%EB%B4%85%EC%8B%9C%EB%8B%A4

쏘카 백오피스 팀 내 공통 코드(Common Code) 관리 변천사

해당 글을 읽으면서 '정답' 이 없다라는 것에 다시 한 번 '근거가 있다면 내가 하는게 일단 맞다고 확신하자'라는 생각을 다시 잡게 된 것 같다. 더해서 Enum을 활용하자라는 생각과 Web에서 넘어오는 값에 대해 어떻게 처리하지 라는 생각으로 확장 시킬 수 있었다. 공통코드의 내용이였지만, Color의 부분을 복합키로 통합하는 것으로 마음 먹은 시점에서 해당 글을 보았을 때 DB에 VARCAHR로 저장하고 Enum으로 관리하자라는 것에 착안해서 활용하기로 하였다.

 

  이후에  DB에 enum을 쓸수 없을까 생각했는데 https://velog.io/@leejh3224/%EB%B2%88%EC%97%AD-MySQL%EC%9D%98-ENUM-%ED%83%80%EC%9E%85%EC%9D%84-%EC%82%AC%EC%9A%A9%ED%95%98%EC%A7%80-%EB%A7%90%EC%95%84%EC%95%BC-%ED%95%A0-8%EA%B0%80%EC%A7%80-%EC%9D%B4%EC%9C%A0 해당글을 읽고 DB에서는 VARCHAR를 사용하기로 결정했다.

[번역] MySQL의 ENUM 타입을 사용하지 말아야 할 8가지 이유

 

결정) 

코드나 DB설계에만 집중하다보니 온갖생각이 들었는데 결국 정책적으로 정할 것들이 필요했다.

일단 Color와 관련하여 꽃은 무조건 색상을 하나 이상 가진다. 

장미에도 빨간 장미, 노란 장미 등등이 있지만 이들을 각각의 하나의 장미로 보기로 했다.

꽃을 결정하는 것으로 색상과 이름 복합키를 활용한다.

 

Entity가 아니라 Value로 생각하는 것이 맞고, 코드테이블이 아닌 Enum으로 활용하자.

단 Application 단에서 색상에 관련된 부분은 Enum으로 정해두고 처리한다.

Table로 빼지 않고 Enum으로 관리하겠다. 

 

Keyword에 대한 부분 또한 추가되는 부분이 없다면, 충분히 위와 같이 사용가능하지만, 추가될 가능성이 높은 tag와 같은 경우라면 tagTable을 분리하는 것이 맞다. 하지만, 바뀌지 않는다고 가정한다.

 

원래 구현하려고 했던 사이트에 집중해보았을 때 글을 통해 자연어 처리를 통해 mapping 된 정해진 keyword 안에서 추천해주는 것이 목표였기 때문에 이를 감안했을 때, 변화할 가능성이 크지 않다고 판단한다.

 

 

Color와 keyword 를 고려하는데 생각이 너무 많았다.

- Entity도 아니고 특성이자 어떤 값 타입인데 굳이 table로 빼야하는가?

- 중복된 값들이 너무 많고 활용하려면 그래도 빼야하지 않을까?

- 빼게 되면 join이 너무 많아지는데? 그리고 단순히 이름만 있는데?

- 객체적으로 Feature을 가지게 하고 싶은데?

결국 중복되는 값들에 대해서는 Application 단에서 Enum으로 관리하고

인덱스 관리는 DB에서 복합키로 처리하는게 맞다고 판단이 들었다.

 

일단, Join을 가장 줄이고 싶다는 생각과 객체에 맞추어 만들고 고려하고 싶다는 생각이 컸기 때문에

테이블 수를 최대한 줄이고 관리하기 위해서는 가장 깔끔한 점은 꽃 자체를 '색상과 꽃 이름'으로 구분하게 하는 것이 맞다고 생각했다. 꽃만이 가지는 어떤 값이 있다면 역정규화라고 부를 수 있겠지만, 그건 아니라 애매하긴 하다.

 

그리고 실제로 꽃만이 가지는 어떠한 특정한 값을 추가할 일 없는 read-only table이고 읽기가 빨라야하기 때문에 가능하다고 생각했다.

 

+ JPA Jointable

JPA에서 제공하는 ManyToMany나 Jointable을 최대한 활용하지 않으려고 했다.

새로운 테이블이 하나 생기는 것이기 때문에 설계 과정에서 많이 고민하고 설계한대로 최대한 맞추어 보자라는 개인적인 생각이 있었고 결국 Join을 해야한다는 점에서 사용하고 싶지 않았다.

https://data-make.tistory.com/613

[JPA] 고급 매핑(상속관계, 복합키, 식별/비식별, 조인 테이블)

[JPA] 고급 매핑(상속관계, 복합키, 식별/비식별, 조인 테이블)

@join 에 대한 내용.

 

 

 

정리

테이블과 데이터의 특징 그리고 하려는 일의 괴리감. 

일단 다루고 있는 주제가 꽃말이다 보니, 문화적, 향토적 느낌이 강하여 굳이 이렇게 까지 설계를 해가면서 만들어야 하는가? 하는 생각이 들었습니다. 더욱이 해당 테이블은 Insert가 자주 일어나지 않는 고정된 테이블 값으로 한번 결정되고 나면 READ_ONLY 로 활용될 테이블이었습니다. 한번 정해지면 바뀔이유가 없는 데이터들이기 때문이죠.

그래서 Insert에 대해 고려할 필요가 있나? 라는 생각도 발목을 많이 잡았습니다. 

 

read만 하면 되서 데이터 일단 sql로 넣어두기만 하면 되는데, 그냥 큰 테이블에 다 담아두면 편하지 않나 라는 생각이 컸는데, 하고자 하는 게 CRUD를 모두 해보고 싶었고, select 를 통해 빠르게 조회하고, JPA를 활용하여 객체 지향적으로 만들어보자 라는 게 컸기 떄문에 아무래도 하면서도 왜하는가 하는 의문이 많이 들었지만, 꽃이 아니라 충분히 비슷한 사례가 나올 수 있다는 점이 계속 붙잡아서 해보고 싶다는 생각이 너무 강했던 것 같았습니다.

 

 

출처

 

꽃말 정보 관련

https://ko.wikipedia.org/wiki/%EC%8B%9D%EB%AC%BC_%EC%83%81%EC%A7%95%EC%A3%BC%EC%9D%98

 

hashTag 관련

http://daplus.net/database-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4%EC%97%90-%ED%83%9C%EA%B7%B8%EB%A5%BC-%EC%A0%80%EC%9E%A5%ED%95%98%EB%8A%94-%EA%B0%80%EC%9E%A5-%ED%9A%A8%EC%9C%A8%EC%A0%81%EC%9D%B8/

 

DB모델링 및 정규화 

https://mangkyu.tistory.com/28

[Database] 7. 정규화(Normalization)

https://inpa.tistory.com/entry/DB-%F0%9F%93%9A-%EC%A0%9C-1-2-3-%EC%A0%95%EA%B7%9C%ED%99%94-%EC%97%AD%EC%A0%95%EA%B7%9C%ED%99%94

[DB] 📚 제 1-2-3 정규화 & 역정규화 💯 정리