[db] 데이터 모델링

Posted on | Edited on | In | Comments:

데이터 모델링은 DBMS 사용에 가장 중요한 부분이면서 가장 쉽게 간과되는 부분이기도 하다.
데이터 모델링은 크게 논리 모델링과 물리 모델링으로 나눌 수 있다.
제대로 표현되고 있는 곳은 잘 없지만,
원래 논리 모델링과 물리 모델링의 차이는 테이블/칼럼 등의 이름이 영어냐 한글이냐가 아니라, 모델에 표현하려는 것이 업무냐 시스템이냐의 차이다.
업무를 분석하여 그에 대한 데이터 집합/관계를 중점적으로 표현하는 것이 논리 모델링이고,
그 산출물을 시스템으로 어떻게 표현할지 고려하는 것을 물리 모델링이라고 볼 수 있다.

모델링 용어

ERD상에 표현되는 오브젝트는 논리 모델링이냐 물리 모델링이냐에 따라 각각 이름이 다르게 표현된다.

논리 모델 물리 모델
엔티티(Entity) 테이블(Table)
속성, 어트리뷰트(Attribute) 컬럼(Column)
관계, 릴레이션(Relation) 관계, 릴레이션(Relation)
키 그룹(Key group) 인덱스(Index)

논리 모델링

엔티티

객체지향 언어에서 클래스와 동급의 의미다.
일반적으로 2개 이상의 속성을 가지고, 1개 이상의 레코드를 가지는 오브젝트를 말한다.
엔티티를 도출할 때 가장 중요한 것은 용어의 정의다.
해당 용어가 의미하는 범위가 어디까지인지 명확히 하고, 그에 걸맞는 이름을 부여해야 한다.
그래야만 다음으로 도출할 속성이나 식별자, 관계가 명확해질 수 있다.

엔티티

ERD 에서 엔티티는 이와 같이 표현한다
박스 외부에는 엔티티의 이름을 적고, 박스 내부 상단에는 PK, 하단에는 일반 속성을 나열한다

엔티티 종류

엔티티는 크게 키 엔티티, 메인 엔티티, 액션 엔티티로 구분할 수 있다.

키 엔티티는 대상 데이터 중 가장 최상위에 존재하는 엔티티이며, 일반적으로 메인 엔티티와 액션 엔티티를 만들어내는 부모 역할을 한다
일반적으로 현실에 존재하는 객체를 표현하는 경우가 많다. 사원, 고객, 상품 등의 엔티티는 대표적인 키 엔티티이다.

이러한 키 엔티티간의 작용으로 만들어지는 엔티티를 액션 엔티티라고 한다
예로는 구매, 계약 등이 있다

이러한 액션 엔티티들 중 서비스에서 상당히 중요한 역할을 하는 엔티티들을 메인 엔티티 라고 한다
위에서 언급한 구매, 계약 등은 사실상 메인 엔티티의 대표적인 예시이다
액션 엔티티도 나중에 업무가 변화하고 확장되면 메인 엔티티로 향상될 수 있다.

메인 엔티티

엔티티 작명

엔티티의 이름은 복수형 표현을 사용하지 않고 별도의 수식어가 없는 단순 또는 복합 명사 형태를 사용한다.

  1. 복수형 표현의 예로 목록, 리스트 등의 단어가 있는데, 테이블 자체가 이미 레코드의 목록을 저장하는 객체이므로 이 같은 이름을 사용할 필요는 없다.
  2. 엔티티의 이름에 수식어가 있다면 주의해서 검토하고, 필요하다면 통합하는 것이 좋다. ex) 상품(O), 고객용상품(X), 직원용상품(X)
  3. 애매모호한 단어도 피하는 것이 좋다. ex) 사원(O), 사원정보(X)

어트리뷰트(속성)

엔티티가 가지고 있는 속성으로써, 더 이상 분리될 수 없는 최소의 데이터 보관 단위이다.

