생활코딩 - 관계형 데이터 모델링 학습 정리

https://edu.goorm.io/learn/lecture/14442/%EC%83%9D%ED%99%9C%EC%BD%94%EB%94%A9-%EA%B4%80%EA%B3%84%ED%98%95-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%AA%A8%EB%8D%B8%EB%A7%81

생활코딩 - 관계형 데이터 모델링 - 구름EDU

1. 데이터 모델링의 순서

    1) 전체 흐름

        1-1) 업무파악 : 의뢰한 사람의 요구사항 / 기획서 정리

        1-2) 개념적 데이터 모델링 : ER 다이어그램 작성

        1-3) 논리적 데이터 모델링 : 표로 구현함

        1-4) 물리적 데이터 모델링 : 코드로 작성함 (SQL Code)

 

2. 업무파악

    1) 말보다 시각적인 자료를 기반으로 접근

        1-1) 기획서를 작성하자 (엑셀, figma etc)

 

3. 개념적 데이터 모델링

    1) ERD 개념

        1-1) 데이터의 관계를 정리한다. 중복되는 데이터인지, 포함, 교차 등의 성질을 가지고 있는지 정리함

        1-2) 데이터를 거대한 단일 테이블로 표현하면 중복이 발생함 

        1-3) 중복으로 발생하는 데이터는 적절히 분리되어 다른 테이블로 관리되어야 함

        1-4) 분리된 테이블들은 join으로 하나의 테이블로 결합될 수 있음 

    2) ERD 개념

        1-1) 속성(Attribute) -> 엔티티(Entity) -> 릴레이션(Relation)

    3) 식별자

        1-1) 데이터를 식별할 수 있는 정보는 차후 Key가 된다. 예) user_id, email, 주민등록번호, 핸드폰 번호 etc

        1-2) 데이터 내용 중 유니크한, 식별한 값이 없는 경우 인위적인 키를 만들어 준다.

    4) 표기법

        1-1) 1:1

        1-2) 1:N

        1-3) N:M

        1-4) Mandatory(필수):Optional(옵션), 1:1

        1-5) Mandatory(필수):Optional(옵션), 1:N

 

4. 논리적 데이터 모델링

    1) Mapping Rule

        1-1) Entity -> Table

        1-2) Attribute -> Column

        1-3) Relation -> PK, FK

        1-4) N:M을 논리적 데이터 모델링으로 표현할 경우, MAPPING TABLE을 만들어 표현함

    2) 정규화 (Normalization)

        1-1) 제 1 정규화 (Atomic columns) : 각 행, 각 컬럼의 값이 원자적이어야 함

            - 비정규화된 테이블의 tag는 하나의 데이터를 추출하기도, 정렬하기도 어렵다

            - tag의 값을 하나로 만들기 위해 중복으로 데이터를 만든다면 비효율적인 데이터를 추가로 만들어야 한다.

            - tag1, tag2와 같은 새로운 열을 만든다면, 테이블 구조 자체를 변경해야하며 Null이란 값이 생성된다.

            - 불필요한 Null 값은 데이터 공간의 낭비를 발생 시킨다. 유연하지 않은 테이블 형태가 된다.

            - 해당 테이블은 N:M 구조로 새로운 매핑 테이블이 필요하다.

            - Tag를 분리하고 Topic_tag_relation 테이블을 만든다.

            - 데이터를 분석하면, Title 값에 따라 Tag가 구분, 매핑되어 진다는 것을 알 수 있다.

            - 이로인해 tag의 Atomic 하지 않은 상황을 해결할 수 있다.

        1-2) 제 2 정규화 (No partial dependencies) : 부분 종속성이 없어야 한다. 중복키가 없어야 한다.

            - 중복 데이터 확인, title의 MySQL에 대한 부분 종속성

            - price는 type이 paper이냐 online이냐에 따라 달라진다

            - topic 테이블은 이유는 title, type, price를 위해 만들어진 것이다

            - 중복된 데이터와는 상관이 없다.

            - topic 테이블 정리 : title에 의존하는 중복 데이터 분할

            - 새로운 테이블 생성 : title의 type에 따라 결정되는 price에 대한 topic_type 테이블 생성

        1-3) 제 3 정규화 (No transitive dependencies) : 이행적 종속성 제거

            - 중복 데이터가 아직 존재하는 topic 테이블

            - 분리된 author 정보들

            - 새로 정리된 topic 테이블

 

