[TOY PROJECT] 야근/대휴 관리 시스템 개발 - 데이터베이스 설계 편

토이 프로젝트로 야근이나 추가근무를 하면 그 시간만큼 대체 휴무를 제공하는 시스템을 구축해보자. 소수(10명내외)가 사용하는 시스템이라고 가정하고 설계부터 개발까지 10시간 내외면 만들어낼 수 있기 때문에 퀄리티는 보장하지 않기로 한다. 하지만 DB부터 웹서버, 화면까지 생각해본다는 점에서 프로젝트 성으로 진행해보기로 하자. 나는 괜찮으면 팀 내에서 사용할 수 있도록 공유할 생각이다.

20190608 깃헙주소 추가 https://github.com/parksuseong/toy_time_manage

1. 화면편
[TOY PROJECT] 야근/대휴 관리 시스템 개발 - 반응형 화면 설계 편( bootstrap , jqgrid, get/post 호출 )
2. 서버편
[TOY PROJECT] 야근/대휴 관리 시스템 개발 - 서버 설계 편( express 사용, mssql 연동, get/post 처리 )
3. 데이터베이스 편
- [TOY PROJECT] 야근/대휴 관리 시스템 개발 - 데이터베이스 설계 편

먼저 데이터베이스를 설계해보도록 하자. Node js를 활용하기 위해 자료 서칭을 하다보니 몽고DB와 mysql 예제가 많았지만 나는 프로시저를 활용하기 위해(?) MSSQL을 사용하기로 하였다.

테이블과 프로시저 스키마는 다음처럼 생각을 했다. 시간은 분단위로 관리한다.

1. 야근 기록 대장 테이블

2. 야근 등록 프로시저

3. 야근 사용기록 조회 프로시저

4. 대체휴무 기록 대장 테이블

5. 대체휴무 등록 프로시저

6. 대체휴무 사용기록 조회 프로시저

7. 잔여 대체휴무 조회 프로시저

이렇게 하면 1~3번과 4~6번은 거의 비슷하게 설계할 수 있기때문에 리소스를 줄일 수 있으며 7번 잔여 대체휴무 조회 프로시저에서 머리를 조금 쓰면 될 것 같았다.

1. 야근 기록 대장 테이블과 대체휴무 기록 대장 테이블을 생성한다.

-- 야근 기록 대장 테이블

CREATE TABLE OVERTIME_WORK_LOG(

USER_ID VARCHAR(6) NOT NULL, -- 사용자 ID

FR_DTS DATETIME2(0) NOT NULL, -- 시작일시

TO_DTS DATETIME2(0) NOT NULL, -- 종료일시

RSN VARCHAR(100) NOT NULL, -- 사유

GEN_TIME INT NOT NULL, -- 근무 시간

REG_DTS DATETIME2(0) NOT NULL, -- 등록 일시

CONSTRAINT PK_OVERTIME_WORK_LOG PRIMARY KEY (USER_ID,FR_DTS)

) ON [PRIMARY]

GO

-- 대휴 기록 대장 테이블

CREATE TABLE TIME_OFF_LOG(

USER_ID VARCHAR(6) NOT NULL, -- 사용자 ID

FR_DTS DATETIME2(0) NOT NULL, -- 시작일시

TO_DTS DATETIME2(0) NOT NULL, -- 종료일시

USE_TIME INT NOT NULL, -- 대휴 사용 시간

REG_DTS DATETIME2(0) NOT NULL, -- 등록 일시

CONSTRAINT PK_TIME_OFF_LOG PRIMARY KEY (USER_ID,FR_DTS)

) ON [PRIMARY]

이 두 테이블은 야근과 대휴를 기록하는 HISTORY 성 테이블이다.
사실 야근 기록 대장 테이블에 대휴 사용 유무(소진 플래그)를 넣을까 고민도 했었지만 굳이 화면을 잘 설계하면 필요가 없어보였다. 또한 만약 소진 플래그 컬럼을 넣는다면 히스토리성 팩트 테이블이 업데이트가 일어난다는 점이 찝찝하고 그렇다고 테이블을 하나 더 만들어 분리하자니 관리 포인트가 늘어나므로 PASS하기로 하였다.

