| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | |
| 7 | 8 | 9 | 10 | 11 | 12 | 13 |
| 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| 21 | 22 | 23 | 24 | 25 | 26 | 27 |
| 28 | 29 | 30 |
- 정보에너지장
- client secret
- 비트나미루트
- 관계형데이터베이스
- 마우스캡쳐
- introjs
- optionality
- authorization code
- Resource Sevrer
- mp4GIF변환
- 비트맵 인덱스
- 크롬개발자도구
- 아파치루트
- 므카데쉬
- client id
- 치드케누
- Resource Owner
- refresh token
- MySQL사용자이름
- WAMP
- Android-pc
- 온라인프리젠테이션
- 호반써밋
- revealjs
- 웹서버설치
- Gamebar
- 생체정보프로그램
- captures
- Intel CPU #13th Gen #i9 CPU
- 리모트디버깅
- Today
- Total
솔트펀 티스토리
관계형 데이터 모델링 본문
1. 시작
https://opentutorials.org/course/3883
거대하고 복잡한 현실을 정보로 만들어 표에 담는 것은 매우 어려운 일이다. 천재도 어렵고, 일생을 다 바친 사람에게도 절대로 쉬운 일이 아니다. 과거에는 이런 작업이 아무나 할 수 있는 일이 아니었다.
이런 문제를 해결하고자 하는 생각으로부터 전문가들은 누구나 현실의 복잡성을 컴퓨터에 담을 수 있는 기가 막힌 방법론을 만들었는데 그것이 바로 데이터 모델링이다.
이 내용을 앎으로 하여 아무리 복잡한 현실이라도 표에 담을 수 있는 자신감을 가지게 될 것이다.
강의 전체 내용을 담은 재생목록은 다음과 같다.
2. 데이터 모델링의 순서
업무파악 -> 개념적 데이터 모델링 -> 논리적 데이터 모델링 -> 물리적 데이터 모델링
업무파악 : 의뢰인과 함께 하려고 하는 일이 무엇인지를 알아내야 한다. 그림과 같은 기획서 같은 것이 산출물로 나올 것이다.
이를 바탕으로 개념을 찾아낸다. 내가 하고자 하는 이 일에는 어떠한 개념들이 있고 각각의 개념들은 서로 어떻게 상호작용하고 있는가 하는 것을 심사숙고해보는 시간이다.
이 과정에서 다음 단계로 가기 위한 초석이 되는 ERD[ER 다이어그램. Entity-Relation Diagram 개체 관계도]를 얻게 된다.
개념을 충분히 숙고했다면 이 개념을 관계형 베이스에 맞게 잘 구성을 해야 하는데 이것이 논리적 데이터 모델링 단계이다. 이 단계에서는 앞단계에서 생각하고 만들었던 개념을 관계형 데이터 베이스의 패러다임에 맞는 표로 전환하게 된다.
표를 이렇게 이상적으로 잘 그린다고 하여 실제 DB의 표가 되는 건 아니다.
다음 단계에서는 실제 사용하고자 하는 DB의 제품을 선택하고 해당 제품에 최적화된 코드를 작성하여 실제 표를 만들게 되는데 이것이 물리적 데이터 모델링이다.
데이터 모델링: 문제를 현실에서 뜯어내 고도의 추상화 과정을 거쳐 그것을 컴퓨터라는 새로운 현실로 옮겨 담는 작업. 이 두 개의 세계는 서로 다르기 때문에 처음에 해결하려고 했던 문제가 DB의 표에 잘 담겼는지를 확인하는 작업을 끊임없이 계속해서 해나가야 한다.
3. 업무파악
3.1 소개
클라이언트, 해당 분야의 실무자들과 정확하게 소통하는 것이 중요. 실무자들도 그 분야에 대한 정확한 이해가 있다기보다는 그냥 익숙해져 있을 뿐일 수도 있다. 일은 잘하지만 설명을 할 수 없는 경우가 그런 경우라 하겠다. 이런 익숙해진 사람으로부터 필요한 정보를 끌어내기 위해서는 정말 많은 노하우와 노력이 필요하다. [고로 소프트웨어 엔지니어는 공부를 멀리할 수 없고, 인간관계를 등한시 할 수 없는 것 아닌감?!!!]
업무파악을 할 때 많이 사용하는 방법이 있다. 이렇게 UI를 같이 그려 보는 것이다.
UI[User Interface]는 사용자가 기계를 작동할 때 사용하는 여러 조작 장치들을 말한다.
만들고자 하는 애플리케이션은 어떤 UI를 갖게 될 것인지를 클라이언트와 함께 그려보는 과정에서 원하는 것을 분명히 할 수 있다. 서로 생각하는 것을 일치시키는데 이것만큼 좋은 것이 없다. 이것은 말의 힘을 불신하는 아주 좋은 사례이다.
말의 기능을 불신해야 한다. 그럴수록 말의 신뢰성은 높아진다. 복명복창은 내가 한 말을 상대가 잘 이해했는지 크로스체크하는 것, 정확하게 서로의 생각을 동기화하고 확인할 수 있다.
3.2 기획
카카오오븐 오븐앱, ovenapp https://ovenapp.io/ 온라인 UI 구성 툴
기획서 예시
https://ovenapp.io/view/EuTjedDvHdhBQLoxthGrqvBlGp1jBJj6/SnHZx
https://ovenapp.io/view/EuTjedDvHdhBQLoxthGrqvBlGp1jBJj6/Nv1Y0
https://ovenapp.io/view/EuTjedDvHdhBQLoxthGrqvBlGp1jBJj6/XDCP1
최대한 의사결정자들, 이해당사자들이 모여 자주, 명확하게, 서로 대화를 주고 받으면서 커뮤니케이션을 하며 목적하는 바에 대한 일치를 이루어야 함.
4. 개념적 모델링
- 4.1 개념적 모델링 소개
- 4.2 관계형 데이터베이스 다운 개념의 구조
- 4.3 ERD의 구성요소
- 4.4 Entity 정의
- 4.5 속성 정의
- 4.6 식별자 지정
- 4.7 Entity 간의 연결
- 4.8 Cardinality
- 4.9 Optionality
- 4.10 ERD 완성
- 4.11 Entity Relationship Diagram Helper
4.1 개념적 모델링 소개
개념적 모델링은 논리적, 물리적 모델링보다 앞선 단계이기는 하나, 물리적 모델링을 경험해보지 않고 개념적 모델링을 하기는 어렵다. 개념적 모델링은 관계형DB 모델링 전체 프로세스의 극치이다. 개념적 모델링에 비해 논리적, 물리적 모델링은 비교적 기계적 일이다.
업무라는 현실과 DB라는 또다른 현실사이의 이사, 이 두가지[업무와 DB]에 정통하지 않고 개념을 뽑아내기는 어렵다.
개념적 모델링은 현실에서 개념을 추출하는 필터를 제공한다. 또한 개념에 대해 다른 사람들과 대화를 가능하게 해주는 일종의 언어로서 작용하게 된다.
이런 목적을 이룰 수 있게 해주는 하나의 도구가 Entity Relationship Diagram 이다.
ERD는 현실을 3개의 관점으로 바라볼 수 있는 파인더 finder를 우리에게 제공한다.
정보가 그 첫 번째다. 정보를 발견하고 그것을 다른 사람들에게 표현할 수 있게 도와준다.
다음은 그룹인데, 서로 연관된 정보를 그루핑하여 인식하고, 이것을 다른 사람들에게 표현할 수 있게 해준다.
세번째는 정보, 그룹 사이 관계를 인식하고, 그것을 다른 사람에게 표현할 수 있게 해준다.
현실로부터 개념을 인식하는 도구이면서 그것을 다른 사람도 알아볼 수 있게 표현하는 도구가 바로 ERD인 것이다.
또 하나의 환상적인 기능은 매우 쉽게 표로 전환할 수 있다는 것이다.
ERD를 어떻게 표로 전환할 수 있을 것인가에 대한 정교한 규칙들이 수십년동안 적립되었기 때문에, ERD만 만들면 그것을 표로 만드는 것은 매우 쉽다고 볼 수 있다.
4.2 관계형 데이터베이스 다운 개념의 구조
기획서에는 여러 가지 정보들이 흩어져 있다. 글제목, 저자, 댓글의 내용, 등등. 여기서 서로 연관된 내용들을 묶어주는 큰 덩어리부터 끄집어 내야 한다.
글, 그 글의 저자, 댓글, 댓글의 저자 이런 덩어리들 말이다.
이것을 그림으로 표현하려면 어떤 모습이 되어야 할까? 정답따위는 없다, 설명이 가능하고 모순이 없다면 모두 타당하다.
예를 들어 이런 모습이다.
위 두 그림 모두 기획서의 내용을 잘 설명하고 있기는 하다. 둘 다 합리적이란 얘기다. 관계형 DB 모델링을 놓고 보자면 관계형 DB에 더 어울리는 모델이 유리할 것이다. 그런 점에서 두 번째 모델을 선택해야 한다.
그 이유에 대해 좀 더 자세히 살펴보자.
첫 경우의 모델을 표로 만든다고 하면 아마도 이런 모양일 것이다.
표안에 표가 들어있는 관계임을 알 수 있다. 하지만 RDB 관계형 데이터 베이스에서는 이런 내포관계가 허용되지 않는다.
그렇다고 불가능한 것은 아니다. 이렇게 거대한 하나의 표로 만들 수도 있는 것이다.
어마어마한 프로젝트라 할지라도 거대한 하나의 표로 만드는 것이 아주 불가능하지는 않다. 이런 식으로 만들어진 표는 불합리한 점들을 가지고 있다. 큰 프로젝트라서 표의 컬럼이 1000개나 될 수도 있다. 단 하나의 컬럼 데이터가 필요한 상황에서도 1000개의 데이터를 함께 다루어야 하는 상황이 되는 것. 그리고 그림에서 보는 것처럼 저자의 중복이 발생하고 그 중복개수가 극단적으로 많다면 얼마나 극단적인 자원의 낭비를 초래하겠는가. 이름을 변경하는 등 뭔가 수정해야 하는 상황이 발생한다면 거기에 소요될 시간, 실수할 확률이 아주 높은 정말로 위험한 작업이 되게 된다. 분명한 한계가 있다. 거대 단일 테이블은 중복이 발생한다.
이런 문제를 해결하기 위해 주제에 따라 테이블을 쪼갠다. 이렇게.
이렇게 표를 쪼개게 되면 우선 좋은 것은, 주제에 따라 속성들, 데이터를 그룹핑할 수 있다는 것이다. 가장 중요한 얘기다. 두번 째로는 어떤 정보 예를 들어 글에 대한 정보가 필요하다고 할 때, 글에 대한 표만을 조회하는 것으로써 컴퓨터의 자원을 아낄 수 있다는 것이다. 또 하나, 무엇보다 중요한 특징 중 하나는 바로 JOIN이다.
필요한 자료들을 필요할 때마다 순간순간 합성해낼 수 있다.
즉 Fig 16과 같은 포함 관계가 아니라, Fig 17과 같은 수평적인 관계로 개념을 뽑아내는 것이 관계형 데이터베이스에 더 어울리는 방식이라고 말할 수 있다. 이렇게 설명하는 것이 개념적 데이터 모델링에 대한 정확한 설명은 아니기 때문에 보다 자세한 내용은 스스로 더 학습을 진행하는 것이 바람직하다.
4.3 ERD의 구성요소
이렇게 찾아낸 개념을 Entity라고 한다. Entity는 후에 Table로 전환된다.
글에 대해서 좀 더 들여다 보면, 이 글이라는 Entity는 실제적인 데이터는 아니다. 구체적인 데이터는 제목이나 생성일, 본문 등이다.
이것들을 그루핑한 것이 바로 글이라는 Entity인 것이다.
이 구체적인 데이터가 바로 Attribute 속성이며, 이것은 후에 표의 column이 된다.
설명이 이런 식으로 되면, 저자의 이름 [Fig 24. kim, lee], 댓글 과 같은 것들은 왜 글의 속성이 되지 않는가 라는 의문이 들게 된다. 만일 저자의 이름만 필요하다면 저자의 이름이 속성이 될 수 있을 것이지만, 저자의 이름뿐 아니라 저자에 대한 자기 소개, 가입일 등과 같은 정보가 필요하다면 저자는 이름, 자기소개, 가입일로 이루어진 Entity로 되는 것이다. Entity를 디렉토리, Attribute를 파일에 비유한다면, Entity는 파일만 담을 수 있고, 자식 디렉토리는 담을 수 없는 제한 적인 디렉토리이다.
Entity 들 사이의 관계를 살펴보자.
Fig 26과 같이 연관성을 표현한 것을 Relation 관계라고 하며, 표에서 이 관계는 기본키 PK: Primary Key, 외래키 FK:Foreign Key 라는 형태로 표현된다. 이렇게 저장된 테이블들을 JOIN을 통해 동적으로 연결할 수 있게 된다.
개념적 데이터 모델링은 개념에 집중하고 DB 패러다임으로부터 거리를 두고 있기 때문에 용어가 실제 DB 제품들과는 다르다. Entity 는 Table, Attribute는 Column, Relation은 PK, FK가 될 것이다. 또한 Diagram에서는 표현되지 않지만 설계를 할 때 자주 나오는 표현이 행 Row에 관한 것인데 행을 Tuple이라고 부른다.
4.4 Entity 정의
애플리케이션을 하나의 건물에 비유한다면 User Interface는 옥상, Database는 지하라고 볼 수도 있겠다. UI와 DB사이의 일을 원인과 결과사이의 관점에서 생각해본다면, 정보를 입력하는 UI가 원인이 되어 DB의 데이터를 변경하는 결과를 낳는다. 또한 DB의 데이터라는 원인으로 인해 UI의 내용이 표시되는 결과를 낳는다. 즉 사용자가 마주하는 UI와 컴퓨터에 저장되는 데이터는 서로 원인과 결과의 관계에 놓인다고 할 수 있다. 이 원인과 결과를 번갈아 가면서 순차적으로 점검하지 않는다면 좋은 모델링이 나오기 어렵다.
그런 점에서 기획자와 구현자가 다르다면 데이터 모델링까지는 최소한 함께 동행하는 것이 이상적이다. 데이터 모델링은 기획에 , 또 기획은 데이터 모델링에게 놓치면 안되는 중요한 내용을 보여주게 되는 것이다.
기획서를 다시 한번 보자
제일 먼저 해야 할 일은 기획서에서 Entity를 찾아내는 것이다. 인간은 본능적을 연관된 데이터들을 그루핑하는 탁월한 능력을 가지고 있다. 의식적으로 하려면 잘 안되는 것 같지만, 무의식적으로 이미 하고 있다는 얘기다. 읽기에서는 그것을 찾아내기가 좀 어려워 보이지만 쓰기 화면을 한 번 보자.
쓰기 화면을 보면 표로 만들기에 아주 적합한 Entity가 대체로 분명히 드러나게 된다. 쓰기 화면을 그대로 Entity에 한 번 옮겨보고 큰 무리가 없다면 쓰면 되는 것이고, DB 테이블에 적합치 않다면 수정하면 되는 것이다.
이것을 ERD로 한 번 옮겨보자. https://draw.io 서비스를 이용하면 각종 Chart나 Diagram을 그려 Onedrive나 GoogleDrive등에 저장해둘 수 있다.
먼저 Entity를 그린다.
4.5 속성 정의
이번에는 속성을 정의를 해야 하는데 속성은 각각의 Entity의 소속이 되는 것이다. 그 소속 관계는 작성을 통해 쉽게 파악할 수 있다.
저자목록이 직접 입력하는 것이 아니라 셀렉트박스 select box인 것은 저자목록이 좀 더 복합적인 속성이기 때문에 별도의 Entity로 독립해나갔다라는 의미일 수 있는 것이다. 따라서 저자는 속성이라기보다는 나중에 Relation을 통해 해결해야 한다.
다음, 쓰기만으로 좀 부족한 것은 읽기에 보면, 언제 작성이 되었는지 즉 작성일에 대한 정보도 DB 시스템을 필요로 한다.
그러면 Entity의 속성으로 날짜 정보도 있어야 한다. 제목, 본문, 날짜 까지 한 번 정의해보자.
ER Diagram에서 속성은 원을 사용한다. 글이라는 Entity에 속한 속성이라는 것을 나타내기 위해 연결해준다.
다음, 저자에 대한 정보는 이름과 자기 소개가 필요한데,
읽기를 보면 가입일 정보도 필요하다는 것을 알 수 있다.
이제, 댓글을 작성하는 부분을 보자.
내용을 입력하는 부분이 2개인 것을 발견할 수 있으며 아래 부분은 작성자명, 저자명이 들어가는 곳이라는 것을 알 수 있다. 로그인이 필요없이 댓글작성이 가능하다면
Fig 40과 같이 해도 되겠지만, 기존에 가입되어 있는 저자들에게만 댓글 작성 권한을 주고싶다면 select box로 바꾸는 것이 좋을 것이다.
보는 바와 같이 모델링을 하게 되면 모델링이 UI를 검증하고, 또 UI가 모델링을 검증하는 교차검증이 가능하기 때문에 이 두 가지를 함께 번갈아 가면서 살펴보는 것이 매우 중요하다. 기획자와 모델러가 한 사람이면 좋겠지만 서로 다른 사람이라면 충분한 협력과 소통을 거쳐야만 원하는 결과를 얻어낼 수 있다.
4.6 식별자 지정
속성을 정의하고 나서 해야 할 것은 Entity에 있는 속성중에서 대표선수를 뽑아야 한다. 다시말하여 식별자 Identifier를 찾아 지정해주어야 한다. 국가는 국민을 주민번호에 의해 식별하고, 자동차는 자동차 번호가 있고 웹사이트는 도메인 이름이 있다. 이러한 것들을 식별자라고 하는데, 식별자가 하는 일은 원하는 대상을 정확하게 타겟팅하는 것인데, 그래야 그 대상에 대해 어떠한 일을 할 수 있기 때문이다. Identifier의 가장 중요한 부분은 유니크(유일한)한 값을 가져야 한다는 것이다. ER Diagram [Entity Relationship Diagram]에서도 어떤 속성을 Identifier로 할 것인지 지정해야 하며 이 Identifier는 DB에서 PR [primary key 기본키] 이 된다. 다음 표를 한 번 보자.
이 표에는 Identifier로 사용될 수 있는 Column과 사용될 수 없는 Column이 있다. name 은 동명이인이 있을 수 있고, city는 당연히 중복이 되기 때문에 name과 city는 identifier가 될 수 없다. email은 식별자가 될 수도 있겠지만 한 사람이 여러 개의 email을 가질 수도 있기 때문에 정부에서 운영하는 시스템 등의 경우에는 email이 식별자 identifier로 쓰기에 적합치 않을 수도 있다. 그런 경우에는 주민번호 national_id 를 식별자로 쓸 수 있을 것이다.
아니면, 행이 추가될 때마다 자동으로 1씩 증가시켜 유니크한 값을 부여하는 것을 통해 식별자 identifier를 제공해줄 수도 있다. 이런 식별자는 인조키라고 부른다.
Fig 45 에서는 식별자가 될 수 있는 후보키 cadidate key 가 3개라는 사실을 보여준다. 이 후보키 중에서 식별자가 user_id 라고 한다면 기본키 primary key 라고 부르며 다른 키들은 대체키 alternate key 라고 부른다.
alternate key 들은 성능향상을 위한 secondary index 라고 하는 아주 유용한 용도로 쓰이게 된다.
좀 특수한 경우로 중복키 composite key 가 있다. 아래 표를 보자. 이 표는 부서별 직원명단 인데, 1번 부서에는 1과 2 두명의 사원을 가지고 있다. 또한 한 명의 직원이 여러 개의 부서에 속할 수도 있는데 1번 직원은 2번 부서에도 속할 수 있다는 뜻이다. 이 표에서는 직원번호만으로, 또는 부서번호만으로 행을 식별하는 것이 불가능하다. 부서배정일은 말할 것도 없고.
이런 경우에는 직원 번호와 부서번호 이 두 가지를 합쳐서 식별할 수 있는데 이런 식별자를 중복키 composite key라고 부른다.
앞에서 만든 Entity들 중 하나씩 살펴본다면, 글 Entity의 속성중에는 식별자가 될 만한 것이 없다. 자연스럽게 식별자가 될 수 있는, identifier가 될 수 있는 속성이 없기 때문에 인조키, 대리키 라는 것을 만들어 , 실제 DB에서는 autoIncreament, sequence 등을 이용하여 다른 것과 중복되지 않는 일련번호를 사용하게 될 것이다.
그리고 식별자라는 것을 쉽게 알 수 있도록 밑줄을 그어준다.
이렇게 하여 각각의 Entity가 자신을 식별할 수 있는, 자신의 tuple 즉 행을 식별할 수 있는 컬럼이라고 할 수 있는 identifier column 식별자 컬럼을 가지게 되었다.
4.7 Entity간의 연결
Entity와 Entity의 Relationship을 맺어주는 방법을 알아보자. 그럼 최종적으로 Relationship은 무엇이 되는가?
Fig 22의 표를 다시 보면, 표가 있고 각각의 표들은 데이터로 서로 연결되어 있다. 이때 각각의 표의 행을 식별하는 유일무이한 식별자를 Primary Key라고 한다. 즉 '저자' 표의 '아이디', '댓글' 표의 '아이디', '글' 표의 '아이디' 가 각 표들에서 Primary Key가 된다. 그리고 '저자' 테이블의 '아이디'를 가리키는 '글' 테이블의 '저자 아이디'는 외래에 있는 테이블과 연결할 때 사용하는 열쇠라는 뜻에서 외래키 foreign key라고 부른다.
즉, 관계형DB의 Relationship은 primary key와 foreign key가 연결되는 것을 통해 실제로 구현된다고 말할 수 있다.
이런 관계를 어떻게 그림으로 표현하는지를 보면 Relationship에 대해 어느 정도 알 수 있게 될 것이다.
ER Diagram에서 Relationship을 표현할 때에는 마름모꼴을 사용하여 표현한다. 저자는 글을 쓰고 글은 저자에 의해 씌어진다. 이 관계는 '작성'이라는 관계로 표현한다. '저자'와 '댓글' 역시 '작성'이라는 관계가 있고, '글'은 '댓글'을 포함[소속]하고, '댓글'은 '글'에 소속되어 '글'과 '댓글'은 소속이라는 관계로 맺어지게 된다. 이렇게 관계의 이름을 정한 다음에는 선으로 연결한다.
4.8 Cardinality
Relationship에서 꼭 따져보아야 하는 요소로서 Cardinality와 Optionality가 있다. 1주일만 지나 다시 봐도 헷갈릴 만큼 매우 까다로운 개념이다.
cardinality: cardinal number - A cardinal number is a number such as 1, 3, or 10 that tells you how many things there are in a group but not what order they are in. Compare ordinal number. 기수. 基数。
https://itholic.github.io/database-cardinality/
[database] 카디널리티(cardinality)란?
cardinality
itholic.github.io
먼저 이런 경우를 보자. 아래에 표가 두 개 있다. 이 두 개의 표는 나중에 JOIN 같은 것으로 서로 연결될 것이다.
각각의 표에 행이 3개 씩 있는데, Cardinality를 따져 보려면 담임에게 반은 몇 개인가, 반에게 담임은 몇 개인가 를 양쪽 모두 따져봐야 한다. 무슨 뜻인지 좀 더 자세히 보자. 담임은 반을 몇 개 가질 수 있을까? 각 선생님은 하나의 반만을 담임한다. 예를 들어 kim 이라는 선생님이 1반을 맡았다면 kim 은 다른 반은 맡을 수 없다. 그리하여 담임에게 반은 1 개다.
이번에는 반대로 반에게 담임은 몇 개일까? 각 반의 담임은 1 명이다. 예를 들어 1반 선생님이 kim이라면 다른 선생님은 그 반의 담임이 될 수 없다. 즉 반에게 담임은 1 이다. 이것을 1:1관계라고 하며 ER Diagram에서는 Fig 53과 같이 나타낸다.
다음 경우를 보자. 저자에게 댓글은 몇 개냐를 따져보는데, 저자는 여러 개의 글을 작성할 수 있다. 예를 들어 kim이라는 사람은 아래 그림과 같이 3개의 댓글을 작성할 수 있는 것이다. 그리하여 저자에게 댓글은 여러 개이다라는 뜻에서 N이라고 표기한다.
댓글에게 저자는 몇 개냐를 따져보면, 각 댓글은 하나의 저자만 존재한다. 예를 들어 댓글1이 kim 이라는 저자를 가지고 있다면 다른 누구도 이 댓글의 저자가 될 수 없다. 즉 이런 경우에는 댓글에 저자는 1개다. 그래서 이것을 1대多 라고 한다. 1:N 이라고도 한다.
이것을 ER Diagram에서는 Fig 56 과 같이 표기한다. N에 해당하는 부분은 3발이 표시를 한다. 표기법은 다양한데 어떤 경우에는 한쪽에는 1, 한쪽에는 N이라고 직접 쓰는 경우도 있으며 그냥 보기만 하면 대뜸 이해할 수 있을 것이다.
또 다른 경우로서 저자와 글의 관계다. 하나의 글을 여러 명이 공동으로 편집할 수 있다고 하면, 각 저자는 여러 개의 글을 작성할 수 있다. 예를 들어 kim 이라는 사람은 Fig 57과 같이 3개의 글을 작성할 수 있다. 저자에게 글은 여러 개라는 뜻에서 M이라고 표현하기로 하자.
이번에는 글에게 저자는 몇 개냐를 따져보자. 각 글은 여러 저자가 존재한다. 예를 들어 글1이 생겼다면 이 글은 여러 명의 저자가 참여할 수 있는 것이다. 글에게 저자는 여러 개이기 때문에 N이라고 표현한다.
이런 관계를 N대M이라고 하며 多대多 라고도 한다. ER Diagram으로 표시하면 Fig 59에서와 같이 3바리가 양쪽에 붙어있다.
지금껏 살펴본 관계성들은 3가지, 즉 1대1, 1대多, 多대多 등이다. 이 중에서 多대多 관계는 실제로 현실의 DB의 테이블로는 표현할 수 없기 때문에 다이어그램처럼 그대로 두지 않고 중간에 연결테이블이라고 하는 특별한 테이블을 만들어서 최종적으로는 1대多 관계로 converting을 시킨다.
4.9 Optionality
두 개의 표가 있다. 저자와 댓글 사이에는 어떤 관계가 있는지 한 번 생각해보자. 만일 어떤 시스템에 저자가 등록을 했다고 하여 댓글을 반드시 가지고 있는 것은 아니다. 일단 등록을 먼저하고 댓글은 나중에 언제가 쓰게 될 것이니까 말이다. 즉 저자는 댓글을 작성하지 않을 수도 있다. 아래그림은 kim 은 댓글을 작성했는데 나머지 사람들은 댓글을 쓰지 않았다는 것을 보여주고 있다. 이렇게도 해석할 수 있는데, 즉 저자에게 댓글은 옵션이다.
이것을 Diagram에서는 다음과 같이 표시한다. 동그라미는 Option의 이니셜을 뜻할 수도 있다. 그리고 어느 한 쪽에 더 가까이 표시함으로써 방향도 나타낸다.
그리고 댓글이 일단 존재한다면 그것은 반드시 저자를 가지고 있어야 한다. 즉 각 댓글은 반드시 저자가 있다. 다시말해 댓글에게 저자는 필수 Mandatory 이다.
이것을 Diagram에서는 이렇게 막대기 하나를 그어 표시한다. Optional과 Mandatory의 방향은 헷갈릴 수 있기 때문에 주의를 매우 집중해야 한다.
이렇게 보면 저자와 댓글은 필수냐 선택이냐 라는 측면도 있지만 동시에, 1:1이냐, 1대多냐, 多대多라고 하는 Cardinality도 있다. 만일 이 두가지 속성을 모두 반영한다면 어떻게 될까?
저자와 댓글은 1:N의 관계이기도 하다.
이때 ER Diagram에서는 두가지를 서로 합쳐서 표현할 수 있다. 이런 다이어그램과 맞다들리면 L[왼쪽]은 여러 개의 R을 가질 수 있고[3바리], 또 R이 없을 수 도 있고 [Optional], R은 반드시 L이 필요하다는 사실[왼쪽의 Mandatory], 그리고 단 하나의 L만 가진다는 사실에 대해 해석할 수 있게 된다.
4.10 ERD 완성
이렇게 하면 개념적 모델링은 끝난 것으로 되는데, 개념적•논리적•물리적 모델링의 각 단계에서 어떤 일을 해야 하고 어떤 기호들을 쓸 것인가에 대한 의견이 완전히 통일된 것은 아니며 서로 다른 의견들이 존재한다. 정해진 엄격한 룰이 있는 건 아니란 얘기다. 따라서 과제의 성격에 따라 작업 순서를 변경시켜 가면서 자유롭게, 유연하게, 합의를 통해 진행해나가면 될 것이다.
4.11 Entity Relationship Diagram Helper
이 부분은 일종의 부록이라고 볼 수 있는데, 정작 하다보면 1:1, 1대多, 多대多, 거기에 더해 Mandatory, Optional 까지 하면 매우 헷갈리게 된다. 그래서 간단한 프로그램을 만들어 놓았는데, erd.yah.ac 로 접속해보면 두개의 Entity 사이의 관계를 입력하여 해당한 다이어그램 부호를 얻을 수도 있고 다이어그램 부호를 변경시켜 하나 또는 다수의 관계인지, 필수 인지, 옵션인지에 대해 얻어낼 수 있어, 데이터 모델링 하면서 해석으로 쓰기 유용한 웹앱이다.
5. 논리적 데이터 모델링1
- 5.1 논리적 데이터 모델링
- 5.2 테이블과 컬럼 생성
- 5.3 1:1 관계의 처리
- 5.4 1:N 관계의 처리
- 5.5.1 N:M 관계의 처리
- 5.5.2 N:M 관계의 처리 - 내용 정정
5.1 논리적 데이터 모델링
데이터모델링을 여러 사람이 분업을 한다면 아마도 최고의 실력자는 개념적 데이터 모델리에 배치해야 할 것이다. 개념적 데이터 모델링은 순서상으로는 앞에 있지만, 제일 쉽기 때문이 아니라 제일 어렵고 제일 중요하기 때문이다. 개념적 모델링이 잘 되어있다면 논리적 모델링은 매우 기계적인 일이다. 개념적 모델링이 업무에서 개념을 뽑아내는 일이라면 논리적 데이터 모델링은 그렇게 뽑아낸 개념을 관계형 DB의 패러다임에 어울리게 데이터 형식을 잘 정리정돈 하는 것이다. 이 과정에서는 구체적인 DB 제품의 특성이나 성능 같은 것에 대해서는 크게 신경 쓰지 않는다. 대신 관계형 DB 패러다임에 어울리는 가장 이상적인 모습으로 개념을 잘 정리정돈 하는 것이 포인트다.
가장 먼저 Mapping Rule에 대해 얘기 해보자. ER Diagram을 통해 표현한 내용을 관계형 DB 에 맞는 형식으로 전환할 때 사용해볼 수 있는 방법론이 Mapping Rule이라 할 수 있다. 이 방법론에 따라 하게 되면 덜 고민하고도 더 많은 것을 빠짐없이 처리할 수 있기 때문에 Mapping Rule은 광범위하게 사용되고 있다.
Mapping Rule을 간단히 본다면, Entity는 테이블로 바꾸면 되고, Attribute는 Column으로 바꾸고, Relation은 PK와 FK로 전환하면 된다.
5.2 테이블과 컬럼 생성
ER Diagram 을 관계형 데이터 모델로 전환하는 작업을 시작해보자구요~~
ER Master 라고 하는 도구를 사용해 본다. 이 도구는 오픈소스이고 무료이며 성능 or 기능이 아주 좋다. 이 도구가 아니더라도 종이에 쓸 수도 있고, 엑셀•구글스프레드 시트 같은 것을 써도 무방하다. 이 Tool은 설치과정을 거쳐야 쓸 수 있다. ermaster. sourceforge.net ER Master
ER Master는 Eclipse 이클립스에 설치되는 plugin 이다.
Mapping Rule 에 따르면 제일 먼저 해야 할 것은 Entity를 테이블로 바꾸는 것이다. '저자', '글', '댓글' 이라는 3 개의 Entity가 있기 때문에 3개의 빈 테이블을 먼저 만든다.[Table 도구 사용]
Entity 중에서 Table로 옮기기 쉬운 테이블은 스스로도 존재하는 테이블, 즉 foreign key 가 없는 테이블이다. 예를 들어 저자 테이블.
Add를 클릭하여 컬럼을 추가하는데 먼저 author이 id 값을 지정한다. Primary Key, AUTO_INCREMENT 를 체크해주고, Physical Name, Logical Name, Type 등을 지정한다. 이렇게 어떤 속성에 여러 가지 제약조건, 예를 들어 해당 데이터는 정수, 문자길이는 얼마, 유니크해야 함 등 여러 가지 제약조건 을 해당 컬럼에 대한 도메인을 설정한다고 말한다. 도메인은 영역이라는 뜻, 즉 해당 컬럼에 들어갈 데이터가 정해진 범위 안에 있는 데이터만 들어갈 수 있도록 경계를 지어준다고 생각해도 될 듯 싶다.
이렇게 엔트리와 속성을 테이블로 매핑하는 작업을 진행한다.
5.3 1:1 관계의 처리
이번에는 Relationship을 Primary key와 Foreign Key로 연결하는 과정을 통해 관계형 DB의 모델에 맞게 converting을 해보자.
여기서 가장 중요한 것은 cardinality 즉 몇 대 몇 이냐를 따져보는 것. 가장 단순한 것은 1:1 관계이고, 다음은 1:N, N:M의 순서로 처리하면 되는데, 위에서 본 예시에는 1:1 관계가 없기 때문에 1:1 관계가 되는 Entity 하나를 더 추가해보자. 회원으로서의 활동을 멈추고 휴면 상태에 있는 저자들의 목록을 관리하는 테이블을 하나 추가하기 위해 ER Diagram에서 '휴면자'라는 Entity를 하나 더 만든다.
저자는 휴면을 할 수도 있고, 안 할 수도 있기 때문에 Option이고, 휴면자가 있는 경우에 그것은 반드시 저자이기 때문에 '필수'로 한다. 이렇게 짜여진 ER Diagram을 관계형 데이터 모델에 반영해보자.
'휴면자' Entity에 상응하는 표인 dormant를 먼저 추가한다. 이때 휴면 id는 자기의 독자적인 id 값을 가질 필요가 없고 author id 값과 같은 값을 주면 된다. 굳이 자기 id 값을 AUTO_INCREMENT 같은 걸 하지 않아도 되면 가급적 하지 않는 게 좋다. primary key 값은 DB에서 성능이 가장 좋은 key, 인덱스이기때문에 그렇다.
그러면 author 와 dormant 2개의 테이블에서 어느 것이 Foreign Key를 가질까? 이런 문제는 1:N 관계에서는 전혀 헷갈리지 않는다. 1쪽에 PK, N쪽에 FK를 준다는 것이 너무 당연하기 때문이다.
Fig 86과 같이 '저자' 테이블과 '휴면저자' 테이블이 있으면 누가 누구에게 의존하는지를 따져본다.
저자는 휴면저자의 id 가 있든 없든 상관없이 자기 행을 추가할 수 있다. 반대로 휴면저자에는 저자의 id에 존재하는 id 값만 추가할 수 있다. 스스로도 존재할 수 있는 '저자'는 부모테이블, 혼자서는 존재할 수 없는 '휴면저자'는 자식테이블이라고 생각해도 좋다. 그런 맥락에서 '저자' 테이블에는 PK, '휴면저자'에는 FK를 설치하면 된다. 이런 구분법으로 1:1 관계에서 PK와 FK를 어디에 정할지를 결정한다.
ER Master에서는 FK를 설치할 때 'Relation by existing columns 이미 존재하는 컬럼으로 관계 relation 맺기' 도구를 사용하면 된다.
author_id 를 이용하여 만들 것이다. 먼저 부모테이블 author를 클릭하고, 자식테이블 dormant를 두 번째로 클릭하면 이렇게 관계가 형성된다.
Fig 90을 보면 FK는 dormant테이블의 author_id 값에 FK를 가진 것을 볼 수 있다. (골든키:PK, 실버키:FK)
두 테이블 사이의 관계를 맺어주는 선을 클릭하여 author와 dormant 사이에 몇 대 몇 관계인지를 지정한다.
author에게 dormant는 있을 수도 있고 없을 수도 있기 때문에 Child/Multiplicity를 '0..1' (0 혹은 1) 로 지정하고 dormant에게 author는 반드시 있어야 하기 때문에 1로 지정한다.
5.4 1:N 관계의 처리
Fig 82를 보면 '댓글'은 '저자'와도 관계를 가지고 있고, '글'과도 관계를 갖고 있다. 이것을 표로 표현하면 댓글[comment]이 만들어지기 위해서는 댓글을 어떤 저자가 썼고, 어떤 토픽['글']에 속해있는지에 대한 정보가 필요하다.
이를 위해 comment[댓글] 테이블에 컬럼을 두 개 추가한다.
ER Diagram에는 이미 반영이 되어 있고, 이를 관계형 데이터 모델에 반영해보자.
comment 테이블에 컬럼 두 개를 추가해야 하는데 ER Master에서는 쉽게 컬럼을 추가할 수 있다. 1:N 관계는 '1:N Relation'도구를 사용하며 Primary Key를 가지고 있는 topic을 먼저 클릭하고 다음 comment를 클릭한다. 그러면 자동으로 컬럼이 하나 추가된다.
그리고 author를 클릭하고 comment를 클릭하면 Fig 97과 같이 자동으로 또 하나의 컬럼이 추가된다.
comment 테이블을 더블클릭하여 topic_id와 author_id 라고 컬럼명을 편집한다.
다음, topic은 comment에게 필수이고 topic에게 comment는 옵션이라는 것을 지정해보자. topic과 comment사이의 선을 클릭하고 Multiplicity 값을 '0..n'으로 지정하는데, 의미는 0이나 1이나, 그보다 더 큰 수자도 가능하다는 뜻으로 결국 Optional을 의미한다.
author와 comment 사이 관계를 본다면 저자는 반드시 있어야 하지만 저자가 반드시 댓글을 쓰는 것은 아니기 때문에 comment는 없을 수 도 있다. 그것을 반영하기 위해 author와 comment 사이의 선을 클릭하여 역시 comment의 Multiplicity를 '0..n'으로 설정한다.
1:N 관계는 가장 쉬운 관계라고 볼 수 있다. 1:1 관계는 어느 쪽이 Primary Key를 가지는지 애매하고 좀 헷갈리며, N:M은 아주 어렵다.
5.5.1 N:M 관계의 처리
ER Diagram 상에서 N:M 관계는 '저자'와 '글'이다. 이 시스템은 1명의 저자가 여러 개의 글을 쓸 수 있고, 하나의 글을 여러 명의 저자가 작성할 수 있다. 즉 N:M 관계다. 이런 관계를 어떻게 처리하는지 한 번 들어가 보자.
author[저자] 테이블과 topic[글] 테이블만 떼어놓고 Excel에서 한 번 그려본다. kim이라는 저자는 MySQL과 SQL Server, ORACLE이라는 글을 썼고, lee는 MySQL과 SQL Server라는 제목의 글을 썼다. 초록색으로 표시를 한 MySQL과 SQL Server는 kim이라는 사람도 작성을 했고, lee라는 사람도 작성을 했다. 또 kim이라는 사람은 그외에도 더 작성한 글을 가지고 있다. 즉 N:M 관계이다.
그렇다면 이런 내용을 테이블에 어떻게 반영할 것인가? topic에 author_id 컬럼을 추가해보면 '1, 2'와 같이 값이 콤마로 구분된 2 개 이상의 값을 가지기 때문에 JOIN이 불가하며 정열도 할 수 없고 author_id 가 1인 저자가 쓴 글만을 얻기 위해 WHERE 문을 쓰려고 해도 제대로 쓸 수 가 없다.
반대로 author 테이블에 topic_id 컬럼을 추가해도 마찬가지고 값을 2 개 이상 가지기 때문에 JOIN도 할 수 없고, 여러 가지 문제들을 가지고 있다.
즉 N:M 관계는 어느 한 쪽에 컬럼을 추가하는 방식으로는 문제를 해결할 수 없는 것이다.
그리하여 이때에는 author도 아니고 topic도 아닌 중재자가 필요한데 이것을 맵핑테이블 또는 연결테이블이라고 한다. 하는 중재자가 필요하다. 궁극의 해결방법에 대해서 보기로 하자.
author와 topic의 중간에 새로운 테이블을 만드는데, 이 테이블은 각각의 저자가 어떤 글을 썼으며 각각의 글은 누가 저자인가 라는 것을 연결시켜주는, mapping 시켜주는 맵핑테이블 mapping table 이다. 이름은 'write'라고 한다. 이런 맵핑테이블을 만들게 되면 두 개의 테이블이 결합되었을 때 의미가 있는 정보 예를 들어 각각의 저자가 글을 언제 수정하기 시작하였는지와 같은 정보들을 추가할 수도 있다. 여기서는 created라는 컬럼에 그런 정보를 담았다고 하자.
이렇게 작업한 내용을 ER Diagram에도 추가하고 스키마에도 반영을 한다.
5.5.2 N:M 관계의 처리 - 내용 정정
ER Diagram에는 사실 따로 반영할 필요가 없다. 이미 연결선에 多대多 관계가 '3발이' 표시를 사용하여 반영이 되어 있기 때문이다. 그것을 보고 Relationship을 나타내는 마름모꼴[작성]이 맵핑 테이블이라고 생각하면 되는 것이다. 그러면 스키마부분에서 어떻게 반영하는지를 알아보자.
ER Master에서 author와 topic 사이에 'Table'도구를 사용하여 새 테이블을 추가한다.
그리고 '1:N Relation' 도구를 사용하여 Relation을 맺어주는데, author/write 순으로 클릭을 하고, 또 topic/write 순으로 클릭을 하면 1:N 관계가 맺어지게 된다.
write을 열어 컬럼명을 수정하고 created 컬럼을 추가한다.
author_id 와 topic_id를 모두 Primary Key로 지정하면 Primary Key인 동시에 Foreign Key로 된다.
이제 각각의 테이블과 매핑테이블사이의 관계를 따져본다. author와 write를 먼저 보면 저자는 작성을 하지 않을 수도 있으므로 Option이고, 작성을 했다면 저자는 반드시 있어야 하기 때문에 Mandatory 필수가 되어야 한다. author와 write 사이의 선을 클릭하여 관계를 편집한다. Child의 Multiplicity는 '0..n'의 값을 설정하여 옵션이라는 것을 표현한다.
topic과 write의 경우는 약간 다르다. 글이 있으면 반드시 작성이 있는 것이고, 작성이 있으면 반드시 글이 있는 것이다. 따라서 topic과 write는 양쪽 다 필수여야 한다. 편집을 위해 topic과 write사이의 선을 클릭하고 Child의 Multiplicity를 '1..n'의 값으로 지정한다. 글은 1명 또는 그 이상의 저자가 작성하는 것이기 때문이다.
6. 논리적 데이터 모델링 2 - 정규화
평범한 표를 관계형 데이터 베이스에 어울리는 비범한 표로 만드는 공정인 정규화에 대해 살펴본다.
6.1 정규화 소개
관계형 DB의 부모라고 할 수 있는 Edgar Frank "Ted" Codd, 에드거 프랭크 "테드" 커드 박사는 평범한 사람도 그가 제안하는 방법을 적용하기만 하면 천재적인 표를 만들 수 있는 레시피를 개발하였는데 그것을 '정규화 Normalization'라고 한다. Normalization이란 정제되지 않은 데이터, 즉 표를 관계형 DB에 어울리는 표로 만들어주는 레시피이다.
그럼 정규화란 무엇인가에 대해 살펴보기로 하자.
UNF: UnNormalization Form 정규화되지 않은 표를 의미
1NF: 1st Normal Form 제1정규형
제3정규형 까지가 산업적으로 주로 많이 사용되며 그 뒤쪽으로는 학술적으로 많이 사용되는 것이기 때문에 여기서는 제3정규형을 만드는 방법까지 살펴보기로 한다.
http://bit.ly/2wV2SFj 예시 파일주소
Fig 119에 UNF의 예시를 보여주고 있다. topic이라는 표인데, title과 type이라는 두 개의 컬럼을 묶어서 중복키로, 또 Primary Key로 지정해놓았다. 각각의 행들은 title과 type 두 개의 컬럼을 통해 식별된다는 뜻이다. 색을 칠한 셀들은 문제를 가지고 있다. cyan 색을 칠한 tag 컬럼은 하나 이상의 값을 가지고 있고, 연한 빨강 부분과 뻘건 색을 칠한 부분은 중복된 값을 가지고 있다. 이러한 것들은 관계형 DB에 잘 어울리지 않는 표들이라는 뜻에서 unNormalized Form 이라고 한다. 이제 이것을 제1정규형 First Normal Form으로 바꿔보고, 제2정규형 Second Normal Form 으로 바꾸고, 제3정규형 Third Normal Form으로 바꿔 나가면서 관계형 DB에 더욱 걸맞는 표로 만들어보자. 정규화에서는 한 번에 원하는 것을 선택적으로 하는 것이 아니라 제1정규형을 끝내고 제2정규형 끝내고, 제3정규형 끝내는 식으로 일종의 공정처럼 진행되는 방식이라고 볼 수 있다.
6.2 제1정규화
First Normal Form 제1정규형을 만드는 제1정규화에 대해 살펴 보자. 제1정규화의 원칙은 Atomic Columns이다. 즉 각 행의, 그리고 각 컬럼의 값들이 하나하나가 Atomic 원자적이어야 한다라고하는 말의 의미가 무엇일까?
Atomic Columns의 의미는 각각의 컬럼이 값을 하나만 가져야 한다는 뜻이다. Fig 119의 한 행을 놓고 볼 때, Atomic의 원칙을 지키지 못한 컬럼은 tag 컬럼이다. tag는 하나의 값을 가지지 않고 2 개의 값을 가졌기 때문이다. 원하는 데이터 자체가 여러 개의 값을 가지고 있는 것이 하나의 값이면서 더 이상 쪼갤 필요가 없는 상태라면 문제가 없겠지만, tag의 값이 rdb 인 행을 찾으려고 하면 문제가 되는 것이다.
SELECT * FROM topic WHERE tag="rdb" 이 명령어는 tag값이 rdb인 행을 찾아내라는 뜻이지만 위의 표에서는 찾을 수 없다. 또한
SELECT * FROM topic ORDER BY tag 이 명령어는 tag 값에 따라 행을 정렬하려는 명령어이지만 tag 의 값이 2개인 경우 뒤쪽에 있는 값은 정렬에 영향을 줄 수 없다.
JOIN을 사용하려고 하는 경우에도, 하나의 컬럼에 여러 개의 값이 들어 있다면 JOIN을 하는 것이 어렵거나 불가능하다.
즉 원소의 값, 다시 말해 하나의 컬럼 값이 여러 개이라면, Atomic 하지 않다면 여러가지 문제점을 야기시킬 수 있기 때문에 그것은 해소 되어야 하며 그것이 해소되면 제1정규형을 만족시키는 표라고 말한다.
Fig 121 처럼 컬럼을 추가하면 유연하지 않은 방법으로 해결하려는 지름길로 가는 것이다.
이 문제를 해결하기 위해 이제 topic이라는 테이블과 topic 안에 있는 tag라는 테이블로 쪼갤 것이다.
하나의 Topic은 여러 개의 Tag를 가질 수 있고, 하나의 Tag도 여러 개의 Topic을 가질 수 있기 때문에 이 둘 사이의 관계는 N:M 관계로 볼 수 있다. N:M 은 테이블을 쪼개는 것만으로는 안되고 mapping table 연결테이블을 만들어줘야 한다.
이렇게 하여 기존에 tag 컬럼에 있었던 Atomic 하지 않은 컬럼의 상태를 해소할 수 있게 되었다. 제1정규형을 만족시키게 된 것이다.
6.3 제2정규화
Secodn Normal Form: 제2정규형을 만들기 위해서는 테이블 상에 부분 종속성이 없어야 한다 [No partial dependencies]는 조건을 만족시켜야 한다. 표의 기본키 중에 중복키가 없어야 한다는 뜻이다.
제1정규화가 된 표를 보면 중복이 된 부분을 볼 수 있다.
이 테이블에서 중복이 발생하는 이유는 바로 부분 종속성때문이다. 색을 칠한 부분은 사실 title이라는 primary key 하나에만 의존하고 있다. type의 값에는 영향을 받지 않는 부분인 것이다. description, created, author_id, author_name, author_profile 컬럼들은 title 컬럼에만 부분적으로 종속되어 있다. 그것은 price 컬럼 때문인데, price는 type 이 paper인지, online인지에 따라 달라지는 것이다. topic이라는 표는 사실, title, type, price를 위한 표이지 기타 다른 컬럼들과는 상관이 없는 것이다.
이 문제는 어떻게 해결할 것인가?
부분적으로 종속되는 컬럼들만 모으고 전체적으로 종속되어있는 컬럼을 다시 따로 쪼개는 것이다. 쪼개서 만든 topic 테이블에는 먼저 부분적으로 종속되는 정보들만 가져온다.
이제, price는 title과 type에 의존하기 때문에 그것들을 가져와 새 테이블을 만들고 topic_type이라는 이름을 지었다.
이렇게 제2정규형의 조건을 만족시키는 핵심적인 방법인 부분종속성에 대해 살펴 보았다. 표는 1개 더 추가 되어 모두 4개가 된다.
6.4 제3정규화
Third Normal Form: 제3정규형의 조건은 No transitive dependencies 이행적 종속성이 없어야 한다는 것인데, 그 의미를 살펴보자. 2NF에서 역시 topic 테이블을 자세히 보면, 한 행은 title이라고 하는 기본키에 종속되어있다.
첫 행을 놓고 본다면, MySQL이 그 행 전체를 대표하는 것이다. 그런데 author_id 컬럼은 title에 종속되어있긴 하지만, author_name, author_profile은 author_id 값에 의존하고 있다. 이렇게 author_id 는 title에 의존하고 있고, author_name, author_profile 은 author_id에 의존하고 있는 것을 이행적 종속성이라고 하며, 이행적 종속성을 가지고 있다는 것은 완벽한 관계형 DB 의 모습을 띠지 못했다는 의미이다. 노랑 색의 부분은 중복되고 있으며 중복은 시스템의 성능에 아주 부정적 영향을 준다. 이런 경우에는 중복을 만들어내는 부분을 따로 갈라내어 새로운 테이블을 만들고 중복되는 부분도 제거해 버린다.
그리고 author_id 컬럼의 컬럼명을 id로 바꾸었다.
이렇게 떼어내고 나서 topic의 테이블에 남는 것은 created 컬럼까지이지만, 이것만으로는 데이터가 부족하며 각각의 행이 어떤 author 테이블의 id를 가리키는지의 데이터도 필요하기 때문에 author_id 컬럼까지를 복사하여 topic 테이블로 한다.
이렇게 되면 중복이 제거되어 노랑색 부분을 없애고, topic의 author_id 는 Foreign key로서 중복으로 보지 않기 때문에 노랑색을 없애면 아주 환상적인 결과를 남기게 된다.
Fig 129 에서의 author_id 가 실제 상황에서는 테이블에 없을 수 도 있지만 [Fig 133], 서로 성격이 같은 author_name, author_profile 컬럼들은 내부적으로, 암시적으로 자신의 식별자를 가지고 있을 것이라고 봐야 하며 그렇다면 결국 눈에는 보이지 않지만 그 역시도 이행적 종속성을 가지고 있는 것이라고 판단하여야 한다.
그리고 그때 분리를 하면서 id 값을 만들어 Primary key ['author' 의 id]와 Foreign Key ['topic'의 author_id]로 연결을 시키면 되는 것이다.
직접 눈에 보이지 않는 것처럼 되어 있는 경우에도 인간은 사실 그 컬럼들의 성격에 대해 무의식적으로는 이미 알고 있는 듯도 싶다. 왜냐하면 이미 컬럼들의 이름을 붙일 때 author_id, author_name, author_profile 등과 같이 해당 컬럼의 성격들을 관통하는 성격의 이름을 앞에 이미 붙여줬으니 말이다. 'author_'라고.
그래서 때로는 그냥 만든 다음에 다시한번 돌이켜보고 무의식적으로한 선택들로부터 어떻게 쪼갤 것인가하는 방법들을 찾기내기도 한다. 즉 어떤 prefix가 있다고 하면 [예시에서는 'author_'] 그것은 독립시킬 수 있는 가능성이 매우 높다는 뜻이고 게다가 실제로 중복을 발생시킨다면 위에서 본 방법대로 그것을 분리시키면 될 수도 있다.
이렇게 제3정규형까지 살펴 보았는데 Fig 118 과 같이 여러 가지 정규형들이 존재한다. wikipedia에서 database normalization 으로 검색하면 해당 페이지를 볼 수 있다. https://en.wikipedia.org/wiki/Database_normalization
EKNF: Elementary Key Normal Form
BCNF: Boyce-Codd Normal From
ETNF: Essential Tuple Normal Form
DKNF: Domain-Key Normal Form
표에서 보다싶이 정규형은 한 번에 나온 것이 아니라 천천히 발전해온 것을 알 수 있다.
이에 대한 또 다른 내용을 다음 링크에서 참고해도 좋다. https://3months.tistory.com/193
http://www.studytonight.com/dbms/database-normalization.php
http://pronician.tistory.com/922
7. 물리적 데이터 모델링
논리적 데이터 모델링이 관계형 DB 패러다임에 잘 맞는 이상적인 표를 만드는 것이었다면, 물리적 데이터 모델링은 선택한 데이터베이스 제품에 맞는 현실적인 고려를 하는 방법론이다. 이 단계에서 가장 중요한 것은 성능이다. 특히 역정규화(반정규화, denormalization)의 사례를 집중적으로 다룬다.
- 7.1 물리적 데이터 모델링
- 7.2 역정규화 소개
- 7.3 역정규화: 컬럼을 조작해서 join을 줄이기
- 7.4 역정규화: 컬럼을 조작해서 계산을 줄이기
- 7.5 역정규화: 표를 쪼개기
- 7.6 역정규화: 관계의 역정규화
7.1 물리적 데이터 모델링
일단 중요한 것은 운영을 조금이라도 해보는 것이다. 데이터가 조금 쌓이고 처리량이 많아져야 어디가 느려지고, 어디는 느려지지 않는지에 대한 분별이 생길테니까. 그때 적당한 시점에서 각 query의 성능을 평가해보고 병목이 발생하는 지점을 집중적으로 해결하는 것이 바람직하다.
이때 사용해볼 만한 방법이 find slow query [여러 가지 query 가 동작할 때 특히 느려지는 query 들을 찾을 수 있는 방법이 제품마다 서로 다르기 때문에] 에 사용하는 제품의 이름을 붙여서 검색하여 어디서 병목이 발생하는가 하는 것을 찾는 것이다. 이런 방법을 이용하여 느려지는 지점을 찾았다면 성능을 향상시키기 위한 여러 가지 방법들을 적용해봐야 할 것이다. 최후의 보루는 이상적으로 정규화된 표의 구조를 손에 드는 것이다. 이러한 행위를 역정규화 혹은 반정규화라고 부른다. 그런데 역정규화는 혹독한 대가를 치뤄야 한다. 과연 그런 대가를 지불한 만한가 하는 것은 자신이 처해있는 상황에 따라 결정될 것이다. 따라서 역정규화를 시도하기 전에 다른 방법을 먼저 찾아보는 것이 바람직하다.
우선 고려해볼만한 방법은 index 이다. 인덱스는 행에 대한 읽기 성능을 비약적으로 향상 시킨다. 반대로 쓰기 성능은 과도하게 희생시킨다. 왜냐하면 쓰기가 이루어질 때마다 그 행이 index가 걸려 있다면 입력된 정보를 잘 정리 정돈하기 위한 복잡한 연산과정이 필요하기 때문이다. 이 과정에서 시간이 많이 걸릴 뿐만 아니라 저장공간을 더욱 많이 차지하기도 한다. 그럼에도 불구하고 index를 사용하는 이유는 그렇게 잘 정리정돈하면 엄청나게 빠른 속도로 읽을 수 있기 때문이다.
index 만으로 성능을 향상시키지 못했다면 데이터베이스를 이용하고 있는 application 영역에서 캐시와 같은 방법을 시도해보는 것도 좋다. 캐시 cache 는 고속기억장치로서, 입력에 따른 실행결과를 저장해두었다가 나중에 동일한 입력이 들어왔을 때 저장해둔 결과를 사용하는 걸 통해 DB에 부하를 주지 않는 것이다. 이렇게 하면 DB가 겪게 되는 부하를 획기적으로 줄여줄 수 있다.
여러 가지 수단을 강구했음에도 불구, 성능향상에 어려움이 있다면 이때 할 수 있는 것이 바로, 표의 구조를 바꾸는 일종의 외과수술이라 할 수 있는 역정규화 denormalization 을 시도해보는 것이다.
7.2 역정규화 소개
정규화를 통해서 만든 이상적인 표를 성능이나 개발 편의성을 위해 조작, 구조를 바꾸는 것을 역정규화라고 할 수 있다.
정규화를 한마디로 정의하는 것은 어렵지만 대체로 쓰기의 편리함을 위해 읽기의 성능을 희생하는 것이라고 할 수 있다. 왜냐하면 정규화를 하게 되면 표들이 여러 개로 쪼개지게 되는데, 쪼개진 표들을 다시 사용하게 될 때에는 JOIN을 통해 해야 하는데 그 JOIN은 매우 비용이 높은 작업이기 때문이다. 따라서 읽기의 성능이 희생되는 것이다. application을 운영하다보면 읽기가 매우 자주 일어나는데 그때 정규화로 인해 성능이 느려지는 경우가 종종 생긴다. 그럴 때에는 여러 가지 방법들을 먼저 시도해보고 최후의 수단으로 역정규화 denormalization, 즉 표의 구조를 다시 바꾸는 작업을 하게 되는 것이다.
한가지 기억해둬야 할 것은 일단 정규화를 한 다음에 역정규화를 하는 것이지 처음부터 정규화를 아예 하지 않은 표가 좋은 표일 수는 없다. 또한 정규화를 한다고 하여 성능이 반드시 떨어지는 것도 절대 아니기 때문에 정규화가 성능을 떨어뜨린다는 단순한 일반화도 바람직하지 않다.
구글 스프레드 시트에 denormalization 이라는 문서를 만듦. http://bit.ly/2WLMCko
T3 셀의 메모에 실습용 표를 만들기 위한 DB 명령어를 참고해도 좋다. 하지만 이 코드는 MySQL에서 작성한 것이기 때문에 DB 제품이 다르다면 그대로 쓰기 어려울 수 있다.
DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`profile` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `author` VALUES (1,'kim','developer'),(2,'lee','DBA');
DROP TABLE IF EXISTS `tag`;
CREATE TABLE `tag` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tag` VALUES (1,'rdb'),(2,'free'),(3,'commercial');
DROP TABLE IF EXISTS `topic`;
CREATE TABLE `topic` (
`title` varchar(50) NOT NULL,
`description` text,
`created` datetime DEFAULT NULL,
`author_id` int(11) DEFAULT NULL,
PRIMARY KEY (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `topic` VALUES ('MySQL','MySQL is ...','2011-01-01 00:00:00',1),('ORACLE','ORACLE is ...','2012-02-03 00:00:00',1),('SQL Server','SQL Server is ..','2013-01-04 00:00:00',2);
DROP TABLE IF EXISTS `topic_tag_relation`;
CREATE TABLE `topic_tag_relation` (
`topic_title` varchar(50) NOT NULL,
`tag_id` int(11) NOT NULL,
PRIMARY KEY (`topic_title`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `topic_tag_relation` VALUES ('MySQL',1),('MySQL',2),('ORACLE',1),('ORACLE',3);
DROP TABLE IF EXISTS `topic_type`;
CREATE TABLE `topic_type` (
`title` varchar(45) NOT NULL,
`type` char(6) NOT NULL,
`price` int(11) DEFAULT NULL,
PRIMARY KEY (`title`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `topic_type` VALUES ('MySQL','online',0),('MySQL','paper',10000),('ORACLE','online',15000);
역정규화를 진행하는 대상은
- 우선 하나의 표 안에서 컬럼을 바꾸는 것,
- 다음은 하나의 표를 여러 개의 표로 쪼개는 것,
- 세번째로는 테이블과 테이블 사이의 관계성을 조작하여 일종의 지름길을 만드는 것
이다.
마치 엄격하게 공정을 거쳐가듯이 순차적으로 진행하는 것이 정규화이지만 역정규화는 어떤 규칙이 있는 게 아니다. 역정규화는 상황을 판단하여 진행하는 것이기 때문에 일부 기법들에 대해 살펴보는 일종의 샘플을 살펴볼 것이며 무슨 법칙처럼 받아들일 필요는 없고 창의력을 발휘하여 자신의 상황에 맞게 현명하게 최선의 결과를 얻어낼 수 있는 방법을 선택하는 것이 중요하다.
7.3 역정규화: 컬럼을 조작해서 join을 줄이기
여기서는 하나의 테이블 안에서 컬럼 조작을 통해 성능을 향상시키는 방법을 살펴본다. 성능 향상에 있어 가장 중요한 대상 중의 하나는 JOIN을 줄이는 것이다. JOIN은 비용이 매우 높은 작업이다.
하려는 작업: topic_tag_relation 테이블과 tag 테이블이 있는데, topic_tilte 컬럼의 값이 MySQL인 행의 tag name을 알고 싶다.
이때 문제는 topic_tag_relation에는 태그의 id 값 tag_id가 있고 실제 태그의 이름은 그 id 값과 연결된 tag 테이블에 존재하기 때문에 JOIN이 필요한 상태란 것이다.
https://dev.mysql.com/downloads/workbench/
MySQL Workbench 워크벤치 를 이용하여 몇 가지 조작을 해보자. [역정규화의 내용은 지금 보는 MySQL처럼 특정 DB와는 관계없음]
Fig 136. SELECT * FROM topic_tag_relation WHERE topic_title = 'MySQL';
태그의 이름은 tag 테이블에 있기때문에 JOIN을 해야 한다.
Fig 137. SELECT * FROM topic_tag_relation AS TTR LEFT JOIN tag ON TTR.tag_id = tag.id WHERE topic_title = 'MySQL'; topic_tag_relation AS TTR 별명을 TTR로 해주고 ON TTR.tag_id 는 topic_tag_relation의 tag_id 컬럼을 의미
Fig 138. Fig 137에서 * 대신 tag.name을 넣어 이름만 가져온 결과
서비스를 운영하다보면 이런 조회가 아주 많이 일어날 수도 있는데, 그걸로 인해 시스템에 부하가 걸리고 사용자의 경험이 급격히 나빠진다면 대책이 필요하게 된다. 이때 선택할 수 있는 여러 가지 방법 중 하나는 topic_tag_relation 테이블안에 tag의 이름을 넣어버리는 것이다. 다시 말해 중복을 허용하는 것이다. 이렇게.
ALTER는 'topic_tag_relation' 테이블에 'tag_name' 컬럼을 추가 하는 쿼리[명령어]이다.
중복이 생기는 건 어쩔 수 없지만, JOIN을 하지 않고 이 표를 다룰 수 있게 된다.
JOIN을 하지 않고 결과를 가져오기 때문에 훨씬 더 빨리 처리할 수 있으며 이런 것을 역정규화라고 하는데, 역정규화를 하면 정규화를 하기 전에 마주했던 문제를 고스란히 다시 가지게 된다. 중복현상이 생기고 기존의 테이블도 그대로 가지고 있고, ..., 등등의 원인으로 시스템의 복잡도가 엄청나게 높아지고 프로그램이 고장날 확률이 훨씬 높아진다. 그런 불리한 문제들이 있음에도 불구 역정규화를 진행하는 것은 성능을 위해서이며, 처음부터 역정규화를 고려하고 정규화를 소홀히 한다는 것은 바람직하지 않다.
7.4 역정규화: 컬럼을 조작해서 계산을 줄이기
여기서는 데이터를 처리하는 로고 or 비용을 줄이기 위한 역정규화에 대해 살펴 본다.
하려는 작업: 각각의 저자별로 몇 개의 topic을 생성했는지를 알고 싶다
이를 위해서는 topic 테이블에 있는 author_id의 값들이 각각의 author_id 별로 author_id가 같은 행이 몇 개가 있는지를 세어보면 될 것이다.
우선 topic 테이블에서 author_id 별로 몇 개의 행들을 가지고 있는가를 출력해보자.
GROUP BY author_id: 집계함수 GROUP BY는 author_id 별로 그루핑을 해준다.
COUNT(author_id): author_id의 개수를 센다.
SELECT autho_id, COUNT(author_id) FROM topic GROUP BY author_id; 만일 이런 작업이 아주 빈번한 작업이라면 GROUP BY 를 사용하는 것이 아주 비용 높은 작업이 될 수도 있다. 행이 1억개 씩이나 된다면 비용이 꽤 높은 작업이 된다는 말이다.
그러면 Fig 141을 들여다 보면서 이런 생각을 해볼 수도 있을 것이다. author라는 테이블에 컬럼을 추가해서 각각의 저자가 몇 개의 글을 가지고있는지를 topic 테이블에 행을 추가할 때마다 author_id의 카운트 값을 1씩 증가시키면 어떨가라는 생각을 한다면 직관적으로 이미 역정규화에 대해 생각하고 있다는 의미가 된다.
topic_count라는 컬럼을 추가해 각각의 author 별로 글이 몇 개인지를 카운트한 값을 갖도록 한다. ALTER는 'author' 테이블에 'topic_count' 컬럼을 추가하는 명령어이다. 이제 SELECT id, topic_count FROM author; 를 사용하면 Fig 142의 GROUP BY를 쓰지 않고도 원하는 결과를 얻을 수 있다.
이미 계산되어 있는 결과를 가져오기 때문에 결과는 같지만 처리속도는 엄청 빠르다. 물론 author 테이블 안에 topic_count라는 값을 항상 유지시켜야 한다는 문제가 존재한다. 즉 이처럼 역정규화를 했을 때 얻는 것과 잃는 것이 분명히 trade-off가 있기 때문에 어느 쪽이 더 많이 얻는지를 잘 따져보고 현명하게 선택해야 한다.
7.5 역정규화: 표를 쪼개기
여기서는 하나의 표를 성능을 향상시키기 위해 여러 개의 표로 쪼개는 것이다. topic 테이블이 있는데 이것을 쪼개는 방법은 컬럼을 기준으로 쪼갤 수 도 있고, 행을 기준으로 쪼갤 수 도 있다.
먼저 컬럼을 기준으로 쪼개는 방법부터 살펴보자. topic 테이블에 있는 description 이라는 text 의 값은 용량이 아주 크며 description 이외의 나머지 컬럼들을 조회하는 operation과 description을 포함하여 조회하는 operation을 나눠볼 수 있도록 테이블을 쪼갠다.
이렇게 되면 topic에는 용량이 큰 description 컬럼이 없기 때문에 여러 가지 장점들을 가지게 되고 성능을 향상시킬 수 있게 된다. 이렇게 테이블을 쪼개고 나서 topic도 조회가 많고 topic_description도 사용량이 엄청나게 많다면 각각의 테이블을 서로 다른 컴퓨터에 저장하여 그곳에서 쓰기, 읽기와 같은 작업을 시키면 컴퓨터 한 대로 처리하던 일을 여러 대로 분산할 수 있게 되며 이를 통해 성능을 향상시킬 수 있게 된다.
이런 것을 sharding 샤딩이라고 하는데, DB를 운영하다가 성능의 한계가 느껴지면 여러 대의 컴퓨터로 scale out 하는 기법으로서 유지하기도 어렵고 난이도도 높은 기술이다. 최후의 수단으로 사용하는 방법이라고 할 수 있다.
다음으로 행을 기준으로 테이블을 분리하는 방법을 살펴보자. 무한으로 추가될 수 있는 행에 비해 컬럼의 수는 거의 제한되다 싶이 하여 컬럼을 기준으로 분리하는 방법은 한계가 있지만, 행을 기준으로 분리하는 방법은 이론적으로 한계가 없으며 관리하기가 매우 어렵다.
분리는 이런 식이다. 예를 들어 topic 테이블에서 사용자가 엄청많아 어마어마한 조회가 일어나는 서비스를 운영한다면 1~1000 의 authou_id 값에 대해서는 topic_1000 테이블, 1001~2000 의 author_id 값에 대해서는 topic_2000 테이블을 쓰게 하는 방식으로 할 수 있다.
이렇게 되면 application을 만들 때 author_id가 1번이라면 topic_1000 테이블이 있는 서버쪽으로 쿼리를 보내 거기에서 데이터를 처리하는, 즉 사용자에 따라 데이터를 처리하는 표를 구분하는 것이다. 각각의 물리적인 서버마다 서로 다른 표를 저장하고 서로 다른 조회를 처리하는 것을 통하여 아주 많은 처리량을 소화할 수 있게 되는 것이다. 하지만 이런 식으로 처리를 하는 것이 쉬운 일은 아니다. 노하우도 필요하고 사고 위험도 높다. 아주 코너에 몰렸을 때 쓰게 되는 수단이란 얘기다. 여러 가지 솔루션이나 방법론들을 적용하여 정교하게, 안전하게 처리해야 할 테크닉이다.
테이블의 역정규화는 대체로 여러 대의 서버로 분산할 때 사용할 수 있는 방법이라는 것을 알아두면 좋을 것이다.
7.6 역정규화: 관계의 역정규화
관계의 역정규화는 JOIN을 줄여서 지름길을 만드는 테크닉이라고도 할 수 있는데, 앞서 살펴 본 컬럼의 역정규화와도 사실 비슷한 내용이다. foreign key를 추가하는 것을 통하여 JOIN을 줄이는 테크닉이라는 점에서 약간의 차이가 있을 뿐이다.
하려는 작업: 어떤 특정한 저자의 태그 아이디와 태그명을 조회하고 싶다
topic_tag_relation 테이블과 tag 테이블을 JOIN하면 되는데 이 두개의 테이블에는 author_id 값이 없기 때문에 topic 테이블까지 JOIN에 참여시켜야 한다. 그것을 피하기 위한 역정규화 방법을 살펴 보고자 한다.
위의 명령어에 JOIN을 하면 아래의 결과가 나온다. topic_tag_relation 대신에 TTR이라는 별명 사용.
이때 author_id 가 1인 사람의 태그이름만 얻고자 하는데 author_id가 없어서 WHERE문을 쓸 수 가 없다. 따라서 topic 테이블을 추가로 JOIN 해야 한다.
이제 WHERE 문을 사용하여 원하는 author_id에 해당한 결과만을 추려낼 수 있다.
이런 operation이 대단히 자주 일어나는 경우 JOIN이 많아 성능을 저하시킬 위험이 있기 때문에 topic_tag_relation 테이블에 author_id 컬럼을 추가하여 그 값만을 갖고 조회를 할 수 있도록 하면 성능에 나쁜 영향을 주는 요소 [즉 JOIN] 를 줄일 수 있을 것이다.
Fig 154의 역정규화 쿼리는 MySQL 에 해당한 것이며 DB 제품마다 차이가 있을 것이다. 이렇게 해서 author_id 컬럼이 추가된 topic_tag_relation 테이블을 얻게 된다.
위에서 살펴본 역정규화의 예시가 별로 좋다고 볼 수는 없지만 어느 정도 이해에 도움을 줄 수는 있을 것이다.
8. 수업을 마치며
순수하게 이상적인 것을 향해 가다가 물리적인 모델링 단계에서 현실에 맞게 수정해야 한다.
고전에 속한 작품은 괜찮은 작품이지만, 고전에 속하지 못했다고 하여 괜찮지 않은 것은 아니다.
여기서 소개하지 않은 데이터 모델링 방법론이 얼마든지 있을 수 있고 훨씬 괜찮은 방법론도 있을 수 있다.