[DB] 생활코딩 - 관계형 데이터 모델링

며칠 뒤 시작되는 데브코스 최종 프로젝트에서 데이터베이스 스키마를 설계하고, Analytics 테이블을 생성하는 역할을 맡게 되었다. 그런데 관계형 데이터베이스 모델링에 대한 지식이 아직 정립되지 않았기에 생활코딩의 강의를 통해 준비를 하려고 한다!

관계형 데이터 모델링

전체적인 흐름

• 관계형 데이터 모델링의 순서
  1. 업무파악 : 어떤 업무를 진행하는지 파악 (기획서)
  2. 개념적 데이터 모델링 : 진행하는 일에 어떤 개념과 상호작용이 존재하는지 파악
  3. 논리적 데이터 모델링 : 관계형 DB에 맞게 표로 전환하는 작업
  4. 물리적 데이터 모델링 : DB 제품 선택 및 SQL 코드 작성

업무 파악

• 컴퓨터를 활용해 작업하려면 해당 분야의 실무자와 정확하게 소통하는 것이 중요
• UI (User Interface)를 사용 : 의뢰인과 서로의 생각을 일치하는데 효과적

개념적 데이터 모델링

개념적 데이터 모델링 소개

• 개념적 데이터 모델링 vs 논리적/물리적 데이터 모델링
  • 개념적 데이터 모델링은 업무와 DB에 대한 지식 모두 갖고 있어야 할 수 있음
  • 논리적/물리적 데이터 모델링은 비교적 기계적인 작업
  • 논리적/물리적 데이터 모델링을 잘한다고 개념적 데이터 모델링을 잘한다는 보장은 없음

RDB에 적합한 개념적 구조

모델링을 위한 예시

• 글, 댓글, 저자를 단계적인 구조로 표현
• 문제점 존재 
  • RDB는 내포관계를 허용하지 않음 
  • 거대 단일 테이블로 표현하면 중복 발생

모델 1

내포 관계

거대 단일 테이블

• 댓글, 글, 저자를 동등하게 표현
  • 주제에 따라 데이터를 그루핑 할 수 있음
  • 필요한 특정 주제의 정보만 추출 가능
  • JOIN을 하기에 유리함

모델 2

테이블

ERD (Entity Relationship Diagram)

• ERD : 정보, 그룹, 관계를 제공
  
  • 현실에서 개념을 추출하는 일종의 필터를 제공
  • 개념에 대해 다른 사람과 대화하게 해주는 일종의 언어로 작용
• ERD의 구성 요소 
  • Entity : 이후에 테이블로 구성 (글, 저자, 댓글)
  • Attribute : 이후에 컬럼으로 구성 (제목, 생성일, 이름 등)
  • Relatitionship : 이후에 PK, FK로 구성 (저자 - 글 - 댓글)
• Identifier
  • 후보키 (Candidate Key) : 식별자가 될 수 있는 속성
  • 기본키 (Primary Key) : 후보키 중에서 선택한 식별자
  • 대체키 (Alternate Key) : 기본키를 제외한 후보키
  • 중복키 (Composite Key) : 두 개 이상의 컬럼을 식별자로 지정

ERD 작성 과정

1. 엔티티 (Entity) 정의 (글, 저자, 댓글)
2. 속성 (Attribute) 정의
   • 글 : 제목, 생성일, 본문
   • 저자 : 이름, 자기소개, 가입일
   • 댓글 : 작성일, 본문
3. 식별자 (Identifier) 지정
   • 글, 저자, 댓글 모두 새로운 식별자를 생성 (Auto Increment)
4. 관계 (Relationship) 지정 : Ca
   • 글 - 저자 <작성> : N : M 관계
   • 저자 - 댓글 <작성> : 1 : N 관계
   • 글 - 댓글 <소속> : 1 : N 관계
   • N : M 관계는 중간에 매핑 테이블이 필요

ERD

Cardinality & Optionality

• Cardinality
  
  • 1 : 1 관계 : 담임 - 반, 한 명의 담임은 하나의 반을 가짐
  • 1 : N 관계 : 저자 - 댓글, 한 명의 저자는 여러 개의 댓글을 가짐
  • N : M 관계 : 저자 - 글, 각 저자는 여러 글을 작성하며 각 글은 여러 저자가 존재