어트리뷰트 원자성

어트리뷰트는 반드시 독자적인 성질을 가지는 하나의 값만을 저장해야 한다.
그런데 값의 최소 단위(하나의 값)라는 것이, 표현되는 서비스에 따라 달라질 수 있다.
예를 들면 주소가 있다
주소를 시군구, 읍면동 단위로 조작하는 행위가 많다면 속성이 시, 군, 구 등으로 잘개 쪼개지겠지만, 그렇지 않다면 굳이 잘게 쪼개어 관리를 어렵게 할 이유는 없다.

위와 같은 이유가 아닌 상태에서 어트리뷰트에 여러 값을 저장하는 행위는 지양해야 한다.
예를 들면 회원 취미 정보를 하나의 어트리뷰트에 구분자로 한꺼번에 저장하는 경우이다.
이는 어트리뷰트의 원자성에 위배되며 물리 모델링(성능)에 나쁜 영향을 미칠 가능성이 크다.

어트리뷰트 작명

어트리뷰트는 그 이름 자체만으로 그 의미를 이해할 수 있게 작명하는 것이 좋다.
사람들은 대부분 어트리뷰트의 이름을 최대한 간단히 작명하려는 경향이 있는데, 이름을 너무 간략히 작성하면 나중에 그 의미를 혼동하기 쉽다.
아래는 잘못된 어트리뷰트 명명의 예시이다.

너무 간략히 작명된 속성명

속성을 하나씩 살펴보자.

  1. 번호
    어트리뷰트의 이름은 최소한 범위를 한정하는 한정자와 값을 표현하는 명사로 구성해줘야 한다.
    번호의 경우 값을 표현하는 명사만 사용되어 가독성이 떨어진다. 누군가는 이를 회원번호가 아니라 전화번호라고 생각할 수도 있다.
    회원아이디, 회원일련번호 등으로 변경하는 것이 좋다.
  2. 주소
    지금은 어느정도 전달력이 있지만 만약 이 외에 사무실 주소라는 어트리뷰트가 추가로 있다면, 이 어트리뷰트가 자택 주소를 나타낸다는 보장이 없어진다.
    이러한 경우 때문에 단어 하나로만 구성된 어트리뷰트는 배제하도록 노력해야 한다.
  3. 상태
    이게 무슨 상태를 나타내는 것인지는 ERD를 설계한 사람만이 알 것이다.
    게다가 실수로라도 여기에 설계자가 의도한 값 외의 값이 들어가게 된다면, 이 어트리뷰트는 거의 쓰레기 수준으로 관리될 것이다.
  4. 로그인
    번호와는 반대로 범위를 한정하는 한정자만 사용된 케이스이다.
    로그인 일시를 의미하는건지, 로그인 IP를 의미하는 건지 전혀 알 수 없다.

1번에서 언급하였듯이 어트리뷰트의 이름은 범위를 한정하는 한정자 + 값을 표현하는 명사로 구성하는 것이 가장 이상적이다.
어트리뷰트의 이름이 너무 길어지면 물리모델링 과정에서 까다로워지므로 단어 2~4개 정도를 결합해서 사용하는 것이 좋다.

식별자(프라이머리 키)

식별자는 본질 식별자와 실질 식별자로 나눌 수 있다.
본질 식별자는 엔티티의 레코드가 생성될 수 있는 조건을 알려주는 식별자를 의미한다.
위의 구매 테이블에서 보이듯이, 고객아이디 + 상품코드 + 구매일자가 있어야 레코드가 한건 생성될 수 있으므로, 이 3개의 어트리뷰트가 묶여서 본질 식별자가 되는 것이다
실질 식별자란 실질적으로 테이블에서 식별자로 사용하고 있는 값을 의미하느데, 구매 테이블의 경우 본질 식별자를 그대로 실질 식별자로 사용하고 있다

