[DB Programming] 데이터베이스 설계 - 논리적 데이터 모델링, 물리적 데이터 모델링, ERD

BackEnd/Database Programming

[DB Programming] 데이터베이스 설계 - 논리적 데이터 모델링, 물리적 데이터 모델링, ERD

하노정 2022. 10. 17. 19:58

데이터베이스 설계 개요 -> 논리적 데이터 모델링 -> 물리적 데이터 모델링

데이터베이스 설계 개요

업무 처리 시스템을 개발한다고 해보자.

업무 처리 시스템에는 Business Logic, Databases가 있다.

프로세스 관점에서 업무 처리 시스템에 대해 Process Modeling을 하고, 데이터 관점에서 업무 처리 시스템에 대해 Data Modelling을 해 목표 업무를 처리한다.

데이터베이스 설계 단계

기업의 업무와 관련된 데이터베이스를 구축하기 위해서는 논리적 데이터 모델링, 물리적 데이터 모델링을 수행해야 한다.

요구사항 수집 및 분석
- 어떤 데이터가 필요하고 어떻게 사용되는지 분석
- 업무 분석, 기능적/비기능적 요구사항 도출, 요구사항 명세서 작성
논리적 데이터 모델링 (Logical Data Modelling)
- 개체-관계 (ER) 모델 이용
- 개체 및 관계 도출, ERD 작성
물리적 데이터 모델링 (Physical Data Modelling)
- 내가 사용할 DBMS에 맞게 설계
- 일반적으로 관계형 데이터 모델 이용 -> 테이블 생성 가능
- Table, Index, View, Constraints 등 정의
데이터 베이스 구축
- DBMS를 통해 물리적인 데이터베이스 구축

논리적 데이터 모델링

업무와 관련된 데이터를 분석하고 추상화하여 논리적인 데이터 모델을 설계하고, 개체-관계 모델(Entity-Relationship Model)이 널리 사용된다. 개체-관계 모델이란 개체 타입과 속성, 식별자 등을 정의하고 개체 타입을 사이의 관계를 기술하는 것이고 개체-관계 다이어그램(E-R Diagram)으로 표현한다.

논리적 데이터 모델링의 참여자

프로젝트 개발자: 업무 분석가, 데이터베이스 관리자, 소프트웨어 관리자 등
업무 수행자: 기업에서 업무를 수행하는 실무자, application 사용자

논리적 데이터 모델링 주의사항

명명법 등에 일관성 있는 규칙을 적용해야 함
실무자, 도메인 전문가 등과 협의해 수행해야 함
업무의 현재 모습 뿐만 아니라 향후 계획, 정책, 전략을 반영해야 함
업무의 변화에 따른 모델의 확장 및 변경이 용이해야 함
물리적 데이터 모델로 쉽게 변환 가능해야 함

논리적 데이터 모델링의 구성 요소

개체타입, 속성, 관계가 있다.

개체 타입(Entity Types) : 업무와 관련 있는 대상
- 업무에서 사용하고 관리하고자 하는 유형, 무형의 정보
- 관리의 대상, 영속적으로 존재하는 데이터들의 집합
- 예: 학생, 학과, 과목 등
- 식별자를 통해 구별 가능해야 함 (학생->학번, 학과->학과명, 과목->과목코드)
- 하나 이상의 속성을 가짐 (학생->학번, 성명, 소속학과명, 주소)
- 다른 개체 타입과 관계를 가짐 (학생은 학과에 소속됨, 학과는 과목을 개설함)
- 명명법
  - 단수 명사 사용 (학생들->학생 - 이게 나중에 테이블이 됨)
  - 실제 업무에서 사용하는 용어 사용 (학생번호->학번 - 이게 속성이고 나중에 테이블 컬럼이 됨)
  - 이해하기 어려운 약어보다는 구체적인 명칭 사용 (TA->Technical Architect)
  - 실제 생성 시 영어로 지정 (속성명, 테이블명, 컬럼명 고려)
