[DB 안티패턴] 다중 칼럼 속성

[문제코드]

 

어떤 사람에 대한 정보를 DB 에 저장할 때 취미가 여러 개라면 아래와 같이 여러 칼럼에 나누어 저장하는 안티패턴을 생각할 수 있다.

 

CREATE TABLE Person (
	person_number INT PRIMARY KEY,
    name VARCHAR(10),
    hobby1 VARCHAR(20),
    hobby2 VARCHAR(20),
    hobby3 VARCHAR(20),
)

 

해당 구조는 아래와 같이 여러 단점을 가지고 있다.

 

1 . 검색 문제

원하는 정보가 어느 칼럼에 있는지 모르기 때문에 모든 필드들을 확인해야 한다.

hobby1, hobby2, hobby3 이 모두 null 로 초기화되어 있는 상태에서 하나씩 hobby 를 추가하기 때문에 찾으려고 하는 값이 어느 위치에 있는지 확인하기 어렵다.

 

SELECT * 
FROM Person
WHERE hobby1 = 'soccer'
   OR hobby2 = 'soccer'
   OR hobby3 = 'soccer';

 

취미가 축구인 사람을 찾기 위해 모든 다중 속성 칼럼들에 대해 찾는 것을 볼 수 있다.

 

2 . 수정 문제

마찬가지로 여러 칼럼 중 어떤 칼럼을 수정해야 할지 먼저 검색을 해야한다는 것이 단점이다. 그런데 이마저도 동시성 문제가 존재하여 둘 중 하나는 충돌로 인해 업데이트에 실패하거나 변경 내용을 덮어쓸 수 있다. 그래서 아래와 같이 NULLIF 함수를 통해 칼럼 값이 특정 값과 같으면 NULL 로 만드는 작업을 해야해서 번거롭다.

 

먼저 삭제 코드는 다음과 같다.

 

UPDATE Person 
SET hobby1 = NULLIF(hobby1, 'soccer'),
    hobby2 = NULLIF(hobby2, 'soccer'),
    hobby3 = NULLIF(hobby3, 'soccer')
WHERE person_number = '~';

 

만약에 첫 번째 NULL 인 칼럼에 추가하는 작업을 하는 코드를 만들어 본다고 생각해보자. 각 칼럼마다 NULL 이 아니면 아무런 변경도 가하지 않고 새 태그 값은 기록하지 않는 과정을 해야할 것이다.

 

3 . 일관성 문제

일관성도 문제다. 여러 칼럼에 중복되는 값이 입력될 수 있다.

 

4 . 확장 문제

취미가 하나 더 필요하다면, 해당 테이블 칼럼을 확장해야 하는데 이 때 테이블 전체를 잠금 설정하고 모든 클라이언트의 접근을 차단하는 과정이 필요하게 된다. 예전 구조의 데이터들을 마이그레이션해야하고, 그 양도 많다면 작업 시간이 많이 걸릴 수 있다. 또한 해당 테이블을 사용하는 모든 애플리케이션의 SQL 구문을 변경해야 한다.

 

[해결방법]

다중 속성 칼럼들을 종속 테이블로 변환 생성해서 사용하면 된다.

 

CREATE TABLE person (
  person_number INT PRIMARY KEY,
  name VARCHAR(10),
);

CREATE TABLE Hobby (
  person_number INT FOREIGN KEY REFERENCES person(person_number)
  hobby_name VARCHAR(10)
);

 

모든 문제를 해결할 수 있다.

1 . 검색 해결

 

SELECT * 
FROM Person JOIN Hobby USING (hobby_id)
WHERE hobby_name = 'soccer';

 

두 개의 취미를 가진 사람도 손쉽게 찾을 수 있다.

 

SELECT * FROM Person
	JOIN Hobby AS h1 USING (hobby_id)
	JOIN Hobby AS h2 USING (hobby_id)
WHERE h1.hobby_name = 'soccer' AND h2.hobby_name = 'sing';

 

2 . 수정 해결

수정 문제도 쉽게 해결 할 수 있다. 단순히 종속 테이블에 행을 추가하거나 삭제하면 된다.

 

--- 수정
INSERT INTO Hobby (member_id, hobby_name) VALUES (1, 'soccer');

--- 삭제
DELETE FROM Hobby WHERE member_id = 1 AND hobby_name = 'soccer';