따봉 구걸남's 블로그

데이터베이스 모델링에서 관계는 결국 컬럼으로 표현이 되는데 이 컬럼이 참조하는 테이블이 자기자신일 때가 있다. 

이를 자기 참조 모델 또는 순환 관계 모델이라고도 부르는데, 어떻게 사용하는지 알아보자.

-- 테이블 생성
CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMBER PRIMARY KEY,
    FIRST_NAME VARCHAR2(50),
    LAST_NAME VARCHAR2(50),
    EMAIL VARCHAR2(100),
    PHONE_NUMBER VARCHAR2(20),
    HIRE_DATE DATE,
    JOB_ID VARCHAR2(10),
    SALARY NUMBER(8,2),
    MANAGER_ID NUMBER,
    DEPARTMENT_ID NUMBER
);

-- 샘플 데이터 삽입
INSERT INTO EMPLOYEES VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567', TO_DATE('17-JUN-2003', 'DD-MON-YYYY'), 'AD_PRES', 24000, NULL, 90);
INSERT INTO EMPLOYEES VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', TO_DATE('21-SEP-2005', 'DD-MON-YYYY'), 'AD_VP', 17000, 100, 90);
INSERT INTO EMPLOYEES VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', TO_DATE('13-JAN-2001', 'DD-MON-YYYY'), 'AD_VP', 17000, 100, 90);
INSERT INTO EMPLOYEES VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', TO_DATE('03-JAN-2006', 'DD-MON-YYYY'), 'IT_PROG', 9000, 102, 60);
INSERT INTO EMPLOYEES VALUES (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', TO_DATE('21-MAY-2007', 'DD-MON-YYYY'), 'IT_PROG', 6000, 103, 60);
INSERT INTO EMPLOYEES VALUES (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', TO_DATE('25-JUN-2005', 'DD-MON-YYYY'), 'IT_PROG', 4800, 103, 60);
INSERT INTO EMPLOYEES VALUES (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', TO_DATE('05-FEB-2006', 'DD-MON-YYYY'), 'IT_PROG', 4800, 103, 60);

SELECT LEVEL, 
       LPAD(' ', 4*(LEVEL-1)) || e.FIRST_NAME || ' ' || e.LAST_NAME AS "Employee",
       e.EMPLOYEE_ID,
       e.MANAGER_ID,
       e.JOB_ID,
       e.SALARY,
       e.DEPARTMENT_ID
FROM EMPLOYEES e
START WITH e.MANAGER_ID = 0
CONNECT BY PRIOR e.EMPLOYEE_ID = e.MANAGER_ID
WHERE e.DEPARTMENT_ID = 60
ORDER SIBLINGS BY e.SALARY DESC;

- WHERE 절은 계층 구조가 만들어진 후에 적용. => 상위 관리자가 다른 부서에 속해 있어도(ex. 90) 결과에 포함
- ORDER SIBLINGS BY : 이 구문은 같은 레벨의 형제 노드들을 정렬.
  여기서는 각 레벨에서 급여(SALARY)를 기준으로 내림차순 정렬합니다. 예를 들어, LEVEL 4의 Bruce, David, Valli는 모두 같은 상사(Alexander)를 가지고 있으며, 이들은 급여 순으로 정렬됨.
- 최상위노드의 manager_id 는 보통 null 이 된다. 그러나 검색 편의를 위해 특정 값을 넣거나 편의상 컬럼을 추가하기도한다. (ex. IS_ROOT )

- prior 에 명시된 컬럼이 상수로 바뀌고 이를 반대편 컬럼(manager_id) 에 넣고 찾아가게 되므로 해당 컬럼에 인덱스를 생성하는 것이 좋다. 역전개의 가능성이 있기에 반대쪽 컬럼에도 인덱스를 생성하는 것이 좋다. 

1. 식별자 종류

주식별자 : PK
업무식별자 : 업무적으로 인스턴스를 구분하는 속성 (논리적 구분)
후보식별자 : 주식별자가 될수 있는 후보
대체식별자 : 주식별자로 선택되지 않은 후보식별자
인조식별자 : 물리적으로 인스턴스를 구분. 인스턴스 추가 기준 알수없다
슈퍼식별자 : 식별자 + 추가 속성(주로 성능적 이유, 안만드는 것이 좋다) 

2. 식별자 선정 과정 # 98p 

  1) 업무식별자 도출
  2) 후보식별자 도출
  3) 업무식별자가 주식별자로  "적합한지 검토" 
    -> 적합) 업무식별자를 주 식별자로 선정
    -> 부적합) 후보식별자 중 주 식별자 " 적합한지  검토" 
            => 적합) 후보식별자를 주 식별자로 선정
            => 부적합) 인조식별자를 주 식별자로 선정