2. 야근 등록 프로시저와 대휴 등록 프로시저를 만든다.

-- 먼저 야근 등록프로시저

CREATE PROC dbo.USP_INSERT_OVERTIME_WORK_LOG

(

    @USER_ID VARCHAR(6) = '',

@FR_DT VARCHAR(20) = '',

@TO_DT VARCHAR(20) = '',

@RSN   VARCHAR(100) = ''

)

AS

INSERT INTO dbo.OVERTIME_WORK_LOG 

(  USER_ID, FR_DTS, TO_DTS, RSN, GEN_TIME, REG_DTS)

VALUES(

 @USER_ID

,CONVERT(DATETIME,@FR_DT,120)

,CONVERT(DATETIME,@TO_DT,120)

,@RSN

,DATEDIFF(MINUTE,CONVERT(DATETIME,@FR_DT,120),CONVERT(DATETIME,@TO_DT,120))

,GETDATE()

);

-- 대휴 등록 프로시저

CREATE PROC dbo.USP_INSERT_TIME_OFF_LOG

(

    @USER_ID VARCHAR(6) = '',

    @FR_DT VARCHAR(20) = '',

    @TO_DT VARCHAR(20) = ''

)

AS

INSERT INTO dbo.TIME_OFF_LOG 

( USER_ID, FR_DTS, TO_DTS, USE_TIME, REG_DTS )

VALUES(

 @USER_ID

,CONVERT(DATETIME,@FR_DT,120)

,CONVERT(DATETIME,@TO_DT,120)

,DATEDIFF(MINUTE,CONVERT(DATETIME,@FR_DT,120),CONVERT(DATETIME,@TO_DT,120))

,GETDATE()

);

3. 야근 사용기록과 대휴 사용기록을 조회하는 프로시저를 만든다

-- 먼저 야근 기록을 조회하는 프로시저

CREATE PROC dbo.USPS_SELECT_OVERTIME_WORK_LOG

(

    @USER_ID VARCHAR(6) = ''

)

AS

SELECT USER_ID
, CONVERT(VARCHAR,FR_DTS) AS FR_DTS
, CONVERT(VARCHAR,TO_DTS) AS TO_DTS
, RSN
, GEN_TIME
, CONVERT(VARCHAR,REG_DTS) AS REG_DTS
FROM OVERTIME_WORK_LOG
WHERE USER_ID = @USER_ID 

-- 대휴 기록을 조회하는 프로시저

CREATE PROC dbo.USPS_SELECT_TIME_OFF_LOG

(

    @USER_ID VARCHAR(6) = ''

)

AS

SELECT
  USER_ID
, CONVERT(VARCHAR,FR_DTS) AS FR_DTS
, CONVERT(VARCHAR,TO_DTS) AS TO_DTS
, USE_TIME
, CONVERT(VARCHAR,REG_DTS) AS REG_DTS
FROM TIME_OFF_LOG
WHERE USER_ID = @USER_ID

4. 잔여 대휴 시간 조회 

-- 근무 외 시간(새벽 등)에 발생한 시간에 대해서는 1.5배 적용을 한다면 프로시저 마지막에 SELECT 할 때 * 1.5를 해주면 된다.

CREATE PROC dbo.USPS_SELECT_SPARE_TIME_OFF

( @USER_ID VARCHAR(6) = '' )

AS

DECLARE @WORK_SUM BIGINT

DECLARE @OFF_SUM BIGINT

SELECT @WORK_SUM = ISNULL(SUM(GEN_TIME),0)
FROM OVERTIME_WORK_LOG
WHERE USER_ID = @USER_ID

SELECT @OFF_SUM = ISNULL(SUM(USE_TIME),0)
FROM TIME_OFF_LOG
WHERE USER_ID = @USER_ID

SELECT @USER_ID AS USER_ID
, @WORK_SUM - @OFF_SUM AS SPARE_MINUTE
, (@WORK_SUM - @OFF_SUM)/60. AS SPARE_HOUR

날짜 같은 경우는 DB에서 VARCHAR 형식으로 미리 바꿔서 넘겨주도록 하였다.

스크립트는 아래의 경로에 남겨놓았다.
https://drive.google.com/open?id=1mestYmeMhxOX_t90PDuHtdZ5B_vbZ5Cr