근데 여기서 문제가 되는게, 보통 구매 엔티티의 경우 주문이력, 상태변화 등 수많은 자식 엔티티를 가질 가능성이 상당히 높다.
근데 현재와 같이 본질 식별자를 식별자로 사용할 경우, 자식 엔티티의 경우 부담해야하는 어트리뷰트의 개수가 계속해서 많아지게 된다.
그러므로 인위적인 값을 생성하여 이를 실질 실별자로 사용하는 경우가 많다.
이때 사용되는 식별자를 인조 식별자라고 한다.

인조 식별자 사용

관계(릴레이션)

엔티티간 상호작용을 표현해주는 것을 말한다.
엔티티와 동일하게 매우 중요한 역할을 수행한다. 관계없이 엔티티만 있는 ERD는 ERD로 볼 수 없다.

식별 관계와 비식별 관계

부모 엔티티의 식별자가 자식 엔티티의 식별자로 포함될 경우 식별 관계, 그냥 일반 어트리뷰트로 포함될 경우 비식별 관계라고 한다.

식별 관계와 비식별 관계
부모 엔티티가 자식 엔티티를 만들어 내는데 필수적인 역할을 하고 있을 경우 식별 관계를 형성할 대상이 될 수 있다.
하지만 그 대상을 모두 식별 관계로 형성하면 자식의 식별자가 너무 많아지므로 관계 중 유일성을 보장할 수 있는 최소한의 대표 관계만 식별 관계로 선택하고, 나머지는 비식별 관계로 선택하는 것이 좋다.

관계의 기수성(Cardinality)

부모 엔티티의 레코드 하나에 자식 엔티티의 레코드가 얼마나 만들어질 수 있는지를 의미한다.
정확히 몇 건이냐를 표시하는 것이 아니라, 0건, 1건, N건(1건 이상) 으로 구분해서 표시한다.
기수성은 관계선의 양쪽 끝에 표시하며, 나타내는 법은 아래와 같다.

관계의 기수성
(이 그림 하나 그린다고 30분을 넘게 썼다… 역시 난 미술이랑 안맞다 ㅋㅋㅋ)
그리고 아래는 실제 표시되는 형태이다.

관계의 기수성 표기

아래는 간단한 예시이다.

회원,주문 관계
한명의 회원은 한번도 구매를 하지 않을수도, 1번 이상 구매할 수도 있음을 나타낸다.

관계의 형태

모델링에는 수많은 관계들이 나타나겠지만, 대표적으로 많이 나타나는 몇개의 패턴들이 있다.

  1. 계층 관계
    부모와 자식간의 직선적인 관계가 연속되는 형태를 말한다.
    계층 관계
    자식 엔티티로 갈수록 식별자의 개수가 많아지므로, 적절한 수준에서 식별자를 인조 식별자로 대체하는 것이 좋다.
    업무에 따라 다르지만 보통은 2~4단계에서 대체하는 것이 일반적이다.

  2. 순환 관계
    하나의 엔티티가 부모임과 동시에 자식이 되는 재귀적인 형태를 말한다.
    순환 관계
    MySQL은 Oracle과 달리 재귀 쿼리가 지원되지 않는데, 이를 이유로 순환 관계를 피하는 모델링은 잘못된 방식이다.
    그러한 이유로 순환 관계를 계층 관계로 풀어봤자 나아지는 것은 아무것도 없고, 결국에 더 복잡해질 뿐이다.

  3. N:N 관계
    보통의 데이터 모델에서는 1:N 관계가 90% 정도를 차지할 정도로 많이 존재히나, 가끔씩 N:N(다대다) 관계도 등장한다.
    아래는 N:N 관계의 대표적인 예시다.
    N:N의 논리표현법
    학생은 여러개의 과목을 수강할 수 있고, 과목은 여러 학생에 의해 수강될 수 있다
    그러나 이런 표기법은 논리모델에서나 가능하고, 물리모델에서는 불가능하다.
    즉 물리모델에서는 다른 방식으로 풀어야한다.
    N:N의 물리표현법
    이처럼 2개의 1:N 관계로 풀어줘야 한다. 이를 N:N 관계 해소 라고 한다.
    수강과 같은 엔티티를 관계 엔티티라고 표현한다.