1 : 1 관계

1 : N 관계

N : N 관계

• Optionality
  • Optional : 저자 - 댓글, 저자는 댓글 (Optional)을 작성하지 않을 수 있음
  • Mandatory : 저자 - 댓글, 각 댓글은 반드시 저자가 있음

Mandatory(❘) / Optional (O)

논리적 데이터 모델링

테이블과 컬럼 생성 과정

• 엔티티, 속성, 식별자, 관계 지정
  
  • 엔티티 -> 테이블
  • 속성 -> 컬럼
  • 식별자 -> PK, FK
  • 관계 -> 1 : 1, 1 : N, N : N
• write 테이블은 저자 - 글의 매핑 테이블 (write - topic의 관계는 1..N -- 1로 수정)
• dormant 테이블은 1 : 1 관계를 보여주기 위해 생성한 테이블

논리적 데이터 모델링

정규화 (Normalization) 소개

• 정규화 : 정제되지 않은 데이터를 RDB에 어울리는 표로 만들어주는 레시피
• 데이터 소개 (Unnormalized Form) -> RDB에 어울리지 않는 형태
  • title과 type은 중복키로 지정
  • 핑크색, 빨간색, 민트색 : 중복된 데이터
  • 이 데이터를 제1 정규형 ~ 제3 정규형으로 만드는 작업을 진행

Unnormalized Form

제1 정규화

• 원칙 : 각 컬럼의 값들이 Atomic, 즉 하나만 존재해야 함 (Atomic Columns)
• tag : 하나의 컬럼에 여러 개의 값이 들어 있음 (rdb, free 등)
  • 첫 번째 사진의 테이블은 제1 정규형은 만족하지만, 데이터 중복 혹은 컬럼 낭비가 있음
  • 2 - 3 번째 사진은 새로운 테이블을 생성하여 topic 테이블에 매핑

적절하지 않은 제1 정규형

적절한 제1 정규형

적절한 제1 정규형

제2 정규화

• 원칙 : 테이블 상에 부분 종속성이 없어야 함 - 중복 키가 없어야 함 (No Partial dependencies)
• 부분 종속성
  • description, created, author_id, author_name, author_profile : title 컬럼에만 관련됨
  • price : type 컬럼에만 관련됨

적절한 제2 정규형

적절한 제2 정규형

제3 정규화

• 원칙 : 이행적 종속성이 없어야 함 (No Transitive Dependencies)
• 이행적 종속성 (Transitive Dependencies)
  • author_name, author_profile : title이 아닌 author_id에 의존하는 컬럼

적절한 제3 정규형

적절한 제3 정규형

물리적 데이터 모델링

물리적 데이터 모델링 소개

• 이상적으로 작성했던 표를 바탕으로 제품에 맞는 현실적인 표로 만드는 것
• Find slow query : 해당 데이터베이스의 쿼리 성능/병목 확인
• 성능 향상 방법 : Index, Cache
• index
  • 행에 대한 읽기 성능이 향상되지만, 쓰기 성능이 저하
  • 입력 정보를 정리하기 위한 과정이 필요 -> 더 많은 시간과 공간 필요
• Cache (Application Area)
  • 실행 결과를 저장해 뒀다가 나중에 동일한 입력이 들어오면 저장해 둔 결과를 사용
  • 데이터베이스의 부하 감소

역정규화/반정규화 (Denormalization)

• 이상적인 표를 개발의 편의성/성능을 위해 조작하여 구조를 바꾸는 것
• 일단 정규화를 진행한 이후에 성능에 따라 역정규화를 진행해야 함

제3 정규형

제3 정규형

역정규화 : 컬럼을 조작해서 JOIN 줄이기

• 목표 : topic_tag_relation.topic_title의 값이 MySQL인 태그의 이름
• 제3 정규형 상태에서의 쿼리
  • topic_tag_relation과 tag 테이블을 JOIN

