[핵심 데이터 모델링] - 물리 모델링

soon·2023년 3월 18일

데이터 모델링

물리 모델링이란

📌 논리 모델링 결과를 실제 업무에서 사용할 수 있게 바꾸는 것
성능이 제일 중요하다. 성능이 안좋으면 역정규화도 진행함

물리 모델링은 논리 데이터 모델을 DB종류에 맞게 데이터를 저장할 수 있는 테이블 구조로 변환하는 과정이다.
엔티티 및 서브타입을 테이블로 변환하고, 속성을 컬럼으로 변환해 데이터타입 및 길이를 지정

테이블 설계

📌 관계형 모델에서는 논리 데이터 모델을 테이블로 변환한다.
엔티티 -> 테이블
속성 -> 컬럼

테이블명은 엔티티명을 참고해 작성한다.
- 주제영역 약어와 엔티티명의 표준 영문 약어를 조합해서 만들거나
- 대, 중, 소 주제영역 약어와 일련번호를 조합해 만든다.
테이블은 로우Row와 컬럼Column으로 구성되며. 가장 기본적인 오브젝트이다.
DB내 모든 데이터는 테이블에 저장되며, 로우행 or 컬럼열 방식으로 저장된다.

테이블에선 슈퍼타입/서브타입 구조를 직접 표현할 수 없다.
통합해서 설계하거나, 각각 엔티티를 각각의 테이블로 설계한다.

슈퍼타입 기준 테이블 설계

서브타입 엔티티를 -> 슈퍼타입 엔티티로 통합해 테이블 설계
순서..는 그냥 그림으로 설명한다.
주의할점
- 슈퍼타입 엔티티에서 공통으로 관리하는 속성이 많고, 서브타입이 몇개 없거나 속성이 많지 않을 때 사용
- 고객유형코드에 해당하는 경우만 주민등록번호 또는 법인등록번호 값이 있으므로 Not Null을 지정할 수 없다
  - 경우에 따라 인덱스도 각각 추가해야 한다.

서브타입 기준 테이블 설계

슈퍼타입 속성 -> 서브타입으로 내려 서브타입 단위로 테이블을 생성
슈퍼타입 엔티티에 연결된 관계 대신 서브타입 엔티티에 관계를 추가
슈퍼타입 엔티티를 삭제하고 서브타입 엔티티에 테이블명을 준다.
주의할점
- 서브타입 엔티티 속성들이 명확히 구분되어야 한다.
- 전체 집합을 데이터 처리 시 두 집합을 UNION ALL 해야하고, 다른 테이블과 관계가 있으면 개별 서브타입에 관계를 추가해야한다.
- 이처럼 여러 테이블을 처리해야 한다면 프로그램이 복잡해진다.

슈퍼타입/서브타입 테이블 설계

슈퍼타입과 서브타입 엔티티를 개별 테이블로 설계 -> 테이블 명을 부여하는 방식
슈퍼타입, 서브타입 테이블은 1 : 1 관계로 정의한다.
전체 데이터에 대해 공통적인 속성을 처리할 경우 -> 슈퍼타입 테이블을 읽어서 처리하고,
서브타입 테이블에서 관리하는 데이터를 화면에서 상세조회하는 경우 -> 서브타입 테이블을 읽어 처리한다.
ex
고객이 보험에 가입한 보험 목록을 조회 -> 슈퍼타입 테이블 조회,
특정 보험계약을 선택할 경우 -> 서브타입 테이블 상세정보 조회

관계 설계

관계는 기본적인 관계, 상호배타적 관계, 순환 관계로 나눌 수 있다.

기본적인 관계

부모 테이블의 PK를 -> 자식테이블 PK나 FK로 상속받는다.

상호배타적 관계

DBMS는 상호배타적 관계를 지원 X
- 논리적인 상호배타적 관계를 DBMS가 수용할 수 있도록 테이블을 설계해야함
- 설계방법은, 1. 한 컬럼으로 통합하는 방법과 2. ~~개별 컬럼으로 분리하는 방법~~이 있다.
  - 분리하는 방법은 논리적 구조와 물리적 구조가 다르고, 프로그램이 복잡해져 되도록 통합해 설계한다.