SNS의 팔로잉,팔로워
이는 요즘에 유행하는 SNS의 팔로잉, 팔로워의 대표적인 예시이다.

엔티티 통합

ERD를 작성하다 보면 엔티티를 구성하는 어트리뷰트와 관계가 비슷한 엔티티를 자주 보게 된다.
관계가 비슷하다는 것은 용도가 비슷하다는 의미인데, 이런 엔티티는 통합의 대상이 아닌지 주의깊게 살펴보는 것이 좋다.
아래는 통합의 간단한 예시다.

엔티티 통합
법인고객과 개인고객이 서로 많은 어트리뷰트를 공유하고 있어 이를 하나의 엔티티로 합친 예시이다.
만약 두 엔티티의 어트리뷰트 차이가 꽤나 난다면, 둘의 공통 속성을 모아서 하나의 통합 엔티티로 만드는 방법도 고려해볼 수 있다.
또한 이 서비스에서 어떤식으로 엔티티나 어트리뷰트에 접근하게 될 지도 고려하면서 통합이나 분리를 선택하는 것이 가장 좋다.

관계 통합

관계 또한 통합하는 과정을 거치는 것이 좋다.

관계 통합
왼쪽 관계의 경우 고객의 수가 늘어나는 등의 요구사항에 대응하기 어려우므로, 오른쪽과 같이 고객들을 별도의 엔티티로 분리해주는 것이 좋다.
참고로 관계를 통합하면 조인이나 저장되는 테이블이 늘어나서 개발이 번거로워질수 있다.
하지만 대체로 관계의 통합은 성능적 이슈보다는 업무에 유연하게 대응하기 위한 것이다.

모델 정규화

만약 데이터를 하나의 테이블에 다 때려넣으면 어떻게 될까?
불필요한 공간 낭비는 기본이고 사람이 관리하기도 매우 힘들것이며
삽입이상, 갱신이상, 삭제이상과 같은 부작용 또한 초래할 수 있게 된다

이상현상(abnomaly) : https://yaboong.github.io/database/2018/03/09/database-anomaly-and-functional-dependency/

정규화란 중복과 이상현상이 발생하지 않도록 데이터를 적절한 기준으로 나눠서 저장하는 것을 말한다
객체지향 프로그래밍에서 중복을 제거하기 위해 객체들의 관심사를 분리하는 과정과 비슷하다고 보면 된다
(데이터베이스에서 관심사를 발생하지 않아 발생하는 문제의 경우 코드보다 훨씬 치명적이다)

제1정규화(No Reapeating Group)

제1정규화의 요건은 모든 속성은 반드시 하나의 값을 가져야 한다이다.
아래와 같이 하나의 어트리뷰트에 여러개의 값을 저장하거나, 하나의 엔티티에서 똑같은 성격의 어트리뷰트가 여러번 나열되는 것은 제1정규화를 위반한 것이다.

제1정규화

제2정규화(Whole Key Dependent)

제2정규화의 요건은 식별자 일부에 종속되는 어트리뷰트는 제거해야 한다이다.
엔티티의 식별자를 구성하는 어트리뷰트가 2개일떄, 그 엔티티의 모든 어트리뷰트가 식별자 모두에 완전하게 종속적이어야 한다.

제2정규화
친구회원명이라는 어트리뷰트는 식별자 중 친구회원번호에만 종속관계를 가진다.
이는 제2정규화를 위반한 것이다

제3정규화(Non-Key Independent)

제3정규화의 요건은 식별자 이외의 속성간에 종속관계가 존재하면 안된다이다.