우선순위 : 업무식별자 > 후보식별자 > 인조식별자

3. 엔터티 유형별 업무식별자 도출

 실체엔터티 : 보통 일시속성이 포함x , 포함 되었다면 순수 실체x     ex) 사원E 의 주민번호 + 입사일자 
 행위엔터티 : 누가(who),무엇(what),언제(when) 등 5w1h 로 따져서 업무식별자 도출

4. 식별자 선택 시 고려사항:
 - 업무적 활용도가 높을 것
 - 길이가 짧은 것
 - 대표성을 가질 것
 - 불변성 (한번 부여된 식별자는 변하지 않아야 함)
 - 유일성 (각 인스턴스를 유일하게 식별할 수 있어야 함)

그간 여러 사이트를 다니고 나서 느낀 모델링에 대한 개인적인 소회, 생각들 한번 정리해보자.

- 튜닝에 비해 바로 효과 나타나지 않아 중요해 보이지 않는다. 

한때 좋은 모델과 데이터의 품질에 대해서 신경을 쓰려던 트렌드가 잠깐 생겼었는데 조금은 뜸해진 느낌

튜닝은 바로 효과가 나타나고 비용과 즉시 연결이 된다. 클라우드의 시대가 오면서 더 확고해졌다. 

온프레미스에선 성능 개선으로 자원사용륭이 낮아졌다고 해서 서버를 낮은 스펙으로 바꾸지 않는다. 

이미 구매를 한 서버니 그럴일이 없다. 어느 정도 이상의 튜닝은 굳이 할 필요가 없다. 

하지만 클라우드에서는 서버의 스펙다운이 자유롭다. 튜너를 투입해서 얻은 성능개선이 운영비 절감을 가시적으로 할수있게 되었다. 

- 통합 관점의 표준은 개별적이고 독립적으로 개발하는 MSA 트렌드와 상충

물론 완전히 하나를 버리고 하나를 택하는 극단적인 상황까지 가는 것은 아니지만 통합 관점의 표준과 데이터의 중복도 허용하지 않는 기존의 방식을 계속 고수하려면 갈등이 발생한다. 

- 잘못된 모델은 시간이 한참 지나고 나서야 비싼 청구서를 부과한다. 

물리모델은 dbms 의 특성을 반영할수 밖에 없는데 최근에 mysql 로 환경이 많이 바뀌면서 이 특성을 잘 고려해야한다. 

오라클은 인덱스를 통해 데이터를 찾아갈때 인덱스의 리프노드에서 데이터의 rowid 를 찍어서 바로 원하는 데이터를 찾아갈수 있지만 mysql 은 조금 방식이 다르다.

mysql 의 innodb 테이블에서는 pk 가 클러스터링 되어 저장이 되는데, 세컨더리 인덱스에서 rowid 를 찍는것이 아니라 pk 를 찍어준다. 그래서 pk 가 여러 칼럼으로 구성되면 인덱스의 크기가 커지고 성능상 불리함이 있다. 

그래서 mysql 은 단순하게 일련번호로 pk 를 만드는 오라클의 모델링에서는 참을수 없는 방식으로 가이드를 한다.

다만 이때도 업무식별자를 도출해 유니크 인덱스를 만들 필요가 있다.

일련번호로 식별자를 만들때의 단점은 어떤 규칙으로 데이터가 만들어지는지 파악하기 힘들다는 것에 있다. 

심한경우 운영환경에서 여러사람을 거친 경우 나중에는 특정 테이블을 잘못 사용하고 있거나 무슨 테이블인지 모르겠다고 해서 비슷한 새로운 테이블을 또 만드는 경우가 생기게 된다. 