자식 테이블 컬럼 통합/분리 설계
통합하는 방법은 관계에 참여한 모든 관계를 동일한 컬럼고객번호으로 변환하고, 별도 구분 컬럼고객유형코드을 추가해 구분한다.
분리하는 방법은 개별 컬럼개인번호/법인번호/단체번호으로 변환하고, 구분컬럼고객유형코드을 추가해 구분한다.
- 해당 관계에 대해 FK설정할 수 있으나, Not Null을 지정할 수 없고, 개별 컬럼마다 인덱스를 생성해야하는 단점이 있다.

관계 옵셔널리티 확정

논리 모델링 단계에서 정확하지 않거나, 모호하게 설계한 옵셔널리티를 검토하여 확정한다.
부모, 자식 엔티티가 Mandatory 관계인 경우
: 테이블에 데이터가 입력되는 시점을 기준으로 결정
- 부모 테이블에 데이터가 입력되면 자식 테이블도 동시에 입력된다.
자식 엔티티와 Optional 관계인 경우
: 부모 테이블 PK데이터가 자식 테이블에 없어도된다.
- 자식 테이블에 데이터가 입력될 때 부모 테이블에 해당 데이터가 이미 존재햐아 한다.
양쪽 엔티티가 Optional인 경우
: 부모 테이블 데이터가 없는 상태 -> 자식 테이블에 데이터가 발생하는 유형
- 나중에 부모 테이블에 데이터가 생성되면서 자식 테이블 참조 FK를 변경한다.

컬럼 설계

논리 데이터모델의 속성에 대해 표준용어, 표준단어, 표준도메인을 적용하는 작업이 끝

속성-컬럼 변환

일반적으로 논리 모델에서 정의한 속성은 거의 그대로 사용한다.
💡 시스템에서 데이터에 대한 변화를 추적하고, 트랜잭션을 관리하기 위해 시스템 컬럼을 추가할 수 있다.
- 최초등록일시, 최초등록자식별번호, 최종수정일시, 최종수정자식별번호 컬럼이 해당한다.
- 시스템 컬럼은 모든 테이블에 공통적으로 추가하며 테이블 가장 마지막에 위치하도록 한다.

데이터 타입 및 길이 지정

컬럼에 대한 데이터 타입 지정 관련해 몇 가지 살펴볼 이슈가 있다.
이슈 1
: CHAR고정길이, VARCHAR2가변길이중 어느것을 적용할 것인가?
고정 길이의 경우 CHAR로 하는게 좋을꺼 같지만, 빈칸을 공백으로 채우고, 값을 비교하는데 어려움이 있다.
혼용해서 사용하면 내부적인 형 변환등의 문제도 발생할 수 있다.
성능은 그리 고려할 필요는 없다. CHAR이 크게 나은 점도 없다.
결론은 그냥 VARCHAR2만 사용하는게 좋을 듯 하다.
이슈 2
: 일자YYYYMMDD컬럼을 VARCHAR2(8)로 할지 DATE형으로 할지에 대한 고민
데이터 타입을 정하는데 데이터 품질을 우선으로 할지 개발 생산성과 성능을 고려할 것인지도 고민된다.
VARCHAR2(8)은 개발자들이 선호하지만 날짜가 아닌 데이터가 유입될 수 있고
날짜형 데이터 타입은 날짜를 문자형식으로 변환하는 일이 많아 생산성이 ↆ 되지만 오류 데이터가 들어올 가망은 거의 없다.(시분초까지 하면 오류가 입력될 여지는 있음)
품질이 우선이면 → DATE형, 생산성이 우선이면 → VARCHAR2(8)
＊CARCHAR2(8)로 한다면 제약조건을 추가하거나, 인덱스를 만들어 오류 데이터가 입력되는걸 방지한다.
```
alter table emp add constraint emp_ck_ent_dt
check (ent_dt = to_char(to_date(ent_dt, 'yyyyMMdd'), 'yyyyMMdd' ));
또는
create index emp_ix01 on emp( to_date(ent_dt), 'yyyyMMdd') );

insert into emp values('20190230');
-- ORA-01839 : date not valid for month specified
```

기본키(PK : Primary Key) 지정

📌 기본키는 Not Null 이어야 하고, Unique 해야한다.