SELECT tag_name
FROM topic_tag_relatition AS TTR
LEFT JOIN tag ON TTR.tag_id = tag.id
WHERE topic_title = 'MySQL'

• 역정규화를 진행하여 tag_name 컬럼 생성
  • 중복이 발생한 것을 확인 가능
  • JOIN 작업 없이 tag_name을 가져오므로 훨씬 빠름

ALTER TABLE `topic_tag_relation` ADD COLUMN `tag_name` VARCHAR(45) NULL AFTER `tag_id`;

UPDATE `topic_tag_relation` SET `tag_name` = 'rdb' WHERE (`topic_title` = 'MySQL') and (`tag_id` = '1');
UPDATE `topic_tag_relation` SET `tag_name` = 'free' WHERE (`topic_title` = 'MySQL') and (`tag_id` = '2');
UPDATE `topic_tag_relation` SET `tag_name` = 'rdb' WHERE (`topic_title` = 'ORACLE') and (`tag_id` = '1');
UPDATE `topic_tag_relation` SET `tag_name` = 'commercial' WHERE (`topic_title` = 'ORACLE') and (`tag_id` = '3');

SELECT * FROM topic_tag_relation WHERE topic_title = 'MySQL';

역정규화 : 컬럼을 조작해서 계산 줄이기

• 목표 : 각 저자가 몇 개의 글을 작성했는지 목록으로 표현
• 제3 정규형 상태에서의 쿼리
  • GROUP BY를 활용해 author_id의 개수를 COUNT

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

• 역정규화를 진행하여 topic_count 컬럼 생성
  • GROUP BY 작업 없이 COUNT 확인 가능

ALTER TABLE `author` ADD COLUMN `topic_count` INT NULL AFTER `profile`;
UPDATE `author` SET `topic_count` = '2' WHERE (`id` = '1');
UPDATE `author` SET `topic_count` = '1' WHERE (`id` = '2');

SELECT author_id, topic_count FROM author;

역정규화 : 컬럼/행을 기준으로 테이블 분리

• description의 용량이 크다면, topic 테이블을 조회하는 데 오래 걸림
• 컬럼을 기준으로 분리 : 용량이 적은 것과 큰 것을 분리하여 따로 관리
  
  • 데이터에 접근할 때 성능 향상
  • 각 테이블을 서로 다른 서버에서 관리하여 분산처리 가능

컬럼을 기준으로 테이블 분리

• 행을 기준으로 테이블 분리
  • 관리하기가 힘듦

행을 기준으로 테이블 분리

역정규화 : 관계의 역정규화

• 목표 : 저자의 tag_id와 tag_name을 조회
• 제3 정규형에서는 topic, topic_tag_relation, tag 테이블을 모두 JOIN 해야 함

SELECT tag.id, tag.name
FROM topic_tag_relation AS TTR
LEFT JOIN tag ON TTR.tag_id = tag_id
LEFT JOIN topic ON TTR.topic_title = topic.title
WHERE author_id = 1;

• 역정규화를 진행하여 topic_tag_relation 테이블에 author_id 컬럼 추가
  • JOIN을 한 번만 진행하여 같은 결과를 얻음

ALTER TABLE `topic_tag_relation` ADD COLUMN `author_id` INT NULL AFTER `tag_name`;
UPDATE `topic_tag_relation` SET `author_id` = '1' WHERE (`topic_title` = 'MySQL') and (`tag_id` = '1');
UPDATE `topic_tag_relation` SET `author_id` = '1' WHERE (`topic_title` = 'MySQL') and (`tag_id` = '2');
UPDATE `topic_tag_relation` SET `author_id` = '1' WHERE (`topic_title` = 'ORACLE') and (`tag_id` = '1');
UPDATE `topic_tag_relation` SET `author_id` = '1' WHERE (`topic_title` = 'ORACLE') and (`tag_id` = '3');

SELECT tag.id, tag.name
FROM topic_tag_relation AS TTR
LEFT JOIN tag ON TTR.tag_id = tag.id;
WHERE TTR.author_id = 1;

Reference

https://www.youtube.com/playlist?list=PLuHgQVnccGMDF6rHsY9qMuJMd295Yk4sa