[데이터 아키텍처 준전문가(DAsP)] 과목 4. 데이터 모델링 (4) 물리 데이터 모델링

1. 물리 데이터 모델링 이해

1) 물리 데이터 모델

• 논리 데이터 모델을 특정 데이터베이스로 설계하여 생성된 데이터를 저장할 수 있는 물리적인 스키마
• RDBMS 환경에 맞게 명칭 및 데이터 타입을 논리 데이터 모델링 산출물 기반으로 지정
• 실제 RDBMS 환경에 최적화된 물리적 테이블명과 컬럼명, 데이터 타입 등을 지정

2) 물리 데이터 모델 의의

• 논리 데이터 모델링에서 도출된 내용 변환을 포함하여 데이터 저장공간, 데이터의 분산, 저장 방법 등을 함께 고려하는 단계

3) 논리 데이터 모델 → 물리 데이터 모델

논리 모델링	물리 모델링
Entity	Table
Attribute	Column
Primary UID	Primary Key
Secondary(Alternate) UID	Unique Key
Relationship	Foreign Key
Mandatory	Not Null
Optional	Null
Business Constraints	Check Constraints

 

 

2. 물리 요소 조사 및 분석

항목	내용
시스템 구축 관련 명명 규칙	사내 시스템 구축과 관련된 명명 규칙 파악하여 적용
하드웨어 자원 파악	• CPU, MEMORY, DISK, I/O Controller, Network • 현재 처리 가능 속도, 집중 부하 발생 시간대, 동시 접속 가능 수
운영체계 및 DBMS 버전 파악	• 운영체제와 관련 요소를 파악하고 관리되고 있는지 파악 • 특히 인스턴스 관리 기법
DBMS 파라미터 정보 파악	• 환경 적용 단계에서 가장 중요하게 고려하는 단계 • 저장공간 관리 기법과 메모리 관리 기법 등에 관한 파라미터 • 쿼리에 사용하는 옵티마이저 운영 방법 등
데이터베이스 운영 관련 관리 요소 파악	• 사용자 관리 기법 및 정책 • 백업/복구 기법 및 정책 • 보완 관리 정책

 

 

3. 논리 → 물리 모델 변환

1) 엔터티 → 테이블 변환

✔️ 테이블 설명

용어	설명
테이블 (Table)	기본적으로 컬럼과 로우를 가짐
로우 (Row)	테이블의 한 로우에 대응. 튜플, 인스턴스, 어커런스라고 함
컬럼 (Column)	각 항목의 Value를 저장
기본키 (Primary Key)	하나 혹은 몇 개의 컬럼 조합으로, 어떤 경우라도 테이블 내 동일한 값이 존재하지 않음
외래키 (Foreign Key)	외부 데이터 집합과의 관계를 구현한 구조

 

 

✔️ 서브타입 변환

	1) 하나의 테이블로 통합	2) 여러 개의 테이블로 분할
설명	• 서브타입을 슈퍼타입에 통합하여 하나의 테이블로 만듦 • 통합된 테이블에는 모든 서브타입의 데이터를 포함해야 함 • 주로 서브타입에 적은 양의 속성이나 관계를 가진 경우 적용	• 각 서브타입마다 하나의 테이블로 만듦 • 분할된 테이블에는 해당 서브타입의 데이터만 포함되어야 함 • 주로 서브타입에 많은 양의 속성이나 관계를 가진 경우 적용
절차	• 슈퍼타입으로 명칭 부여 • 서브타입을 구분할 수 있도록 컬럼 추가 • 슈퍼타입의 속성을 컬럼명으로 • 서브타입의 속성을 컬럼명으로 • 슈퍼타입의 관계를 FK로 • 서브타입의 관계를 FK로	• 서브타입마다 테이블 명칭 부여 • 서브타입의 속성을 컬럼명으로 • 테이블마다 슈퍼타입의 속성을 컬럼으로 • 서브타입마다 해당되는 관계를 FK로 • 테이블마다 슈퍼타입의 관계를 FK로
예시
테이블 사례
유리한 경우	• 데이터 액세스가 좀 더 간편함 • 뷰를 활용하여 각 서브타입에만 액세스하거나 수정 가능 • 수행 속도가 좋아지는 경우 많음 • 서브타입 구분 없는 임의 집합의 가공이 용이함 • 다수의 서브타입 통합 시 조인 감소 효과가 큼 • 복잡한 처리 하나의 SQL로 통합 용이	• 각 서브타입 속성들의 선택 사항이 명확한 경우 • 처리마다 유형 구분이 불필요 • 전체 테이블 스캔 시 유리 • 단위 테이블의 크기 감소
불리한 경우	• 특정 서브타입의 NOT NULL 제한 불가 • 컬럼 수와 블록 수 증가 • 처리마다 구분(type)이 필요해지는 경우 많음 • 인덱스 크기 증가	• 서브타입 구분 없이 처리할 경우 UNION 발생 • 처리 속도 감소하는 경우 많음 • 트랜젝션 처리 시 여러 테이블을 처리하는 경우가 증가함 • 복잡한 처리의 SQL 통합이 어려움 • 부분 범위 처리가 불가능해질 수 있음 • 여러 테이블을 합친 뷰는 조회만 가능 • UID 유지 관리가 어려움

 

 

