by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

8장. 다중 칼럼 속성




8장 다중 컬럼 속성

테이블 내에 일반적으로 자주 관리되는 컬럼만이 아니라 덜 일반적인 많은 속성을 관리해야한다면 컬럼은 얼마나 많아야 충분할 것인가?

8.1 목표: 다중값 속성 저장

무단횡단 안티패턴과 같다. 테이블의 한 속성에 여러 개의 값을 가진다.
예제에서는 태그를 이용하여 서브시스템을 분류할 것이다.
태그는 상호 배타적일 필요가 없기 때문에 여러 태그를 다는 것도 가능하다.

8.2 안티패턴 : 여러 개의 컬럼 생성

원칙은 안다. 하나의 속성에 하나의 값!?
그런데 일반적인 속성에서 쉽게 하던 일이 복잡해진다.

CREATE TABLE Bugs (
  bug_id      SERIAL PRIMARY KEY,
  description VARCHAR(1000),
  tag1        VARCHAR(20),
  tag2        VARCHAR(20),
  tag3        VARCHAR(20)
);
값검색

어느 컬럼안에 원하는 태그문자열이 있는지 알 수 없기 때문에 모두 뒤져야 한다.

SELECT * FROM Bugs
WHERE tag1 = 'performance'
   OR tag2 = 'performance'
   OR tag3 = 'performance';

그리고 여러 개의 태그를 가진 자료를 찾아야 할 수도 있다.

SELECT * FROM Bugs
WHERE (tag1 = 'performance' OR tag2 = 'performance' OR tag3 = 'performance')
  AND (tag1 = 'printing' OR tag2 = 'printing' OR tag3 = 'printing');

연속된 OR를 IN으로 변환하여 다음처럼 바꿀 수는 있다.

SELECT * FROM Bugs
WHERE 'performance' IN (tag1, tag2, tag3)
AND 'printing'    IN (tag1, tag2, tag3);
값 추가와 삭제

3개 컬럼 중에서 조회하는 것도 성능문제가 발생할 수 있는데 변경/삭제 또한 걸린다.

  • 3개 컬럼 중에 어떤 컬럼을 업데이트 해야 하는지 확인이 필요
  • 이렇게 확인하는 과정이 트랜젝션 내에 포함되기 때문에 충돌에 의한 업데이트 또는 원하지 않는 덮어쓰기가 될 수 있음

--컬럼의 값을 초기화 할 때
UPDATE Bugs
SET tag1 = NULLIF(tag1, 'performance'),
    tag2 = NULLIF(tag2, 'performance'),
    tag3 = NULLIF(tag3, 'performance')
WHERE bug_id = 3456;

--컬럼을 추가할 때
UPDATE Bugs
SET tag1 = CASE
      WHEN 'performance' IN (tag2, tag3) THEN tag1
      ELSE COALESCE(tag1, 'performance') END,
    tag2 = CASE
      WHEN 'performance' IN (tag1, tag3) THEN tag2
      ELSE COALESCE(tag2, 'performance') END,
    tag3 = CASE
      WHEN 'performance' IN (tag1, tag2) THEN tag3
      ELSE COALESCE(tag3, 'performance') END
WHERE bug_id = 3456;
유일성보장

여러 컬럼에 동일한 값 입력을 막고 싶지만 예방하기 어렵다.??

값의 수 증가 처리

값의 수가 증가하게 되면 컬럼이 모자라게 된다. 무단횡단과 같은 안티패턴의 부작용이 나타난다.
테이블의 Description을 변경할 수 있다고 해도 다음과 같은 부분을 고려해야 한다.

  • 테이블 전체에 대한 잠금이 설정되어 다른 클라이언트의 접근을 차단하게 된다.
  • DBMS에 따라서 테이블전체를 복사한 후 새로운 컬럼을 추가해서 새로 만들기도 하므로 데이터가 많을수록 많은 작업시간이 걸린다.
  • 다중 컬럼 속성을 가지는 컬럼이 추가된 경우 모든 어플리케이션에서 이를 참조하는 모든 SQL을 수정해야 한다.

8.3 안티패턴 인식 방법

사용자 인터페이스에서 여러 개의 값을 할당 할 수 있지만 최대 개수가 제한되어 있는 속성이 있다면 다중 컬럼속성 안티패턴이 사용되어 있다는 것이다.

  • 태그를 최대 몇 개까지 붙일 수 있도록 지원해야 하지?
    : 다중 값 속성을 위해 테이블에 얼마나 많은 컬럼을 정의해야 할 지 결정하려는 것!?
  • SQL에서 여러 컬럼을 한꺼번에 검색하려면 어떻게 해야 하지?
    : 주어진 값을 여러 칼럼에 걸쳐 검색해야 한다면 이들 값은 실제로 하나의 논리적인 속성으로 저장되어야 함을 의미

8.4 안티패턴 사용이 합당한 경우

속성의 개수가 고정되고 선택의 위치나 순서가 중요할 수 있다. 역할/위치 등 수가 고정적이고 의미와 사용처가 명확히 나누어질 경우

8.5 해법 : 종속 테이블 생성

무단횡단 안티패턴에서 확인한 바와 같이 가장 좋은 해법은 다중 값 속성을 위한 하나의 컬럼을 가지는 종속테이블을 만드는 것으로 여러 개의 컬럼 대신 여러 개의 행으로 만드는 것이다.

CREATE TABLE Tags (
  bug_id       BIGINT UNSIGNED NOT NULL
  tag          VARCHAR(20),
  PRIMARY KEY (bug_id, tag),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);
INSERT INTO Tags (bug_id, tag)
VALUES (1234, 'crash'), (3456, 'printing'), (3456, 'performance');

하나의 내용에 연관된 모든 태그가 한 컬럼안에 있으므로 조회/수정/삭제가 용이해진다.

INSERT INTO Tags (bug_id, tag) VALUES (1234, 'save');
DELETE FROM Tags WHERE bug_id = 1234 AND tag = 'crash';

PK로 중복허용하지 않으며 태그가 무한정 증가하더라도 필요한 만큼의 태크를 적용할 수 있다.

  • 최초작성일 : 2011년 12월 06일
  • 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 인사이트(insight) 에서 출간한 'SQL AntiPatterns : 개발자가 알아야 할 25가지 SQL 함정과 해법'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.