- 개체 타입 정의 (개체 타입 도출 -> 개체정의서 작성 -> 검증 -> ER Diagram 작성)
  1. 개체 타입 도출
    - 업무와 관련된 모든 자료 참조, 현재 운영 중인 업무 시스템 분석, 실무자 인터뷰 및 현장 조사 실시, 업무에 필요한 정보인지/속성을 갖고 있는지/반복적으로 발생하는지 고려
      - 업무기술서, 문서양식, 인터뷰 문서 등에서 명사 추출
      - 개념이 불분명하거나 광범위한 대상 제거
      - 개체의 특성 또는 속성인 것은 제거 (일종의 값)
      - 포괄적인 업무 프로세스와 관련된 대상 제거 (취소, 자동, 입금 등)
      - 중복되는 대상(동의어) 제거
      - 누락된 개체 타입 유추해 추가
  2. 개체정의서 작성
    - 개체타입명: 선정한 개체 타입 이름 (예: 물품)
    - 설명: 개체 타입에 대한 구체적인 설명 (예: 사용자가 인터넷을 통해 경매에 접수한 물품에 관한 정보)
    - 속성: 개체 타입에 포함되는 속성 (예: 물품명, 종류, 크기, 무게 등)
    - 구분: 기본, 핵심, 행위 등 (예: 핵심)
    - 동의어: 선정한 명칭과 유사한 용어 (예: 매수신청인)
  3. 검증 및 E-R Diagram 작성
속성(Attributes) : 업무와 관련 있는 대상의 특성. 개체 타입에 속하는 세부 데이터
- 개체 타입을 기술하는 세부적인 특성
- 예: 사원 -> (사원번호, 이름, 성별, 주민등록번호)
- 개체 타입에 속한 모든 개체가 소유하는 정보
- 유형
  - 기본 속성: 개체 타입이 본래 갖고 있는 속성
  - 설계 속성: 설계 시 필요에 따라 추가한 속성 (예: 직무코드)
  - 유도 속성: 기존 속성으로부터 유도되는 속성 (예: 주민등록번호 -> 생년월일, 성별)
- 속성 정의 (속성은 업무상 필요한 정보를 나타내는 최소 단위)
  - 속성을 발견하는 작업은 개발 전까지 지속적으로 진행되어야 함. 데이터 모델링 단계에서 속성들을 완벽하게 식별하지 않아도 됨
  - 업무적으로 관리해야 할 속성이 있음에도 불구하고 도출된 개체타입이 존재하지 않을 경우 새로운 개체타입 생성을 고려
  - 하나의 속성이 시간에 따라 여러 개의 값을 가지며 그 값을 업무에서 관리해야 할 필요가 있으면 새로운 개체타입을 생성 (예: 이사를 자주 간다면, 집 주소가 계속 바뀌게 된다. 근데 만약 이사 history를 관리해야 한다면 별도의 개체타입을 정의하는 게 일반적이다.)
- 식별자
  - 개체 타입에 속한 개체들을 서로 구별할 수 있는 속성
  - Super Key: 고유하게 식별하는 모든 조합, 조합일 경우 최소성을 만족시키진 못함. 후보키에 불필요한 속성을 덧붙이면 후보키는 아니지만 여전히 슈퍼 키임
  - Candidate Key: 슈퍼키 중 더이상 줄일 수 없는 형태를 가진 것, 여러개 가능, 기본키가 될 수 있는 것들
  - Primary Key: 후보키 중 가장 적합한 주 키
  - Alternate Key: 후보키 중 주 키 제외한 것들
  - Natural Key vs Surrogate Key: Natural Key는 일반적인 키를 말하고, Surrogate Key는 대체 키와 비슷한 느낌으로, 회사 이름이라면 해당 이름이 아니라 다른 무언가(예:숫자)로 저장하는 것. 만약 이름이 50자라면 조인 시 다 비교해야하고 저장공간이 낭비되므로 이럴 경우 숫자로 저장하고 사용하는 게 더 효율적이고 편함(예: sequence)
  - Foreign Key: 식별자는 아니고, 다른 개체의 식별자를 참조함
- 식별자 정의
  - 주 식별자 속성
    - 개체마다 값이 반드시 존재해야 함
    - 개체타입에 속하는 모든 개체들을 유일하게 구별할 수 있어야 함
    - 해당 업무에서 자주 이용되는 속성을 사용 (예: 직원이라는 개체에 대한 주 식별자로는 사원번호, 주민등록번호 중 고를 때 -> 직원 개체타입은 사원번호를 많이 사용하므로 사원번호가 적합함. )
    - 값이 변할 수 있는 속성은 제외 (예: 학생 개체타입에서 이메일id는 바뀔 수 있으므로 값이 변하지 않는 학번이 적합함.)
    - 여러 개의 속성들을 조합해서 구성할 수 있지만 너무 많은 속성들의 조합은 바람직하지 않음. 자식 개체타입에 전이될 때를 고려하면 자식 개체타입의 주식별자도 복잡해짐. 따라서 필요에 따라 새로운 주 식별자를 도입하기도 함. (예: 설계 속성 - 접수번호)
  - 외래 식별자
    - 두 개체 타입 사이에 부모-자식 관계가 있을 때, 자식 개체 타입 쪽에 생성되는 속성
    - 관계의 종류에 따라 자식 개체타입의 주 식별자 또는 일반 속성으로 사용됨
      - 식별 관계: 주 식별자 역할
      - 비식별 관계: 일반 속성 역할