제3정규화
직업명은 직업코드에 종속적인 어트리뷰트이므로, 따로 엔티티로 분리하며 제거하였다.

물리 모델링

논리 모델링을 통해 나온 산출물을 RDBMS의 특성에 맞게 변환하는 작업이다.

프라이머리 키 선택

물리 모델링에서는 인덱스라는 존재 때문에 프라이머리 키 선택을 더욱 신중하게 해야 한다.

앞의 논리 모델링에서도 고려했던 인조 식별자 사용 여부를 물리 모델링에서도 고려하게 되는데, 여기서 인덱스의 성능까지 추가적으로 생각해줘야 한다.
InnoDB는 프라이머리키에 의해 클러스터링 되는 스토리지 엔진인데, 이러한 엔진에서는 프라이머리 키를 레코드의 주소 대신 사용하기 때문에 프라이머리키를 구성하는 컬럼의 개수가 많아지면 성능적으로 이슈가 발생할 수 있다

알다시피 인덱스는 레코드의 주소를 가지고 있고, 이 말인 즉 프라이머리 키가 많을 경우 인덱스의 크기도 같이 비대해짐을 의미한다
인덱스의 크기가 작을 경우 차이가 미비하지만, 크기가 커질 경우 그 차이가 확연하게 드러나게 된다.

디스크를 차지하는 크기가 커진다는 것은 그만큼 많은 디스크 입출력을 필요로 하고, 메모리에 캐시나 버퍼링을 하기 위해 더 많은 물리적 메모리가 필요하다는 것을 의미한다.

그러므로 프라이머리 키는 적절한 선에서 인조 식별자를 선택해주는 것이 좋다
그리고 추가로, 프라이머리 키 또한 인덱스로 사용되므로 반드시 SELECT의 조건절에 자주 사용되는 컬럼 위주로 순서를 배치해줘야 한다

데이터 타입 선정

물리 모델링에서 칼럼의 데이터 타입은 가능한 한 최소 단위의 타입을 부여해야 한다.
레코드의 개수가 많아지면 데이터 타입 한 바이트라도 많은 차이를 만들어내기 때문이다.

데이터의 타입

데이터의 타입은 저장하려는 데이터의 성격 그대로 타입을 선정하는 것이 가장 좋다.
숫자나 날짜 데이터를 모두 문자열 칼럼에 저장해도 아무런 차이가 없다면 처음부터 MySQL에 이렇게 많은 데이터 타입이 제공되지 않았을 것이다.

만약 저장할 데이터 타입이 명확하지 않고 두개의 데이터 타입 중간쯤 위치해 애매하다면, 그 두 데이터 타입의 장단점을 비교해 선택하는 것이 좋다.
대표적 예시로는 IP주소가 있다.

컬럼의 길이

항상 우리는 칼럼에 저장될 데이터의 최대 길이만을 생각하여 길이를 지정하는 경향이 있는데, 이것보다는 우리가 저장할 데이터가 어떤 특성을 가지느냐에 따라 칼럼의 길이를 결정해야 한다.
예를 들어 URL 데이터를 저장하는 칼럼의 경우,
URL의 최대 길이에 집중할 것이 아니라 우리 서비스에 필요한 URL 길이만을 생각하면 된다.
그리고 인조식별자에 INTEGER 대신 BIGINT를 사용하는 것도 어떻게 보면 너무 과도하게 멀리 생각하는 행위라고 볼수도 있다.

문자집합

문자열 타입에서 문자열이 어떤 문자집합을 가지는지도 상당히 중요한 문제이다.
특별히 지정하지 않으면 MySQL 서버의 default-character-set에 지정된 문자집합을 사용한다.
하지만 명확한 기준이 있다면 latin1, utf8를 같이 사용하여 데이터 저장 공간을 줄이는 것도 좋다.