- 틀린 모델로 운영이 가능하다 .


안타까운 사실은 모델이 틀려도 어떻게든 운영을 한다는 것이다. 물론 내눈에 보이지 않는 누군가의 노력이 있었겠지만. 

좋은 모델을 만들고 유지하려면 비용을 지불해야 하지만 당장의 우선순위에서 밀리다 보니 고객은 이쪽에 더 돈을 지불할 의사를 보이지 않는듯한 느낌이다. 

1. 관계의 의미

우리가 많이 사용하는 RDBMS 의 'R' 은 Relational 이라는 관계형을 의미한다. 
데이터를 테이블형태로 구조화 하여 사용하는 것이 특징이고, 테이블과 테이블에는 관계라는 것을 설정할수있다
관계라는 것은 내가 어디에서 왔는가를 의미한다. 조인과는 다른 개념이다. 
나와 1촌인 부모-자식 에만 관심이 있고, 할아버지-손자 의 관계는 관심이 없다. 
부모없는 자식, 아버지와 할아버지가 같은 모델은 잘못된 모델이다.   

2. 관계의 구현

데이터모델링에서 관계라는 것은 논리적인 표현이고, 결국 관계란 자식의 컬럼으로 표현되며, 
물리적으로 이를 구현한 형태가 외래키 (FK) 이다. 

요즘은 참조 제약조건을 DBMS 단의 FK 로 구현하지 않는 추세인데, 이유는 아래정도가 있다.

- 개발 편의성
 이관작업시 데이터 선후 관계를 고려해야함.
 ogg는 데이터간의 관계를 따지지 않기때문에 일시적 오류 가능성
- lock 의 전이, 데드락 발생 가능성
 
정말 필수적인 테이블에만 일부 FK를 생성해서 운영하곤 한다. 
dbms 단에서 제약조건을 처리하지 않는것이지, 아예 참조제약조건을 무시하는 것이 아니다. 
application 레벨에서 참조 제약조건을 수행하는 로직을 추가해야 한다.  

논리모델에서는 관계를 표현해야 하지만 물리모델에서 FK 를 생성하는 가는 전략적 문제이다. 
다만 FK 를 생성하지 않으니 논리모델에서 관계를 생략하는 것은 덜 완성된 모델이다. 

* 관계가 생략될수 있는 경우
 - 기준엔터티
  환율엔터티의 경우, 
 - 가공엔터티
  
 - 외부엔터티 
  외부의 시스템의 참조하는 경우
   

3. 속성과 관계 누락
속성은 있지만 관계가 안그려진것 -> 속성을 보고 관계가 누락된것을 발견할수 있으니 불행 중 다행
속성표현X, 관계는 그려진 경우 -> 존재하지 않음
관계없는데 관계가 그려진 경우 


4. 식별/비식별 관계 

관계는 부모의 식별자를 물려받는 것. 
이때 부모의 식별자가 자식의 식별자에 포함이 되면 식별관계가 되고, 부모-자식 간 강한 종속성이 나타남. (부모없는 자식x)
반면, 부모의 식별자가 자식의 일반속성으로 가게되면 부모-자식간의 약한 종속성이 나타나고 부모와 독립적으로 존재할수 있다.

* 유의 사항 
- 부모의 일부만 물려받지 않는다. 
- 부모의 속성을 물려받지 않는다. 


6. 관계의 단절 필요성

조상이 많은 경우 계속해서 식별관계로 그릴경우 식별자가 비대해진다.
이런 모델에서 조인을 걸 경우 SQL 이 복잡해지는 단점이 있다.
이때 인조식별자를 만들어 비식별 관계로 만드는 것을 고려한다.
자식이 많을 경우에도 고려할수 있다. 

1. 이력모델 개념

데이터 모델링에서 '이력', '이력데이터' 는 많이 사용되는 용어 인데 그 개념이 모호하거나 
이력이 아닌것을 이력이라고 부르는 등 이치에 맞지 않게 사용되는 경우가 종종 보인다. 

어느 글에서 이력은 '시간에 따라 발생하는 데이터 형식' 이라고 정의한 것을 보았는데, 
이것은 일반적인 트랜잭션과도 차이점이 없다. 