3) 아크(ARC) 형태로 적용

• 설명
  • 슈퍼타입과 서브타입을 각각 테이블로 변환한 경우
  • 슈퍼타입과 서브타입 테이블 간에는 아크(ARC) 관계가 생성
• 아래 경우를 만족할 때 적용
  • 전체 데이터 처리가 빈번하게 발생할 때
  • 서브타입 처리가 주로 독립적으로 발생될 때 (서브타입에 릴레이션이 각각 있을 때)
  • 테이블로 통합 시 컬럼 수가 너무 많아질 때
  • 서브타입의 컬럼 수가 많을 때
  • 트랜잭션이 주로 공통 부분(슈퍼타입)에서 발생될 때
  • 슈퍼타입 처리 범위가 넓고 빈번하여, 단일 테이블 클러스터링을 해야 할 때
• 변환 예시
  • 
• 서브타입 변환 Case

  서브타입을 테이블로 분리	서브타입을 통한 테이블 생성

 

2) 속성 → 컬럼 변환

항목	변환 절차	예시
일반 속성 변환	• 엔터티에 있는 속성들을 사례표의 컬럼명란에 기록 • 컬럼의 명칭은 표준화된 약어 사용 • SQL 예약어 사용은 피하되, 가능한 짧은 명칭 • 필수 입력 속성은 Nulls/Unique란에 NN, U을 표시 • 실제 테이블 설계 검증을 위해 가능하면 표본데이터를 넣음
Primary UID → 기본키(PK) 변환	• 키 형태 란에 엔터티의 Primary UID에 속하는 모든 속성에 PK 표시 • PK로 표시된 모든 컬럼은 반드시 NN, U로 표시하고 여러 개의 컬럼으로 UID 구성된 경우 각각 NN, U1을 표시 • 또 다른 Unique Key (Secondary UID)가 있다면 U2로 표시
Primary UID(관계 UID Bar) → 기본키(PK) 변환	• 테이블에 외래키(FK) 컬럼 포함 • PK의 일부분으로 표시 • Nulls/Unique란에 NN, UI 표시 • 키 형태란에 PK, FK 표시 • 여러 UID BAR가 있을 경우 PK, FK1, PK, FK2, … • 여러 컬럼으로 구성된 경우 PK, FK1을 각각 표시 • 추가된 FK 컬럼에 표본데이터 추가
Secondary(Alternate) UID → Unique Key 변환	• 논리 모델에서 정의한 Secondary/Alternate는 해당 집합과 상태 집합과의 선택적인 관계를 가질 수 있게 하는 데 중요한 역할 • 변환 절차는 Primary UID 변환 절차와 동일

 

 

✔️ 테이블 정의서 : 개발자들이 많이 참조하는 산출물 중 하나

 

 

3) 관계 변환

	1:M 관계 변환	1:1 관계 변환	1:M 순환 관계 변환
설명	• 가장 많은 형태의 관계 • M쪽 관계의 형태에 따라 관계 컬럼의 선택 사양이 결정됨	• 자주 발생하지 않음 • 관계의 Optionality에 따라 다른 방법으로 적용 • 양쪽 모두 Optional인 경우, 더 빈번히 사용되는 테이블이 외래키를 가지지 않는 것이 유리함	• 대부분의 경우, 계층 관계 표현에 사용 • 최상위 관계 속성은 Optional이어야 함
변환 절차	• 1에 있는 PK를 M의 FK로 변환 • FK의 명칭 결정 • 키 형태 란에 FK 표시 • Nulls/Unique란에 NN 표시 (Must Be 관계 시) • 필수 관계가 아닌 경우에는 NN을쳬크하지 않음 • 표본데이터 추가 • UID BAR가 있는 경우 전단계에서 실시	• Mandatory 반대쪽에 있는 테이블의 기본키를 Mandatory 쪽 테이블의 외래키로 변환하며, NN표시를 함	• 해당 테이블 내 외래키 컬럼 추가 • 외래키는 같은 테이블 내 다른 로우의 기본키 컬럼을 참조 • 외래키 컬럼 명칭은 가능한 관계 명칭을 반영 • 외래키는 절대 NN이 될 수 없음
변환 예시
주의사항	[1이 Mandatory 관계일 때] • 자식 쪽 레코드(row)가 반드시 하나 이상이어야 부모 쪽의 레코드 생성 가능 • 자식 쪽 레코드를 삭제할 경우, 반드시 하나 이상의 자식 레코드를 남겨두어야 함. 또는 자식, 부모 레코드를 동시에 삭제해야 함	• 1:1 관계에 의해 생긴 모든 외래키는 Unique Key가 필수적임 • 한쪽이 Optional이고 다른 쪽으 Mandatory라면, Mandatory 쪽의 테이블에 외래키가 생성됨 • 양쪽 다 Mandatory라면, 변환 시 어떤 테이블에 외래키를 생성할 것인지 선택

 

 

