[DB] 테이블 설계의 중요성

참고 자료

유투브 쉬운코드 DB 테이블 설계 잘못하면 어떤 문제가 생길 수 있을까요?

잘못 설계된 테이블의 여러 가지 문제점 

EMPLOYEE_DEPARTMENT: 잘못 설계한 테이블 

 

현재 EMPLOYEE_DEPARTMENT 에 2개의 튜플 데이터가 있다. 

MESSI, JINHO는 같은 부서이다. 

그래서 dept_id, dept_name, dept_leader_id 같이 부서 관련된 정보가 중복된다. 

 

이런 중복된 데이터의 문제점은 뭘까?

1. 저장 공간 낭비

2. 실수로 인한 데이터 불일치 가능성 존재 (똑같은 부서인데 수기로 입력하면 dept_id, dept_name, dept_leader_id가 다를 수 있다.) Insertion Anomalies (삽입 이상)

 

아직 부서 배치를 받지 않은 사원 데이터 추가 

할 수 있는 한 null 값은 적게 쓰는 것이 좋다.

 

임직원이 없는 부서를 추가한다면?

empl_id는 PK여서 null 삽입도 불가능하다. 부서의 정보인데 empl_id가 있는 것도 이상하다. 

 

임직원이 없는 부서에 직원이 생겼다. 

 

본래 부서의 정보를 가지던 empl_id = 4는 이제 필요 없어서 삭제해 줘야 한다. 

→ 번거롭다. 

 

Deletion Anomalies (삭제 이상)

QA 부서에 유일하게 남아있는 유일한 직원이었는데 QA 부서 정보 자체가 사라진다 

 

UPDATE  Anomalies

DEV 부서의 이름을 DEV1로 변경하면 DEV 부서를 가지는 모든 튜플을 변경하지 않으면 UPDATE Anomalies가 발생한다.

이를 관심사를 분리해서 테이블 분리를 한다면 DEPARTMENT 테이블의 부서 이름 하나만 바꾸면 끝난다.

 

별개의 관심사가 한 테이블에  

EMPLOYEE_DAPARTMENT 테이블은 EMPLOYEE(임직원 정보)와 DEPARTMENT(부서 정보) 2개의 관심사가 존재한다.

 

테이블 분리(관심사 분리)

이전 EMPLOYEE_DEPARTMENT 테이블을 2개의 테이블로 분리했다. 

EMPLOYEE 테이블의 PK는 empl_id DEPARTMENT 테이블의 PK는 dept_id 이다. 

또한 EMPLOYEE 테이블은 DEPARTMENT 테이블의 PK인 dept_id를 FK로 가진다. 

 

이제 새로운 임직원을 추가해 보자 

해당 임직원이 부서 배정을 아직 받지 못했다면 EMPLOYEE 테이블의 dept_id만 null로 삽입하고 

배정을 받았다면 dept_id에 해당 부서 PK를 넣어주면 끝이다. 

훨씬 간결하고 null 사용도 적다. 

Spurious Tuples (가짜 튜플)

사진 촬영 회사의 데이터베이스를 구축하는데 다음과 같은 테이블을 만들었다고 가정 

 

두 개의 테이블을 natural join 

SELECT * FROM DEPARTMENT_PROJECT natural join DEPARTMENT_LOCATION

 

NATURAL JOIN은 두 테이블 간의 동일한 이름을 가진 모든 컬럼을 기준으로 자동으로 JOIN을 수행한다.
즉, DEPARTMENT_PROJECT와 DEPARTMENT_LOCATION 테이블에 동일한 이름을 가진 컬럼이 있다면, 이 컬럼을 기준으로 JOIN이 이루어진다. (proj_location)

총 6개의 튜플이 join 결과로 나온다.

동일한 부서 id를 가지는데 부서 이름이 2개로 나온다. 

이런 튜플을 가짜 튜플 Spurious Tuples 라고 한다. 

 

테이블 분리 

바른 DB schema 설계

1. 의미적으로 관련있는 속성들끼리 테이블을 구성 

2. 중복 데이터를 최대한 허용하지 않도록 설계

3. join 수행 시 가짜 데이터가 생기지 않도록 설계

4. 되도록이면 null 값을 줄일 수 있는 방향으로 설계