주문과 같은 일반적인 트랜잭션을 '발생내역' 이라고 한다면 변경된 속성값을 일자에 따라 관리하는 "이력" 을 '변경이력' 이라고 구별 할 수 있다. 
변경이력을 다시 정의하면 현재 유효하지 않은 과거 데이터 라고 할수 있다. 
이력모델 이라고 부를수 있는것은 '변경이력' 이고, 원천모델이 나오고 나서야 이력모델이 도출되는 것이다. 
즉, 원천모델이 없는데 이력엔터티라고 부르고 있다면 엔터티의 정의를 다시 한번 살펴볼 필요가 있다. 


2. 발생내역 vs 변경이력 

- 발생내역(트랜잭션)
  insert 의 형태 (ex. 주문, 접속ip로그..)
  pk변경, 데이터변경 

- 변경내역 (찐 이력)
  update & insert 형태 (ex. 비밀번호변경 이력)
  pk동일, 데이터변경 
 
ex) 개인경력 엔터티가 있다면..
사번
일자
소속
경력상세 
=> 시간에 따른 경력쌓인 것(발생내역)

    과거의 경력데이터가 변경된 경우(변경내역 발생) -> 변경전 데이터를 관리할거면 이력엔터티 도출, 아니면 업데이트하고 완료


3. 이력엔터티와 원천엔터티 분리 or 통합

- 분리가 원칙 
엔터티 순수성이 유지 되어야 확장이 가능 -> 원천모델이 자식엔터티가 있다면 분리가 원칙 
잘못된 데이터 하나가 전체 엔터티의 데이터 신뢰도를 떨어트림 

이력+원천 통합 모델의 자식은 일자속성은 상속되지 않는 이상한 케이스 발생 => '$'

- 통합하는 경우 
업무적으로 이력/원천데이터가 빈번히 같이 사용된다면 한곳에 사용할수도 있다 
=> 원천에 일시 속성이 추가되어 사용하는것. 성능적 관점. 반정규화 일종 

이력+원천을 통합해서 쓴다면 해당 엔터티의 이름은 '~이력' 이라고 쓰지 않는다. 
오직 이력데이터만 있는 엔터티에게만 ~이력 이라고 명명한다. 


4. 이력관리 모델 형태/유형

- 이력관리 유형(레벨)
 1) 엔터티 레벨 (스냅샷)
   *장: 변경 시점의 다른 속성 값 파악 쉽다, 설계가 쉽다. 
   *단: 공간낭비, 어떤속성이 변경되었는지 1row 만 가지고 알수없다
   원천 형상 바뀌면 이력도 변경 해줘야 함
   자식이 있다면 스냅샷 

 2) 속성 레벨
   *장: 공간 절약, 무엇이 변경된 속성인지 파악 쉽다, 원천형상에 독립적 => 컬럼추가 필요 X
   *단: 변경시점의 다른속성값 알기 어려워. 원천테이블의 이력요건 속성이 추가되면 비효율 증가 

* 추가적 방식(vertical) 
 - 종(vertical)테이블 방식
    
 - 통합이력 
   *장: 업무변경에 유연하다
   *단: 구조가 데이터가 되어 파악 쉽지않다 

 3) 속성그룹 레벨
   같이사용되는 속성들 묶어서 관리 
   *장: 비교적 공간 절약, 
  
* 원천이 종속이면 이력도 종속 
 
 
- 이력관리 형태 
  1) 점이력 
변경시점만 관리 
특정시점의 값 찾기 어려움 

ex)   
SELECT A.기관코드, A.기관거래등급
  FROM 기관정보 A, 
     ( SELECT 기관코드, MAX(적용일자)
 FROM 기관정보 
WHERE 적용일자 <='20080701'
GROUP BY 기관코드 
  )B
 WHERE A.기관코드 = B.기관코드 
   AND A.적용일자 = B.적용일자 
;   

  2) 선분이력
시작시점/종료시점 관리 
특정시점의 값 찾는 것에 유리 

SELECT 기관코드, A.기관거래등급
 FROM 기관정보 A
WHERE '20081201' BETWEEN 시작일자 AND 종료일자 
;