컬럼의 길이나 문자집합을 신경쓰는 이유는 데이터가 디스크를 많이 사용하는 것을 막기 위함이다.
MySQL에서는 임시테이블/버퍼작업(정렬, 그룹핑 등)을 위해 별도의 메모리 할당이 필요하다.
이때 MySQL 서버는 실제 저장된 데이터 길이로 메모리를 할당하는 것이 아니라, 데이터 타입에 명시된 길이를 기준으로 메모리를 할당하고 사용한다.
그런데 이 메모리 공간이 일정 크기 이상을 초과하면 메모리가 아니라 디스크에서 처리된다.
즉, 테이블 컬럼이 과도하게 크게 설정되면 메모리로 처리되어야 할 것이 디스크로 처리될 수도 있다.

NULL, NOT NULL

InnoDB의 경우 NULL이 저장되는 칼럼은 전혀 디스크 공간을 사용하지 않는 특징이 있다.
즉 NULL을 저장함으로써 디스크 공간을 줄일 수 있는 것이다.
하지만 SELECT가 많이 발생하는 컬럼의 경우 NULL을 저장하는 행위를 지양하는 것이 좋다.
MySQL에서 NULLABLE 컬럼에 IN 연산등을 했을 경우 굉장히 이상한 비교 작업을 내부적으로 하기 때문이다.

반정규화

정규화는 데이터의 저장 비용을 최소화하는 역할을 담당하기 때문에,
진행할수록 테이블의 개수나 컬럼의 개수가 증가하게 되고, 이는 결국 SELECT의 부담으로 이어진다.
반정규화는 COUNT나 GROUP BY 같은 컬럼들을 미리 집계하여 별도로 저장하여 데이터를 읽어오는 비용을 최소화하는 작업을 말한다.

컬럼 복사

조인을 없애기 위해 원본 컬럼의 값을 변경하지 않고 그대로 다른 테이블로 복사해두는 형태를 말한다.
복사해온 컬럼을 이용해 GROUP BY나 ORDER BY를 인덱스로 할수있다면 성능에 상당히 도움이 될 수 있다.
하지만 복사해온 컬럼이 자주 변경된다면 비효율적인 작업이 될 것이다.
그러므로 읽기와 변경의 비율을 따져보고 컬럼 복사를 진행하는 것이 좋다.

요약 칼럼

어떠한 계산의 결과로 만들어진 값을 저장해두는 컬럼을 의미한다.
대부분 여러 레코드의 건수, 최대값, 최소값등을 미리 계산해서 저장해두는데 사용한다.
계산된 결과가 빈번히 호출되고, 매번 계산하기가 부담스러울 때 사용하면 좋은 방법이다.

요약 컬럼
하지만 계산의 결과가 빈번히 변경된다면 문제가 될수있다.
이처럼 잦은 데이터의 변경은 문제가 될 수 있으므로, 20-30분에 한번씩 도는 배치를 통해 계산 컬럼을 변경해주는 것이 가장 효율적인 방법이다.

해시 인덱스

해시 인덱스는 칼럼의 원래 값을 인덱싱하는 것이 아니라, 길이를 훨씬 줄인 해시값으로 인덱스를 구성하는 방식이다.
하지만 MyISAM이나 InnoDB에서는 이를 지원하지 않으므로, MD5 함수를 통해 이를 흉내내야 한다.
방식은 간단하다. 별도의 해시 저장용 컬럼을 만들고 그곳에 인덱스를 생성하는 것이다.

해시 인덱스
그리곤 아래와 같이 사용할 수 있다.

1
2
3
SELECT *
FROM temp
WHERE hash_url=MD5('http://....');

url에 직접 인덱스를 생성할 필요도 없고, 만약 url컬럼에 인덱스를 생성하지 못하는 경우에도 유용하게 사용할 수 있다.
기본적으로 InnoDB는 767바이트까지만 인덱스를 생성할 수 있기 때문이다.

참고 : 이성욱, 『Real MySQL』, 위키북스(2012)