PostgreSQL에서 문자열 기반 다대다 관계 정규화하기 — 영화 장르 예제

 

1. 개요

 기존에는 movies 테이블에서 "Action,Comedy,Drama"처럼 장르가 문자열 하나에 묶여 있었는데, 이를 정규화된 구조로 분리하고 genres 테이브로가 movie_genres 중간 테이블을 만드는 실습을 해보았다.

 

2. 문제 상황

-- 예시 데이터 (movies 테이블 일부)
movie_id | title            | genres
---------|------------------|-----------------------
1        | Inception        | Action,Sci-Fi
2        | Parasite         | Thriller,Drama
3        | The Lion King    | Animation,Family,Drama

 

 genres가 이런 식으로 구성되어 있는 경우, 대략 다음과 같은 문제점이 존재한다.

• 중복 데이터가 발생
• 검색//분석이 불편
• JOIN이 불가능

 

3. 정규화

3-1. genres 테이블 생성 및 고유 장르 추출

INSERT INTO genres (name)
SELECT DISTINCT UNNEST(STRING_TO_ARRAY(genres, ',')) 
FROM movies
GROUP BY genres;

 

• STRING_TO_ARRAY(text, delimiter): 문자열을 지정한 기준(delimiter) 기준으로 나누어서 배열로 변환
• UNNEST(array): 배열의 요소를 행(row)으로 펼침
• SELECT DISTINCT: 중복 제거

 이를 통해 "Action,Drama,Sci-Fi"이 경우, Action, Drama, Sci-Fi 같은 고유 장르만 추출할 수 있다.

 

3-2. 다대다 관계 테이블 생성

CREATE TABLE movies_genres (
  movie_id BIGINT NOT NULL,
  genre_id BIGINT NOT NULL,
  PRIMARY KEY (movie_id, genre_id),
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
  FOREIGN KEY (movie_id) REFERENCES movies (movie_id),
  FOREIGN KEY (genre_id) REFERENCES genres (genre_id)
);

 

 영화 하나에 여러 장르가 있을 수 있고 장르 하나도 여러 영화에 적용될 수 있으니 M:N 구조로 연결하는 테이블이 필요하다.

 

3-3. movies_genres에 데이터 삽입

INSERT INTO movies_genres (movie_id, genre_id)
SELECT 
  movies.movie_id,
  genres.genre_id
FROM movies
JOIN genres 
  ON movies.genres LIKE '%' || genres.name || '%';

 

 movies.genres는 문자열 형태(예: "Action,Drama")이기에 genres.name이 해당 문자열에 포함되어 있는지를 LIKE로 비교해서 매핑한다.

 

3-4. 기존 컬럼 제거

ALTER TABLE movies DROP COLUMN genres;

 

 기존 genres 컬럼은 필요 없어졌으니 삭제한다.

 

3-5. 최종 데이터 구조 (예시)

3-5-1. movies 테이블

movie_id	title
1	Inception
2	Parasite

 

3-5-2. genres 테이블

genre_id	name
1	Action
2	Drama
3	Comedy

 

3-5-3. movie_genres (다대다 연결 테이블)

movie_id	genre_id
1	1
1	2
2	2

 

4. 요약 및 느낀 점

• 정규화 과정(순서)
  1. genres 문자열 → 배열 → 고유 장르 추출 및 genres 테이블 생성
  2. movies_genres 중간 테이블 생성
  3. LIKE를 활용한 장르 매핑
  4. 원본 genres 컬럼 제거로 정규화 완료
• 문자열 기반 컬럼은 확장성 및 유지보수 측면에서 좋지 않다고 느꼈다.
• PostgreSQL을 이용하면 정규화가 매우 수월해지는 것을 느꼈다.