관계(Relationships) : 업무와 관련 있는 대상들 사이의 연관성
- 개체 타입들 사이의 연관성을 나타냄
- 유형
  - 존재에 따른 관계 (소속관계)
  - 행위에 따른 관계 (개설, 강의, 수강관계)
  - 1:1(one-to-one) 관계 (학생-신체정보의 소유관계)
  - 1:N(one-to-many) 관계 (학과-과목의 개설관계)
  - M:N(many-to-many) 관계 (학생-과목의 수강관계)
  - 식별 관계: 특정 개체 타입에 속한 각 개체(child/weak entity)가 다른 개체 타입에 속한 하나의 개체(parent/strong entity)와 반드시 관계를 갖고, child entity가 parent entity에 의해 식별되는 경우 (학생-신체정보의 소유관계)
  - 비식별 관계: 특정 개체 타입에 속한 개체가 다른 개체 타입에 속한 개체와 선택적으로 관계를 갖고, 상대 개체에 의해 식별되지 않는 경우 (학생-과목의 수강관계)
- 관계 정의 (관계 도출 -> 검증 -> 관계 정의서 작성 -> E-R Diagram으로 표기)
  - 개체 타입과 달리 업무기술서나 장표 등에 명확히 기술되지 않음, 업무 흐름의 내용을 파악하고 장표의 구성 등을 고려해 도출해야 함
    1. 관계 도출
      - 업무기술서, 장표 등에서 동사 추출
    2. 검증
      - 실무자와의 인터뷰를 통한 검토 수행
    3. 관계정의서 작성
      - 기준 개체 타입명: 관계 상에서 기준으로 삼는 개체 타입명 (예: 사원)
      - 관계 참여도: 기준 개체 타입의 관계 참여도(대응 수) (예: 각 사원은 하나의 부서에 속한다, 각 부서에는 여러 명의 사원이 존재할 수 있다. 각 사원은 여러 개의 주문을 접수할 수 있다. 각 주문은 한 명의 사원에 의해서만 접수된다)
      - 참여 방법: 필수/선택 (예: 필수, 선택, 선택, 필수)
      - 관련 개체 타입: 기준 개체 타입과 함께 관계에 참여하는 개체 타입명 (예: 부서, 부서, 주문, 주문)
    4. E-R Diagram으로 표기

도메인

개체 타입 내의 속성들에 대한 데이터 타입과 크기, 제약사항 등을 지정

도메인 정의 방법

모델 내의 모든 속성들을 나열
공통적으로 나타나는 속성의 명칭을 분리하고 그룹화하여 도메인 정의
도메인 별로 데이터 타입 및 크기, 제약사항 등을 지정
속성에 대해 도메인을 지정

물리적 데이터 모델링

논리적 데이터 모델을 기반으로 물리적 데이터 모델을 설계한다. 관계형 데이터 모델을 주로 사용하고, E-R 모델(E-R Diagram)을 관계형 모델로 변환한다. 사용할 DBMS의 특징을 고려해야 한다.

물리적 데이터 모델링 고려사항

기 정의된 논리적 데이터 모델을 기반으로 트랜잭션 분석 등을 통해 데이터베이스 스키마, 데이터 접근 방법, 물리적 저장 구조 등을 설계함
- 트랜잭션 분석: 사용자들이 데이터베이스를 어떻게 사용하는지 분석하는 것이다. 주로 검색을 할텐데 어떤 테이블과 컬럼을 많이 검색할 것인지 분석한다. 정확하게는 실 사용 후에 모니터링 해봐야 알지만 물리적 설계 단계에서 분석 후 결정해볼 수 있다.
- Index를 모두 붙일 수 없어서 트랜잭션 분석의 결과에 따라 자주 사용할 것 같은 것에 붙인다.
필요 시 분산 환경을 고려해 설계 수행

E-R Diagram

개체-관계 모델링의 산출물로, ERD라고 한다. 데이터 모델링 과정에서는 데이터 모델을 그림으로 표현하기 위한 표시법을 필요로 하고 ERD는 개념적 데이터 모델의 한 타입이다.

E-R Diagram 변환

