11장 31가지 맛
개인연락처 정보 테이블에서, 호칭은 몇가지 값만 갖는 칼럼의 좋은 예다. Mr. Mrs. Dr. Rev. 를 지원하면 사실상 거의 모든 사람을 고려한 것이다. 이 목록을 데이터 타입이나 제약조건을 이용해 칼럼 정의에 지정해, 이 salutaion 컬럼에 실수로 다른 문자열이 입력되지 않도록 할 수 있다.
CREATE TABLE PersonlContacts (
...
salutation VARCHAR(4)
CHECK ( salutation IN ('MR.','Mrs.','MS.','Dr.','Rev.'))
);
프랑스 쪽 호칭도 추가해야 할 경우 이런값도 지원하도록 연락처 테이블을 변경해야한다. 이때 테이블에대한 접근을 차단하지 않고는 불가능 할 수도있다.
11.1 목표 : 칼럼을 특정 값으로 제한하기
- Bugs테이블에서 ,status 칼럼은 주어진 버그에 대해 NEW, IN PROGRESS, FIXED 과 같은 값을 가진다.
- 이들 상태의 의미는 프로젝트에서 버그를 어떻게 관리하냐에 따라 다르지만, 중요한 점은 status 칼럼에 들어가는 데이터는 이 값 중 하나여야 한다.
베스킨라빈스 31 : 첨엔 31가지맛으로 고정된 집합이었지만 지금은 더 많은 맛
11.2 안티패턴: 칼럼 정의에 값 지정
- 많은 사람들이 칼럼을 정의할 때 유효한 데이터 값을 지정한다. 칼럼 정의는 메타데이터, 즉 테이블 구조 정의의 일부다.
- 예를 들어, 칼럼에 CHECK 제약조건을 정의할 수 있다. 이 제약조건은 제약조건을 실패하게 한는 INSERT난 UPDATE를 허용하지 않는다.
CREATE TABLE Bugs (
...
status VARCHAR(20) CHECK (status IN ('NEW','IN PROGRESS', 'FIXED'))
);
- MySQL은 칼럼을 특정 값의 집합으로 제한하는 ,ENUM이라 불리는 비표준 데이터 타입을 지원한다.
CREATE TABLE Bugs (
...
status ENUM( 'NEW', 'IN PROGRESS', 'FIXED')
);
- 값은 문자열로 선언하지만 내부적으로는 문자열이 해당 문자열이 몇번째인지를 나타내는 서수(ordinal number)로 저장하도록 구현 .
- 다른 방법은 도메인이나 사용자 정의 타입을 사용, 이를 이용해 칼럼에 미리 지정한 값만 허용하도록 제한하고, 동일한 도메인이나 데이터 타입을 데이터베이스 내 여러 칼럼에 적용할 수 있다. 그러나 이런 기능은 많은 관계형 DBMS제품에서 아직 지원되지 않고 있다.
- 미리 허용된 값을 확인하고 다른 값이 들어오면 에러를 발생시키는 트리거를 사용할 수 있다.
이모든 방법은 단점을 가지고있다.
중간에 있는 게 뭐지?
- 버그추적시스템의 사용자 인터페이스를 개발하고있다고 가정
- 사용자는 버그를 편집할 수 있다.
- 사용자가 유효한 상태 값 중 하나를 선택하게 하기 위해 이들 값을 드롭다운 메뉴 컨트롤에 채우기로함
- 이때 테이터베이스에 어떻게 쿼리하면 status 컬럼에 현재 허용되는 값의 목록을 얻을 수 있을까?
SELECT DISTINCT status FROM Bugs;
- 모든 버그상태가 NEW라면 이쿼리는 NEW만 리턴.
- 현재 사용중인 상태 이외의 다른 상태로 버그를 바꿀 수 없다.
status에 허용된 값의 완전한 목록을 얻으려면, 칼럼의 메타데이터를 쿼리해야 한다. 대부분의 데이터베이스는 이런 종류의 쿼리를 위해 시스템 뷰를 제공하지만, 사용 방법은 복잡할 수 있다.
예를들어 MySQL에서 ENUM데이터 타입을 사용했다면 INFORMATION_SCHEM(각컬럼의 정보) A에 있는 시스템 뷰를 사용해 다음과 같이 쿼리할 수 있다.
SELECT column_type
FROM information_schema.clumns
WHERE table_schema = 'bugtracker_schema'
AND table_name ='bugs'
AND column_name = 'status';
- 체크 제약조건 또는 ENUM 데이터 타입의 정의를 담은 문자열을 얻게 된다.
- MySQL에서 위 쿼리는 LONGTEXT타입으로 ENUM('NEW','INPROGRESS','FIXED')를 리턴하는데, 괄호와 쉼표, 홑 따옴표가 포함되어 있다. 사용자 인터페이스 컨트롤에서 이를 사용하려면 문자열을 파싱해 각각의 값을 추출하는 애플리케이션 코드를 작성해야 한다.
- 이런식으로 체크 제약조건, 도메인, 사용자 정의타입을 확인해야 할 경우 쿼리는 점점 더 복잡해 진다. 사람들은 대부분 복잡한 것을 패해, 값의 목록을 애플리케이션 코드에 동일하게 유지하는 단순한 방식을 취할 것이다.
- 그리고 애플리케이션 데이터와 데이터베이스 메타데이터가 서로 맞지 않게 되면 문제가 발생할 것이다.
새로운 맛 추가하기
가장 흔한 변경은 허용된 값을 추가하거나 삭제하는 것이다. ENUM이나 체크 제약조건에 값을 추가하거나 삭제하는 문법은 없다. 단지 새로운 값의 집합으로 칼럼을 재정의 할 수 있을 뿐이다. 다음은 MySQL에서 ENUM에 새로운 상태 값 DUPLICATE를 추가하는 예다.
ALTER TABLE Bugs MODIFY COLUMN status ENUM('NEW','IN PROGRESS','FIXED','DUPLICATE');
칼럼의 이전 정의에서 NEW, IN PROGRESS, FIXED가 허용되었따는 사실을 알아야 하는데, 현재 허용되는 값의 집합을 조회하기가 어렵다는 문제로 돌아간다.
어떤 데이터 베이스 제품에서는 테이블이 비어 있지 않으면 칼럼 정의를 변경 할 수 없다. 테이블 내용은 모두 덤프한 다음,테이블을 재정의하고, 저장했던 데이터를 다시 넣어주어야 한다. 이작업을 하는동안 테이블에 접근할 수 없게 된다. 이런작업은 흔하기 때문에 ETL(extract, transform, load)이란 이름도 가지고 있다. 어떤제품에서는 데이터가 있는 상태에서 ALTER TABLE 명령을 실행해 테이블을 재구성하는 기능을 지원하지만, 여전히 복잡하고 비용이 많이 드는 작업이다.
정책적으로, 메타데이터를 변경하는 것은, 즉 테이블이나 칼럼의 정의를 변경하는 것은 드물어야하고 주의를 요해야한다.
예전 맛은 절대 없어지지 않는다.
값을 더 이상 사용되지 않게 만들면, 과거 데이터가 망가질 수 있다.
품질 제어 프로세스가 바뀌어 FIXED를 CODE COMPLETE와 VERIFIED 두 상태로 나누어야 한다고 가정
ALTER TABLE Bugs MODIFY COLUMN status
ENUM('NEW','IN PROGRESS','CODE COMPLETE','VERIFIED');
ENUM에서 FIXED를 삭제하면, 상태가 FIXED인 버그는 어떻게 할 것인가?
상태가 FIXED인 버그를 VERIFIED로 바꿔야할까? 아니면 없어진 값을 NULL이나 디폴트 값으로 바꿔야 할까?
없어질 값이라도 과거 행이 참조 한 그대로 유지해야 할 수도 있다. 그러나 이런경우에 더 이상 사용되지 않는값을 어떻게 식별할 수 있을까? 어떻게 사용자 인터페이스에서 제외해 더 이상 사용되지 않는 값을 입력하지 못하게 할 수 있을까?
포팅이 어렵다.
- 체크 제약조건, 도메인, 사용자 정의 타입은 모든 SQL 데이터베이스 제품에서 균일하게 지원되는 기능이 아니다.
- ENUM데이터 타입은 MySQL의 고유기능이다.
- 각 데이터베이스 제품마다 칼럼 정의에 넣을 수 있는 목록의 길이 제한이 다를 것이다.
- 트리거 작성 언어도 마찬가지다.
- 이런 차이로 인해 여러 데이터베이스 제품을 지원할 필요가 있는 경우에는 안티패턴에서 설명한 방법을 사용하기 어렵다.
11.3 안티패턴 인식 방법
- 데이터베이스를 내려야 애플리케이션 메뉴의 선택항목을 추가할 수 있어. 길어야 30분이면 충분할 거야. 모든게 잘되면 말이지.
값의 집합이 칼럼 정의에 들어가 있다는 신호다. 이런 종류의 변경은 서비스 중단 없이 할 수 있어야 한다.
- status 칼럼은 다음 값 중 하나만 가질 수 있어. 이 목록을 바꿀 일이 생기면 안돼.
'하면 안 된다'는 말은 모호하다. '할 수 없다'는 말과는 완전히 다른 것이다.
- 애플리케이션 코드에 있는 목록 값이 데이터베이스에 있는 비즈니스 규칙과 또 틀어졌어.
정보를 서로 다른 두 곳에 유지하면 생기게 마련인 위험이다.
11.4 안티패턴 사용이 합당한 경우
- 이미 논의했듯이 값의 집합이 변하지 않는다면 ENUM을 사용해도 문제가 별로없다.
- LEFT/RIGHT, ACTIVE/INACTIVE,ON/OFF,INTERNAL/EXTERNAL 등 허용되는 값의 집합이 절대 변할 일이 없을 때 ENUM이 적절하다.
- 체크 제약조건은 단순히 ENUM같은 메커니즘을 구현하는 경우 외에도, 시작시각이 종료시각보다 빠른지를 체크하는 것과 같은, 다른 여러방법으로 사용할 수 있다.
11.5 해법: 데이터로 값을 지정하기
칼럼값을 제한하는 것보다 더 좋은 방법이 있다. Bugs.status칼럼에 들어갈 수 있는 각 값을 행으로 하는 색인 테이블을 만드는 것이다. 그리고 Bugs.status가 새로만든 테이블을 참조하도록 FK제약조건을 선언한다.
CREATE TABLE BugStatus(
status VARCHAR(20) PRIMARY KEY
);
INSERT INTO BugStatus (status)
VALUES ('NEW'),('IN PROGRESS'),('FIXED');
CREATE TABLE Bugs (
...
status VARCHAR(20),
FOREGIN KEY (status) REFERENCES BugStatus(status) ON UPDATE CASCADE
);
- Bugs 테이블에 행을 삽입하거나 업데이트할 때, status 칼럼의 값은 BugStatus 테이블에 있는 값을 사용해야한다. 이렇게 하면 ENUM이나 체크 제약조건처럼 status 값을 제한 할 수있다. 이방법은 몇가지 유연성을 제공한다.
값의 집합 쿼리하기
- 값의 집합을 데이터로 얻어 사용자 인터페이스에 제공하기 쉬워진다.
- 사용자가 선택할 값을 정렬해 보여줄 수도 있다.
색인테이블의 값 갱신하기
- 색인테이블을 사용하면 평범한 INSERT문으로 값을 추가할 수 있다. 테이블에 대한 접근을 제한하지 않고도 이런 변경을 할 수 있다.
- 칼럼을 재정의할 필요도, 다운타임 일정을 세울 필요도, ETL작업을 수행할 필요도 없다.
- 색인 테이블에 값을 추가하거나 삭제하기 위해 현재의 값을 알아야 할 필요도 없다.
INSERT INTO BugStatus (status) VALUES ('DUPLICATE');
- FK를 ON UPDATE CASCADE옵션과 함께 선언했다면, 값의 이름도 쉽게 바꿀수 있다.
UPDATE BugStatus SET status = 'INVALID' WHERE status = 'BOGUS';
oracle은 안됨!
더이상 사용하지 않는 값 지원하기
- Bugs에 있는 행이 참조하는 한, 색인 테이블에서 행을 삭제할 수는 없다.
- status 칼럼의 FK가 참조 정합성을 강제하므로, 색인테이블에 값이 존재해야한다.
- 그러나 색인 테이블에 또 다른 속성 칼럼을 추가해 더이상 사용되지 않는 값을 표시할 수 있다.
- 이렇게하면 Bugs.status 칼럼의 과거 데이터를 유지하는 동시에, 사용자 인터페이스에 표시되어야 할 값과 더 이상 사용하지 않는 값을 구분할 수 있게 된다.
ALTER TABLE BugStatus ADD COLUMN active
ENUM('INACTIVE', 'ACTIVE') NOT NULL DEFAULT 'ACTIVE';
- 값을 DELETE하는 대신 더 이상 사용되지 않는다고 표시하기 위해 UPDATE한다.
UPDATE BugStatus SET active = 'INACTIVE' WHERE status = 'DUPLICATE';
- 사용자 인터페이스에 표시할 값의 집합을 얻을 때는 ACTIVE인 값을 조회하면 된다.
SELECT status FROM BugStatus WHERE active = 'ACTIVE';
- ENUM이나 체크제약조건은 값에 별도의 속성을 추가할 수 없기 때문에 이 방법은 ENUM이나 체크 제약조건을 사용하는 방법보다 훨씬 융통성 있다.
포팅이 쉽다.
- ENUM이나 체크 제약조건, 도메인, 사용자 정의 타입과 달리, 색인 테이블을 사용하는 방법은 FK 제약조건을 사용한 참조 정합성이란 표준 SQL 기능만 사용한다.
- 따라서 포팅이 쉬워진다.
- 또한 각 값을 별도의 행으로 저장하기 때문에 색인 테이블에는 사실상 무한한 개수의 값을 저장할 수 있다.
SQL Antipatterns TIP
- 고정된 값의 집합에 대한 유효성 확인을 할 때는 메타데이터를 사용하라.
- 유동적인 값의 집합에 대한 유효성 확인을 할 때는 데이터를 사용하라.
문서에 대하여