논리 데이터 모델의 주 식별자를 기본키PK로 생성한다.
자체 컬럼으로 구성하는 경우 핵심 엔티티, 중요 엔티티에 해당하는 테이블이고, 데이터에 대한 일정한 채번규칙을 가지고 있어야 한다.
부모 테이블에서 상속받는 경우는 상속받은 컬럼에 숫자형 데이터타입을 가지는 일련번호 컬럼을 추가하는게 일반적이다.
다른 테이블들의 키로만 구성된 테이블은 교차 or 매핑 성격의 데이터를 가지는 테이블이다.
참조하는 컬럼과 동일한 데이터 타입과 길이로 정의한다.
- 다만, 기본키는 조회조건에서 가장 많이 쓰는 컬럼 순으로 구성하고, 값의 종류가 많은 컬럼이 먼저 오도록 구성한다.

데이터 무결성 설계

일관성 (Consistency) : 내 계좌에 100원이 나가면 상대방 계좌에 100원이 추가됨
무결성 (Integrity) : 제약조건으로 이상한 데이터가 들어오면 안된다.

데이터 무결성은 데이터를 저장하고 관리할 때 데이터의 정확성과 일관성을 유지하기 위해 정의한 규칙이다.
데이터 무결성은 아래 3가지가 있다.

실체 무결성 (Entity Integrity)
영역 무결성 (Domain Integrity)
참조 무결성 (Referential Integrity)

실체 무결성(Entity Integrity)

실체 무결성은 기본키PK와 관련된 제약조건으로 식별자 값은 Not Null, Unique 이여야 한다.

create table emp (
		  emp_no   varchar2(6)   not null,
          emp_nm   varchar2(50)  not null, 
          brth_dt  varchar2(8) 
);
alter table emp add constraint emp_pk primary key (emp_no) ; 
alter table emp add constraint emp_uk unique (emp_nm, brth_dt) ;

영역 무결성(Domain Integrity)

데이터의 속성 값들은 정해진 데이터 범위를 벗어나지 않아야 하고, 데이터 타입, 길이, 유효값을 유지해야 한다.
Check, Default, Not Null 제약조건에 해당

create table emp (
		  emp_no   varchar2(6)   not null,
          emp_nm   varchar2(50)  not null, 
          brth_dt  varchar2(8) ,
          reg_dtm  date          default sysdate not null
);
alter table emp add constraint emp_ck_brth_dt
check (brth_dt = to_char(to_date(brth_dt, 'yyyyMMdd'), 'yyyyMMdd')); 

insert into emp values('000001', '홍길동', '20190231', sysdate);
-- ORA-01847 : day of month must be between 1 and last day of month;

등록일시reg_dtm 데이터를 입력하지 않았을 경우 기본값을 sysdate, default로 지정된다.
생년월일brth_dt은 문자형 데이터 타입이지만 check 제약조건을 통해 yyyyMMdd 날짜 형식 데이터가 아닌 경우 오류를 발생시킨다.
2월 31일은 없는 날짜이므로 입력될 수 없다.

참조 무결성(Referential Integrity)

참조 무결성은 데이터 모델에서 정의된 실체 간의 관계조건을 유지하는 것
참조하는 테이블은 참조할 수 없는 외래 키FK를 가져갈 수 없고, 참조되는 테이블은 외래키가 존재하는 한 데이터를 삭제하거나 변경할 수 없다. FK제약조건에 해당

alter table emp add constraint emp_fk FOREIGN KEY (dept_cd)
	references dept(dept_cd) on delete set null ;

insert into emp(emp_no, dept_cd) values('000002', '0002');
-- ORA-02291 : integrity constraint violated - parent key not found

부서dept 테이블에 존재하지 않는 부서코드0002를 사원emp테이블에 입력할 경우 FK 제약조건에 걸려 오류 발생
FK 제약은 확실하고 간편한 방법이지만 성능적인 측, 관리적은 측면도 고려해야 한다.
보통은 FK를 설정하지 않고 개발한다음 나중에 개발이 마무리되면 FK를 설정한다.

성능을 고려한 데이터 구조

물리 모델링 단계에서 대량의 자주 데이터를 처리하거나, 특정 범위를 자주 처리하는 경우는 확인하여 테이블을 설계해야 한다.
필요할 경우 집계 테이블을 추가하는 정도?

집계/요약 테이블 추가

대량의 데이터를 실시간으로 읽어, 집계하고 처리할 경우 집계 테이블을 추가할 수 있음
성능적인 문제를 해결하면서 다양한 집계처리를 포함하고, 공통된 조건을 분석해 테이블을 설계한다.
배치 처리/작업 : 월간 데이터를 실시간이 아닌 월말이나 초에 정리하고 전월처리로 계산한다.