3. 물리적 데이터 모델링

    1) 역정규화 (denormalization) : 정규화된 테이블을 성능이나 개발의 편의성을 목적으로 구조를 변경하는것 

        - 정규화는 대체로 쓰기의 편리함을 위해 읽기의 성능을 희생킨다고 보면 됨 (가능성이 있다는 것임)

        - 세부 테이블로 많이 쪼갠 구조를 join으로 연결하여 사용해야 하는데 이 작업은 비용이 많이 발생됨

        - 이러한 이유로 읽기 성능을 향상시키기 위해 다른 작업을 먼저 조치한 후 최우의 선택으로 역정규화를 함

 

    2) 컬럼을 조작해서 join 줄이기 : 

        - 문제 정의 : topic_tag_relation과 연관된 tag의 name을 가져오는데 매번 join을 통해야 함

        - MySQL Query : 

SELECT tag.name FROM topic_tag_relation AS TTR LEFT JOIN tag ON TTR.tag_id = tag.id WHERE topic_title = 'MySQL';

 

        - tag_name을 topic_tag_relation에 들어가도록 테이블 구조 변경

        * 이러한 구조는 중복 데이터를 만들고, tag 테이블도 그대로 유지되고 있기 때문에 개발의 복잡도를 야기함

        * 오로지 읽기 성능을 향상시키기 위한 작업으로, 충분한 모니터링과 고려 후 작업이 되어야 함

 

        - 테이블 구조 변경

ALTER TABLE 'topic_Tag_relation' ADD COLUMN 'tag_name' VARCHAR(45) NULL AFTER 'tag_id';

        - 문제 개선 : topic_tag_relation과 연관된 tag의 name을 가져오는데 join 없이 하나의 테이블로 가능함

SELECT tag_name FROM topic_tag_relation WHERE topic_title = 'MYSQL';

 

    3) 컬럼을 조작해서 계산 줄이기 : 

        - 목표 시나리오 : 각각의 저자가 몇개의 글을 작성했는지를 목록으로 출력함

        * 해당 작업은 count의 수를 실시간 관리를 해줘야 한다는 개발적 트레이드 오프 사항이 있다.

        - author_id 별로 그룹화 출력

SELECT author_id, COUNT(author_id) FROM topic GROUP BY author_id;

출력 결과

        - 데이터가 많이 누적되어 있는 경우, GROUP BY는 비용이 많이 발생한다. 이에 역정규화된 표를 만든다

        - author 테이블에 count값이 누적되어 관리되는 열을 추가한다.

        - 테이블 구조 변경 Query

ALTER TABLE 'author' ADD COLUMN 'topic_count' INT NULL AFTER 'profile';

        - 변경된 테이블을 이용한 count 수 확인

SELECT id, topic_count FROM author;

 

    4-1) 테이블 쪼개기 (컬럼을 기준으로 분리):         

분리하기 전 테이블

        - 문제 정의 : description의 값이 엄청나게 많이 크다

            - topic 테이블의 title, created, author_id만 검색하는 경우가 많다

            - topic 테이블의 description만 검색하는 경우가 많다

        - 해결안 :

            - topic 테이블에서 description을 제외한다

            - PK인 title을 포함하여 description이 있는 topic_description 테이블을 만든다

            - 검색량이 높은 두 테이블을 다른 서버에 저장하는 방식을 샤딩이라 한다 (수평적)

 

    4-2) 테이블 쪼개기 (행을 기준으로 분리):

        - 문제 정의 : topic 테이블의 누적 데이터가 엄청나게 많다

        - 해결안 :

            - 특정 위치부터 다른 테이블에 저장한다 or 같은 테이블이나 저장되는 크기를 제한, 나눠 관리한다

            - 사용자 id에 따라 저장되는 테이블을 제한, 분리하여 관리한다

            - 동일한 테이블을 분리, 다른 서버에 저장하느 방식을 샤딩이라 한다 (수직적)

분리된 topic 테이블

    5) 테이블 간의 관계 정리, 지름길 제공하기 : 

        - 상황 : 저자의 태그 아이디와 태그명을 조회한다. 해당 정보는 세개의 테이블을 join 해야 한다

        - 문제 정의 : 3개의 테이블을 join 함으로 읽기 성능에 대한 문제가 발생된다

        - Query : 

SLECT * FROM topic_tag_relation AS TTR 
LEFT JOIN tag ON TTR.tag_id=tag.id
LEFT JOIN topic ON TTR.topic_title=topic.title;

조회 결과

        - 해결 방안 : topic_tag_relation 테이블에 author_id 값을 추가하여 join 수를 줄인다

        - Query : 

SELECT * FROM topic_tag_relation AS TTR
LEFT JOIN tag ON TTR.tag_id = tag.id;