개요
하나의 서비스를 만들면 서비스 내에서 정의하여 사용하는 수많은 코드 데이터들이 있다.
이런 코드들을 한데 모아서 관리하기 위한 목적으로 설계하는 것이 공통코드 테이블이다.
관리자 페이지가 따로 존재하는 경우에는 필수적으로 "공통 코드 관리" 메뉴를 확인할 수 있게 된다.
이는 거의 프로젝트 초반에 가장 먼저 설계하는 테이블들 중 하나이다.
어쨋든 우리는 서비스를 만들때 내부에서 많은 코드들을 정의해서 사용하곤 한다.
어떤 경우에는 해당 코드들을 테이블에서 관리하지 않고 소스 코드에서 관리하는 경우도 있다.
(일정 규모 이상의 서비스를 만들고 있다면, 소스 코드에서 관리하는 일은 없다고 본다.)
소스코드에서 관리하게 되면 데이터를 조회 할때 그 코드값이 무엇을 의미하는지 소스코드를 직접 확인해야 한다.
코드값이 추가되거나 변경될때마다 소스코드를 수정해야 하는 등
이런 식의 방식은 소프트웨어의 품질을 논하기 전인 90년대에나 쓰던 방식이다.
자신이 지금 만드는 서비스를 자신이 평생 관리할게 아니기 때문에 만들어두어야 한다.
공통 코드 테이블의 구성 요소
공통코드 관리를 위해서는 크게 두 개 종류의 데이터를 관리해야 한다.
- 공통 코드 (코드, 코드명, 코드 설명) 정보
- 공통 코드의 카테고리 또는 그룹 (그룹코드, 그룹코드명, 그룹코드 설명) 정보
구성은 아래와 같은 형식일 것이고, 크게 공통코드 유형(카테고리 또는 그룹)와 공통코드(카테고리에 포함된 코드)을 관리해야 된다.
결국 성격이 다소 다른 데이터 이므로, 2개의 테이블로 도출할 수도 있지만, 유사한 성격이므로 하나의 테이블로 관리하기도 한다.
테이블 별도로 관리 하기
테이블 간의 가독성을 높이고 싶다면, 그룹코드 테이블과 공통코드 테이블을 분리하는 것도 좋은 방법이다.
이와 같은 방법은 실제로 많은 프로젝트에서 차용하고 있다.
그와 같이 할 경우, 아래와 같은 DDL의 형식일 것이다.
CREATE TABLE 그룹코드
(
GROUP_CODE CHAR(3) NOT NULL ,
GROUP_CODE_NAME VARCHAR2(60) NULL ,
GROUP_CODE_DESCRIPTION VARCHAR2(200) NULL ,
USE_YN CHAR(1) NULL ,
REGIST_DATE DATE NULL ,
REGISTER_ID VARCHAR2(20) NULL ,
UPDATE_DATE DATE NULL ,
UPDATER_ID VARCHAR2(20) NULL ,
CONSTRAINT 그룹코드_PK PRIMARY KEY (CL_CODE)
);
CREATE TABLE 공통코드
(
COMMON_CODE VARCHAR2(6) NOT NULL ,
COMMON_CODE_NAME VARCHAR2(60) NULL ,
COMMON_CODE_DESCRIPTION VARCHAR2(200) NULL ,
USE_YN CHAR(1) NULL ,
GROUP_CODE CHAR(3) NULL , // Point!!
REGIST_DATE DATE NULL ,
REGISTER_ID VARCHAR2(20) NULL ,
UPDATE_DATE DATE NULL ,
UPDATER_ID VARCHAR2(20) NULL ,
CONSTRAINT 공통코드_PK PRIMARY_KEY (COMMON_CODE),
CONSTRAINT 공통코드_FK1 FOREIGN KEY (GROUP_CODE) REFERENCES 그룹코드 (GROUP_CODE) ON DELETE SET NULL
);
CREATE INDEX 공통코드_IX_01 ON 공통코드 (GROUP_CODE ASC);
실제로, 아래와 같은 예시로 많이 사용된다.
// 그룹코드
CREATE TABLE `group_code` (
`group_code` char(6) COLLATE utf8mb4_bin NOT NULL COMMENT '그룹코드',
`group_code_name` varchar(100) COLLATE utf8mb4_bin NOT NULL COMMENT '그룹코드명',
`group_code_description` varchar(1000) COLLATE utf8mb4_bin NOT NULL COMMENT '그룹코드설명',
`use_yn` char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT 'Y' COMMENT '사용여부',
`user_defntn_attr_value1` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '사용자정의속성값1',
`user_defntn_attr_value2` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '사용자정의속성값2',
`user_defntn_attr_value3` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '사용자정의속성값3',
`user_defntn_attr_value4` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '사용자정의속성값4',
`user_defntn_attr_value5` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '사용자정의속성값5',
`regist_dtm` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일시',
`register_id` varchar(30) COLLATE utf8mb4_bin NOT NULL COMMENT '등록자ID',
`update_dtm` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
`updater_id` varchar(30) COLLATE utf8mb4_bin NOT NULL COMMENT '수정자ID',
PRIMARY KEY (`group_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='그룹코드';
// 공통코드
CREATE TABLE `common_code` (
`group_code` char(6) COLLATE utf8mb4_bin NOT NULL COMMENT '그룹코드',
`common_code` varchar(100) COLLATE utf8mb4_bin NOT NULL COMMENT '공통코드',
`common_code_name` varchar(100) COLLATE utf8mb4_bin NOT NULL COMMENT '공통코드명',
`common_code_description` varchar(1000) COLLATE utf8mb4_bin NOT NULL COMMENT '공통코드 설명',
`sort_seq` int NOT NULL DEFAULT '0' COMMENT '정렬순서',
`use_yn` char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT 'Y' COMMENT '사용여부',
`valid_begin_dt` date NOT NULL COMMENT '유효시작일자',
`valid_end_dt` date NOT NULL COMMENT '유효종료일자',
`user_defntn_attr_value1` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '사용자정의속성값1',
`user_defntn_attr_value2` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '사용자정의속성값2',
`user_defntn_attr_value3` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '사용자정의속성값3',
`user_defntn_attr_value4` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '사용자정의속성값4',
`user_defntn_attr_value5` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '사용자정의속성값5',
`regist_dtm` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일시',
`register_id` varchar(30) COLLATE utf8mb4_bin NOT NULL COMMENT '등록자ID',
`update_dtm` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
`updater_id` varchar(30) COLLATE utf8mb4_bin NOT NULL COMMENT '수정자ID',
PRIMARY KEY (`group_code`,`common_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='공통코드';
테이블 한 개로 관리 하기
테이블 한 개로 공통코드와 그룹코드를 관리한다면, DDL 형식은 아래와 같다.
CREATE TABLE 공통코드
(
COMMON_CODE_ID VARCHAR2(6) NOT NULL ,
COMMON_CODE VARCHAR2(15) NOT NULL ,
COMMON_CODE_NAME VARCHAR2(60) NULL ,
COMMON_CODE_DESCRIPTION VARCHAR2(200) NULL ,
USE_YN CHAR(1) NULL ,
REGIST_DTM DATE NULL ,
REGISTER_ID VARCHAR2(20) NULL ,
UPDATE_DTM DATE NULL ,
UPDATER_ID VARCHAR2(20) NULL ,
CONSTRAINT 공통코드_PK_01 PRIMARY KEY (COMMON_CODE_ID, COMMON_CODE),
CONSTRAINT 공통코드_FK_01 FOREIGN KEY (COMMON_CODE_ID) REFERENCES 공통코드(COMMON_CODE_ID) // Point!
);
CREATE INDEX 공통코드_IX_01 ON 공통코드 (COMMON_CODE_ID ASC);
위와 같은 코드 정의방식은 실제 SI에서 많이 사용하는 형태인데, 코드값의 정의는 필요에 따라 다르게 정의할 수도 있겠다. 코드 구성이 처음 볼때는 의미파악도 전혀 안되고 도대체 알 수 없는 구성인 것 같지만 실제 사용해 보면 편리한 점이 많다.
1. 실제 코드가 사용된 테이블을 조회해서 해당 코드값의 의미를 찾을 때 편리하다.
select * from 공통코드 where 공통코드='A0101' ;위와 같은 쿼리로 실제 입력된 데이터만을 가지고 바로 코드값의 의미를 찾을 수 있다.
물론, 이 장점은 공통코드값을 전체 테이블에서 unique하게 구성함으로써 얻을 수 있다.
2. 특정 코드값만으로 코드유형을 알 수 있고 해당 유형의 코드값들을 조회하기 편리하다.
코드 구성(format)이 전체 5자리로 구성되어 있을 경우 앞의 3자리가 코드 유형을 의미한다는
일정한 규칙이 있을 경우, 우리는 코드값만 알면 앞의 3자리를 조회해서 코드유형의 의미를
찾을 수 있다.
select * from 공통코드 where 공통코드= 'A01';
또한, 유사한 방법으로 해당 코드 유형의 모든 코드값을 조회할 수 있다.
select * from 공통코드 where 상위공통코드='A01';
JOIN을 활용하여, 조회할 경우 아래와 같은 결과가 도출된다고 보면 된다.
참고로, 공통코드를 가져올때에는 서브쿼리나 function을 사용하지 않고, 조인을 이용하도록 하자.
(서브 쿼리는 결과행 row 수만큼 수행하므로, Join이 성능상으로 더 빠르다.)
# 출처
'[DBA] Database > 데이터베이스 설계' 카테고리의 다른 글
| [DB] 공통코드, 도메인 부수기 (0) | 2021.05.21 |
|---|
최근댓글