개체 타입 -> Table 정의
- 주 식별자 -> PK
- 속성 -> Column (이때 타입도 결정)
- 외래 식별자 -> FK
- 개체 타입명이 Table 명이 되고, 개체 속성이 유도 속성일 경우 Table에서 Column으로 생성하지 않아도 됨
- 약 개체 타입 및 식별 관계를 테이블로 변환
  - 약 개체 타입이면서 식별 관계를 갖는 경우, 강 개체 타입의 PK와 약 개체 타입의 PK를 Table의 PK로 지정하고 강 PK는 강 개체 타입의 Table을 참조하는 FK로 정의
관계 -> 필요 시 Table 정의 (반드시 x)
- 1:1, 1:N 관계
  - 속성이 없는 경우: Table 정의 필요 x
  - 속성이 있는 경우: Many 쪽 개체에 대응되는 테이블의 컬럼으로 Table 정의
  - 1:1 관계의 변환
    - 두 개체 타입이 관계에 전체 참여이면 하나의 테이블로 병합하여 정의 가능
    - 부분 참여이면 Null 값 저장을 피하기 위해 별개의 테이블로 정의 - 두 테이블의 조인을 위해 별개 테이블을 참조하는 FK를 정의해야 함. FK는 부분참여의 주체가 아닌 테이블에 저장하는 게 좋음 (1:1 관계 변환 시 고려사항)
  - 1:N 관계의 변환
    - Many 쪽 Table에 One 쪽 Table을 참조하는 FK를 생성
    - 관계에 속하는 속성은 Many 쪽 Table에 포함시킴
- M:N 관계
  - 양 쪽 개체타입의 주 식별자들과 관계 자체의 속성들을 컬럼으로 갖는 테이블 정의
  - 연관 개체에 해당하는 Table이 생성되고, 관계 레코드를 표현한다.
  - 물리적 모델링에는 M:N 관계라는 게 존재하지 않으므로 Table을 정의해야 함
  - M:N 관계의 변환
    - 관계를 하나의 Table로 정의
    - 관계에 속하는 속성을 포함하고, 두 개체타입의 식별자를 각각 FK로 정의
    - 논리적 모델링 단계에서 연관 개체 타입으로 설정해서 물리적 모델링 단계에서 자동 생성되도록 해야 함
다치 속성의 변환
- 여러 값을 갖는 다치 속성에 대해 하나의 Table 정의
- 개체 타입의 PK를 참조하는 FK를 포함
N차 관계의 변환
- 개체 타입 N개 사이의 관계를 갖는 것을 말함
- N차 관계에 대해 하나의 Table 정의
  - 관계에 참여하는 각 개체타입의 식별자를 FK로 정의
  - PK는 FK들의 조합을 이용하거나 새로운 키 속성을 정의
- 예: 3차 관계 -> 3개의 2차 관계
  - 3차 관계, 3개의 2차 관계를 Table로 각각 정의
  - PK가 FK 조합 이용
일반화/구체화 관계에 있는 상위/하위 개체 타입 변환 방법
- 일반화/구체화 관계 = 상속 관계 (예: 정규직/임시직)
  - rollup: 상위/하위 개체 타입을 통합해 변환 -> 1개의 Table 생성
  - rolldown: 하위 개체 타입 별로 Table 생성 (상위 개체 타입은 중복해 표현) -> 2개의 Table 생성
  - 상위 및 하위 개체 타입을 별도의 Table로 변환 -> 3개의 Table 생성

정규화 및 역정규화

정규화(Normalization)
- 자료의 손실이나 불필요한 정보의 도입 없이 데이터의 일관성 확보, 데이터 중복의 최소화, 자료의 삽입/갱신/삭제에 따른 이상 현상 제거 등을 위해 Table을 여러 Table로 분리하는 것
- 종류: 1NF, 2NF, 3NF, BCNF, 4NF 등
- 개체 타입 정의할 때 문제가 시작된다. 도메인/개체 타입 정의할 때 너무 광범위하게 정의했을 때 물리적 모델링 단계에서 Table을 설계하면 속성 개수가 많은 건 상관 없지만 관계에 문제가 생길 수 있다. CRUD 중 RUD 시 문제가 발생할 수 있다. 삭제를 할 때 다른 정보도 삭제될 수 있다. 그래서 설계를 잘 해야 하고 설계 단계에서 이런 문제를 해결해야 한다.
역정규화(De-normalization)
- 정규화된 개체 타입, 속성 및 관계에 대하여 시스템 성능 향상, 개발 및 운영의 효율을 높이기 위해 정규화를 역으로 진행함. Table 통합
- 꼭 Table이 나뉘어져 있다고 해서 조인 시 안 좋기만 한 것은 아니지만 조인 시 성능에 문제가 생길 수 있어서 그럴 경우 Table 통합한다. 중복 값이 저장되는 것도 성능 문제기에 나중에 고려할 사항이다.