컬럼 추가

정규화를 잘 했으면 컬럼 중복으로 인한 오류 데이터 유입을 최소화 할 수 있다.
성능 문제 때문에 정규화를 일부 포기하고 반정규화 하거나 중복 컬럼을 추가해야 하는 경우도 있다.
적당한 타협선이 필요하다.
부모 테이블에서 인조 식별자를 기본키로 설계한 경우 기본키를 상속받으면서 본질 식별자에 해당하는 컬럼을 추가하거나,
자주 조회 조건으로 사용하는 컬럼을 자식 테이블에 추가하는 경우가 대표적이다.
부모 테이블에 컬럼을 추가하는 경우도 있다.
부모 테이블에서 자식 테이블의 집계된 값이나 최종 데이터 값을 가지고 있다면, 매번 자식 테이블에서 최소/최대,
건수/합계, 등 최종 데이터 값을 추출할 필요가 없으므로 성능이 향상된다.

테이블 분할

테이블은 컬럼가로과 로우세로로 구성되어 있고, 데이터를 처리할 때 가로 세로 면적이 처리범위가 된다. 면적을 줄이기 위해 가로, 세로를 줄이는 방법이 있다.
테이블 수직분할
: 가로를 줄이는 방법으로 테이블의 컬럼을 두 개 이상의 테이블로 나눠 관리하는 방법
1 :1 구조로 나누는 이유? : 성능과 보안 때문
ex 게시판 테이블
게시물내용 컬럼은 아주 많은 문자가 입력되므로 게시판 데이터의 대부분을 차지한다.
보통은 게시물 목록을 보여주고, 게시물 선택 시 → 게시물 내용 확인 순이다.
게시물 목록 조회시 데이터는 가져오지 않지만, 컬럼이 포함되어 있어 불필요하게 큰 데이터를 처리할 수 밖에 없다.
이와 같이 사용하는 컬럼들끼리 묶어 다른 테이블로 분리하면 성능 향상에 도움이 된다.

물리 설계

물리 모델링 이후 DBMS 특성을 고려해 성능, 관리, 보안, 개발 생산성의 목적에 맞게 물리적인 설계를 한다.
프로젝트에서 주로 사용하는 DB오브젝트는 테이블, 인덱스, 뷰읽기전용, 시노님object 별명, 함수반환값O, 프로시저반환값X, 시퀀스 등이 있다.
테이블, 인덱스, 시노님은 설계 단계에서 DA/DBA가 설계하고, 뷰, 함수, 프로시저, 시퀀스는 개발 단계에서 개발자가 직접 생성하거나 DBA에게 요청한다.
데이터를 저장하고 관리하는 오브젝트는 테이블과 인덱스 등이 있다.

오브젝트 명명 규칙

테이블, 뷰, 시퀀스, 함수, 프로시저 등은 명명 규칙에 따라 영문 약어 및 일련번호 등을 조합하여 부여한다.
- ex TCM_CUST (테이블), VCM_CUST (뷰), SCM_CUST_NO_01 ( 시퀀스), FCM_CUST_NM (함수), PCM_CUST_DEL (프로시저)

soon

이전 포스트

[핵심 데이터 모델링] - 논리 모델링

다음 포스트

[핵심 데이터 모델링] - 물리 모델링

물리 모델링이란

테이블 설계

슈퍼타입 기준 테이블 설계

서브타입 기준 테이블 설계

슈퍼타입/서브타입 테이블 설계

관계 설계

기본적인 관계

상호배타적 관계

관계 옵셔널리티 확정

컬럼 설계

속성-컬럼 변환

데이터 타입 및 길이 지정

기본키(PK : Primary Key) 지정

데이터 무결성 설계

실체 무결성(Entity Integrity)

영역 무결성(Domain Integrity)

참조 무결성(Referential Integrity)

성능을 고려한 데이터 구조

집계/요약 테이블 추가

컬럼 추가

테이블 분할

물리 설계

오브젝트 명명 규칙

[핵심 데이터 모델링] - 논리 모델링

[스프링의 정석] - Spring MVC - 클라이언트 서버, HTTP 요청과 응답

0개의 댓글

관련 채용 정보