선분이라는 것은 시작점, 끝점이 이어진 2차원 형태인데 현재의 RDB 에서는 이를 구현할수 없다
(xmltype 도 있는데 선분타입이라는 것을 못만들 이유는 없을것 같다)
편의상 시작점, 끝점을 속성으로 만들어 선분을 구현했으나(흉내냈으나) unique 제약조건 등을 걸수가 없다.
이력에서 사용하는 선분은 겹치는 지점없이 이어져야(unique 해야) 하지만 이를 DB에선 표현 못함.
겹치거나 누락되는 지점이 발생되는것을 제약조건 등으로 막을수 없다. 
 
 * 선분이력 모델의 자식 => 상속되지 않는 시작/종료일자 속성(엔코아에서는 이를 $로 표현)
 pk에 컬럼추가를 할때 시작 vs 종료  무엇을 추가를 할까 => 종료 : only 성능관점 
 
  - pk결정
  부모pk + 1) 종료일자
                 2) 시작일자 
                 3) 순번

논리적으로는 부모pk속성 + 시작,종료속성 모두를 상속해야 함 (선분을 표현하기 위해서 두점이 필요하므로)
 * 중복 row 는 존재하지 않는다. => 부모에 존재한 row 는 자식에 나오지 말아야함
   

데이터 모델링에서 엔터티 통합과 분리는 다음과 같은 경우에 수행한다 

# 엔터티 통합

1. 데이터의 성격(주제)이 유사한 경우
2. 식별자가 동일하면서 유사한 속성이 존재하는 경우
3. 식별자는 다르지만 기초 속성이 유사한 경우

통합을 고려할 때는 다음 조건을 모두 만족해야 한다
- 별개의 요건으로 사용되지 않고 주로 같이 조회된다
- 통합해서 성능 문제를 일으키지 않는다
- 현행 데이터가 존재하며 마이그레이션하는 데 문제가 없다

# 엔터티 통합의 주요 장점

1. 확장성 향상: 비슷한 유형의 업무 발생시 스키마 변경을 최소화하면서 코드 값 등의 인스턴스 추가로 업무 수용 가능
2. 유지보수 효율성: 엔터티 개수가 감소하여 데이터베이스의 유지보수가 쉬워집니다
3. 개발 용이성: 엔터티 통합에 따른 배타 관계 해소로 액세스 경로의 효율성이 향상되고, 단순한 SQL 작성 가능
4. 모델의 단순성, 가독성 향상: 슈퍼타입, 서브타입에서 발생하는 비즈니스 요건(관계)를 명확히 표현할 수 있다
5. 조인 발생 최소화: 정보가 한 곳에 집약되어 있어 조인 발생을 최소화하여 성능 저하를 예방 가능


# 엔터티 분리

1. 속성에 대한 제약 조건을 정확하게 지정해야 하는 중요한 데이터 값을 입력해야 할 경우
2. 엔터티에 속한 속성이 많아 성능이나 관리 측면에서 좋지 않은 영향을 미칠 때
3. 사용 빈도에 따라 자주 사용되지 않는 속성이나 덜 중요한 속성을 분리할 때
 

엔터티는 모델링에서 기본에 해당하고 상당히 중요한 부분이다.

엔코아 식 분류
 - key : 최초부터 창조된 엔터티, 주로 데이터 발생의 주체나 목적어
 - main : 업무의 주요 행위 데이터를 발생시키는 주체
 - action : 실제 발생하는 내역 업무
 
김기창DA 님 분류
 - 기준엔터티
    업무의 기준이 되는 데이터를 관리하는 엔터티
    환율·우편번호·이자율·코드 등의 업무적으로 참조되는 데이터를 관리하는 엔터티
    RI 제약이 존재하는지 숙고해야.
    ex) 기준데이터가 바뀌면 참조하던 것들이 바뀌어야 하는가? 과거시점이니 놔두는가?
       
 - 실체엔터티
   실체의 존재(Existence)와 연관된 데이터를 관리
   실체가 발생시킨 데이터를 관리하지는 않는다. (ex. 실체가 고객일때 계약, 출금 등의 이벤트)
   인조 식별자가 집합의 성격을 더 직관적, 명확하게 해줌 ex) 고객번호 
 - 행위엔터티
   어떤 실체의 업무 행위나 활동에 의해서 생긴 원천(Raw) 데이터
   특징은 주 식별자가 복잡하다. 주로 업무식별자 
   하위 엔터티가 소수로 존재, 굳이 인조 식별자를 사용할 필요X
 - 가공엔터티
    원천데이터를 가공하여 생긴 데이터를 관리하는 엔터티. 집계, 요약, 임시 데이터를 관리하는 엔터티