✔️ 배타적 관계 변환

	설명	예시
외래키 (FK) 분리 방법	- 각 관계를 관계 컬럼으로 생성하는 방법 - 실제 외래키 제약 조건을 생성할 수 있는 장점이 있지만, 각 키 컬럼이 Optional이어야 함
외래키 (FK) 결합 방법	- 각각의 관계를 하나의 관계 컬럼으로 생성하는 방법 - 실제 외래키 제약 조건을 생성할 수 없는 단점 - 각각의 관계를 선택적으로 구분할 수 있는 추가 컬럼 필요 - 구분 컬럼 추가 : 구분 컬럼 Type이 추가되어 배타적 관계의 각 테이블 구분

 

 

4) 관리상 필요한 컬럼 추가

• 관리 상 이유, 프로그래밍 성능을 위해 사용
• ex) 생성일시, 생성 프로세스 ID 등

5) 데이터 타입 선택

• 물리적인 DBMS 특성과 성능을 고려하여 데이터 타입 선택
• 문자 타입
  • 영문만 사용 / 4K or 8K / 고정 길이
  • (N)TEXT, (N)CHAR, (N)VARCHAR
• 숫자 타입
  • 불린, 정수, 소수, 화폐
• 날짜 타입
  • datetime, smalldatetime

6) 데이터 표준 적용

항목	내용
개념	• 컬럼명을 생성하고 변환하는 과정에서 미리 생성된 데이터 표준을 따름 • 표준 용어, 도메인, 명명규칙 등
데이터 표준 적용 대상	• 데이터베이스 • 스토리지 그룹 (물리적인 DISK를 묶어서 하나의 그룹으로 정의) • 테이블스페이스 (테이블이 생성되는 물리적인 영역) • 테이블 • 항목 (컬럼) • 인텍스 • 뷰
데이터 표준 적용 방법	[명명 규칙에 의한 표준화 적용] • 논리 → 물리 전환 시, 엔티티 한글명과 동일한 용어를 영문명으로 전환 • 영문명 : 영문 약어 사용, 표준 용어 사전에 등록된 표준 영문 약어 참조 • 테이블 명명 순서 : 업무 영역+주제어 수식어+주제어+분류어 수식어+분류어+접미사 [표준 용어집에 의한 표준화 적용] • 사전에 모든 객체명과 데이터 타입, 길이 등을 정해놓고 사용

 

 

4. 반정규화

• 정규화된 데이터 모델은 시스템의 성능 향상, 개발 과정의 편의성, 운영의 단순화를 위해 정규화의 원칙에 위배되는 행위를 의도적으로 수행
• 일관성, 무결성 or 성능, 단순화 중 우선순위를 정해야 함

1) 테이블 분할 (파티셔닝)

항목	내용
개념	• 수직/수평 분할 하는 것 • DB 디자인의 파티셔닝과 다른 의미
수평 분할	• 레코드를 기준으로 분할 • 테이블에 데이터가 너무 많고 특정 덩어리의 범위만을 액세스하는 경우 사용 • 분할 후 서로 다른 디스크에 위치시켜 물리적인 디스크 효용성을 극대화 • DBMS 차원에서 제공
수직 분할	[수직 분할이 필요한 경우] • 컬럼의 갯수가 많아질 때 • 조회 위주의 컬럼과 갱신 위주 컬럼이 나뉠 때 • 특별히 자주 조회되는 컬럼이 있을 때 • 특정 컬럼의 크기가 클 때 • 특정 컬럼에 보안을 적용해야 할 때

 

 

2) 중복 테이블 생성

집계 함수를 이용하여 자주 조회할 때, 특정 통계 테이블을 두거나 중복 테이블을 추가

 

✔️ 중복 테이블 생성 판단 근거

• 정규화에 충실하면 종속성, 활용성은 향상되지만 수행 속도 증가가 발생하는 경우
• 많은 범위를 자주 처리할 때
• 특정 범위의 데이터만 자주 처리할 때
• 처리 범위를 줄이지 않고는 수행 속도를 개선하지 못할 때
• 요약 자료만 주로 요구되는 경우, 추가된 테이블의 처리를 위한 오버헤드를 고려하여 결정
• 인덱스의 조정이나 부분 범위 처리로 유도, 클러스터링을 이용하여 해결할 수 있는지 검토 후 사용

 

