인사정보 테이블 설계

 

오라클 데이터베이스에서 인사정보를 관리하기 위한 테이블 설계를 트리 구조로 구현하고, 인사발령 시 과거 인사이동 이력을 남기는 방식으로 설명드리겠습니다.

### 1. 테이블 설계

#### 1.1. 부서 테이블 (DEPARTMENTS)

CREATE TABLE DEPARTMENTS (
    DEPT_ID NUMBER PRIMARY KEY,
    DEPT_NAME VARCHAR2(100),
    PARENT_DEPT_ID NUMBER,
    FOREIGN KEY (PARENT_DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID)
);

- **DEPT_ID**: 부서 고유 ID
- **DEPT_NAME**: 부서 이름
- **PARENT_DEPT_ID**: 상위 부서 ID (트리 구조를 위한 외래키)

#### 1.2. 직급 테이블 (POSITIONS)

CREATE TABLE POSITIONS (
    POSITION_ID NUMBER PRIMARY KEY,
    POSITION_NAME VARCHAR2(100)
);

- **POSITION_ID**: 직급 고유 ID
- **POSITION_NAME**: 직급 이름

#### 1.3. 직원 테이블 (EMPLOYEES)

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(100),
    DEPT_ID NUMBER,
    POSITION_ID NUMBER,
    HIRE_DATE DATE,
    FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID),
    FOREIGN KEY (POSITION_ID) REFERENCES POSITIONS(POSITION_ID)
);

- **EMPLOYEE_ID**: 직원 고유 ID
- **NAME**: 직원 이름
- **DEPT_ID**: 소속 부서 ID
- **POSITION_ID**: 직급 ID
- **HIRE_DATE**: 입사일

#### 1.4. 인사이동 이력 테이블 (EMPLOYEE_HISTORY)

CREATE TABLE EMPLOYEE_HISTORY (
    HISTORY_ID NUMBER PRIMARY KEY,
    EMPLOYEE_ID NUMBER,
    OLD_DEPT_ID NUMBER,
    NEW_DEPT_ID NUMBER,
    OLD_POSITION_ID NUMBER,
    NEW_POSITION_ID NUMBER,
    CHANGE_DATE DATE,
    FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID),
    FOREIGN KEY (OLD_DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID),
    FOREIGN KEY (NEW_DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID),
    FOREIGN KEY (OLD_POSITION_ID) REFERENCES POSITIONS(POSITION_ID),
    FOREIGN KEY (NEW_POSITION_ID) REFERENCES POSITIONS(POSITION_ID)
);

- **HISTORY_ID**: 이력 고유 ID
- **EMPLOYEE_ID**: 직원 ID
- **OLD_DEPT_ID**: 이전 부서 ID
- **NEW_DEPT_ID**: 새로운 부서 ID
- **OLD_POSITION_ID**: 이전 직급 ID
- **NEW_POSITION_ID**: 새로운 직급 ID
- **CHANGE_DATE**: 인사이동 날짜

### 2. 트리 구조 구현

부서 테이블에서 `PARENT_DEPT_ID`를 사용하여 부서 간의 관계를 설정함으로써 트리 구조를 구현합니다. 이 구조를 통해 부서의 계층을 표현할 수 있습니다.

### 3. 인사 이동 처리

인사 이동 시에는 `EMPLOYEE_HISTORY` 테이블에 이전 부서와 직급 정보를 저장합니다. 이를 통해 직원의 인사 이동 이력을 추적할 수 있습니다. 인사 이동을 처리하는 SQL 예시는 다음과 같습니다.

-- 직원 인사 이동 예시
DECLARE
    v_employee_id NUMBER := 1; -- 이동할 직원 ID
    v_old_dept_id NUMBER;
    v_new_dept_id NUMBER := 2; -- 새로운 부서 ID
    v_old_position_id NUMBER;
    v_new_position_id NUMBER := 3; -- 새로운 직급 ID
BEGIN
    -- 현재 부서와 직급 조회
    SELECT DEPT_ID, POSITION_ID INTO v_old_dept_id, v_old_position_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = v_employee_id;

    -- 인사 이동 이력 저장
    INSERT INTO EMPLOYEE_HISTORY (HISTORY_ID, EMPLOYEE_ID, OLD_DEPT_ID, NEW_DEPT_ID, OLD_POSITION_ID, NEW_POSITION_ID, CHANGE_DATE)
    VALUES (EMPLOYEE_HISTORY_SEQ.NEXTVAL, v_employee_id, v_old_dept_id, v_new_dept_id, v_old_position_id, v_new_position_id, SYSDATE);

    -- 직원 부서와 직급 업데이트
    UPDATE EMPLOYEES
    SET DEPT_ID = v_new_dept_id, POSITION_ID = v_new_position_id
    WHERE EMPLOYEE_ID = v_employee_id;

    COMMIT;
END;

### 4. 요약

위와 같이 테이블을 설계하고 인사 이동 시 이력을 남기는 구조를 구현하면, 부서 및 직급에 따른 소속 정보를 트리 구조로 관리하고, 과거 인사 이동 이력을 체계적으로 저장할 수 있습니다. 추가적인 기능이나 질문이 있으시면 말씀해 주세요! 

이런 자료를 참고했어요.
[1] 네이버 블로그 - 인사관리 구축시 조직관리 체크포인트 - 네이버 블로그 (https://m.blog.naver.com/jiyongchoi/221804966165)
[2] 지능형 입찰 정보 서비스 - 사업계획서 (https://jodal.ai/filedownload/pre/1410386/)
[3] 대원대학교 - 제 안 요 청 서 (https://www.daewon.ac.kr/common/downLoad.mbs?fileSeq=173735&boardId=24)
[4] 다우오피스 - 조직개편, 승진, 부서이동시 발령 등록 방법 (https://account.daouoffice.co.kr/hc/ko/articles/4405562988569-%EC%A1%B0%EC%A7%81%EA%B0%9C%ED%8E%B8-%EC%8A%B9%EC%A7%84-%EB%B6%80%EC%84%9C%EC%9D%B4%EB%8F%99%EC%8B%9C-%EB%B0%9C%EB%A0%B9-%EB%93%B1%EB%A1%9D-%EB%B0%A9%EB%B2%95)