출처: https://dataprofessional.tistory.com/21 

1. 엔터티 정의

 - 엔터티 후보 수집 -> 엔터티 후보 선정 -> 엔터티 확정

2. 식별자 정의

 - 식별자 부여 -> 식별자 확정

3. 관계 정의

 - 관계 형태 설정 -> 식별/비식별 관계설정 -> M:N 관계 해소

4. 속성 정의

 - 속성후보수집 -> 속성후보선정 -> 속성확정 -> 속성검증

5. 정규화/이력관리

- 정규화 -> 이력관리 결정

6. 데이터 모델 검증

 - 사례데이터 작성 -> 데이터 모델 보완 

데이터 표준화 관련 용어, 단어, 도메인, 코드에 대해 정리해보자 

1. 용어 
 종류 : 업무/비표준전문/표준 용어 
  - 업무용어 : 화면등 label 용. 길이 x
  - 비표준전문 : 전문처리용. 길이포함. 
  - 표준용어 : 테이블 컬럼용. 

2. 단어 
 1) 단일어

 한음절 단어는 되도록 표준에서 제외하는 것이 좋다. ex) 회식 + 비(x) ,  회식 + 비용(O)

 예외적으로 허용해야하는 경우는 복합어로 만드는 것이 낫다.  ex) 회식 + 비(x) , 회식비(O)

 2) 복합어 
   - 합성어
   - 파생어

단어 + 단어 로 구성된 단어가 각각의 단어에서 의미가 유추되지 않으면 복합어로 구성이 낫다 

 3) 기타
  관용어, 외래어 
  금칙어/유사어
  동음이의/ 이음동의

 3. 도메인, 인포타입 ( 코드/번호/그룹) 

 - 도메인
도메인은 속성이 가질 수 있는 값의 범위
특정 데이터 필드에 허용되는 값의 집합을 정의

- 인포타입
인포타입은 도메인에 대한 구체적인 데이터 타입과 길이를 지정한 것
ex) 일자 도메인 : Varchar(8) 또는 Date 인포타입을 선택
  계좌 번호 속성:  'VC12' (12자리 가변 문자열) 인포타입을 지정
  

 4. 코드 
 - 코드란 무엇인가 : 속성의 값을 기호로 변환한 것
 - 필요성 :  프로그램 소스코드 수준에서 로직분기, 데이터를 유형화하여 조회, 
            짧게 압축 => 가독성, 저장공간 효율성.  
         본질은 분류, 범주화의 도구 
  - 코드인 것과 아닌 것 (상품코드 vs 상품번호) 
  - 코드 종류 : 
 공통코드 : 시스템 전반에 걸쳐 공통적으로 사용되는 코드
 일반적으로 공통코드 테이블에서 통합 관리
 코드와 코드값만으로 서비스가 가능한 경우에 사용
 개별코드 : 특정 업무 영역이나 테이블에서 개별적으로 정의되고 관리되는 코드
   코드와 코드값 외에 추가적인 정보가 필요한 경우 사용
 외부코드 : 외부코드는 외부 기관이나 표준에 의해 정의되어 사용되는 코드
   시스템 간 호환성을 위해 외부에서 정의된 코드를 그대로 사용
 
 * 식별자와 코드를 구분하기. 

  PD001, PD002 => 상품번호, 
  저축성, 연금성.. => 상품코드(범주로 분류)

[부서코드]  => [부서]
#부서코드  => #부서번호 (* 식별자)
부서장id     => 부서장id
부서구분    => 부서유형코드 (*코드)

데이터표준화는 업무적으로 협의하기에 따른 부분이 많기에 그냥 이렇게 정리해볼수 있다는 걸로만 알아두자.