✔️ 중복 테이블 유형

	유형/상황	유의사항
집계 테이블 추가	• 단일 테이블의 GROUP BY • 여러 테이블의 조인 GROUP BY	• 로우 수와 활용도를 분석하고 시뮬레이션을 통해 그 효용성에 면밀한 검토가 선행되어야 함 • 집계 테이블에 단일 테이블 클러스터ㅓ링을 한다면, 집계 레벨을 낮춰 활용도를 높일 수 있는지 검토해야 함 • 클러스터링, 결합 인덱스, 고단위 SQL로 해결될 수 있는지 검토해야 함 • 집계 테이블을 다시 집계, 조인하면 추출할 수 있을지 검토하여 지나친 집계 테이블을 만들지 않는 것이 좋음 • 추가된 집계 테이블을 기존 응용 프로그램이 이용할 수 있는지 찾아 보정하는 노력 필요 • DB 트리거의 오버헤드에 주의하고 일관성 보장에 유의해야 함 • 집계 테이블과 원본 테이블 간의 일관성 유지가 중요함
진행 테이블 추가	• 여러 개의 조인이 빈번히 발생하여 처리 범위도 넓은 경우 • M:N 관게가 포함된 처리 과정을 추적, 관리하는 경우 • 검색 조건이 여러 테이블에 걸쳐 다양하게 사용되고 복잡한 처리량이 많을 때	• 데이터 양이 적절하고 활용도가 높아지도록 기본키를 선정 • 필요에 따라 추출 컬럼을 추가하여 집계 테이블 역할도 하는 다목적 테이블을 구상 • 진행 테이블을 만들어야 하는지 확인

 

 

3) 중복 컬럼 생성

• 정규화를 어기면서 중복 수행
• 중복 컬럼 생성 상황
  • 빈번한 조인을 일으키는 컬럼, 속도가 중요한 컬럼, 액세스 조건으로 자주 사용되는 컬럼에 고려
  • 자주 사용되는 액세스 조건이 다른 테이블에 분산되어 있어 액세스 범위를 줄이기 힘든 경우, 하나의 테이블로 모아서 조건의 변별성 극대화
  • 복사된 컬럼의 도메인은 원본 컬럼과 동일해야 하며, 접근 경로 단축을 위해 부모 테이블 컬럼을 자식에 중복시킬 수 있음
  • 상위 레벨의 테이블에 집계된 컬럼 추가 가능 (1:M 관계)
  • 하위 레벨의 테이블로 중복 컬럼 복사 가능 (1:M 관계)
  • 연산된 결과를 주로 사용할 경우, 미리 연산하여 중복 컬럼 생성 가능
  • 여러 컬럼의 조합 또는 복잡한 연산의 결과를 통해 판단할 수 밖에 없을 때, 연산의 결과를 중복 컬럼으로 생성 가능
  • 로우로 관리하던 데이터를 컬럼으로 관리하는 경우, 기본키가 길거나 여러 개의 컬럼일 경우, 인위적인 기본키 추가 가능
• 중복 컬럼 생성 시 유의사항
  • 다중 테이블 클러스터링 (단위 클러스터에 2개 이상의 테이블을 함께 저장)으로 해결 가능한지 검토
  • SQL GROUP 함수 이용항 처리 가능한지 검토
  • 저장 공간의 지나친 낭비를 고려해 대비책 마련
  • 반복 컬럼은 특별한 경우를 제외하고는 절대 사용하면 안됨. 만약 특별한 경우가 있다면 sum(decode..) 용법과 같은 기법을 사용해 피해야 함
  • 경우에 따라 상대 테이블의 ROWID를 복사하는 경우가 효과적일 때 있음
  • 데이터의 일관성 보장에 유의하고 컬럼 중복이 심하면 처리 시 오버헤드 발생
  • 사용자나 프로그램은 반드시 원본 컬럼만 수정하는 것이 바람직함
  • 수행 속도 때문에 많은 중복 컬럼을 생성하는 것이 현실이나 가능하면 적게 가져가야 함
  • 클러스터링, 결합 인덱스, 적절한 SQL로 해결 가능한 것이 많으므로 우선 고려한 후 적용
  • 지나친 중복은 데이터 일관성 오류 발생의 개연성 증가 및 데이터 오버헤드 증가라는 반대급부가 있다는 것을 염두하고 수행
  • JOIN, SUB-QUERY 액세스 경로의 최적화 방안을 강구해야 함

 

---

📌 참고 포스팅 📌

[데이터 모델링] 5. 물리적 데이터 모델링