HSEOM GeckoHSEOM

흑섬 AI 기술 블로그

AI 엔지니어링 클래스에서 쓴 코드와 실행 결과를 글로 정리합니다. 모든 실습은 구글 코랩에서 직접 실행했고, 에러와 고친 과정까지 남깁니다.

주요 카테고리

Data Viz 카테고리

NumPy, Pandas를 활용한 데이터 분석과 시각화 기술을 일상 예제로 쉽게 설명합니다.

총 17개의 글이 있습니다.

[SQL 실습 #5] 음악 스트리밍 서비스 데이터 모델 작성

N:M 관계를 중간 테이블로 해결합니다. 한 플레이리스트에 여러 곡, 한 곡이 여러 플레이리스트에 담기는 복잡한 관계를 데이터베이스로 구현하는 방법을 배웁니다.

카테고리: Data Viz

작성일: 2026-02-02

예상 읽기 시간: 20분

Data Viz

[SQL 실습 #5] 음악 스트리밍 서비스 데이터 모델 작성

N:M 관계를 중간 테이블로 해결합니다. 한 플레이리스트에 여러 곡, 한 곡이 여러 플레이리스트에 담기는 복잡한 관계를 데이터베이스로 구현하는 방법을 배웁니다.

2026년 2월 2일·20분 읽기

시작 전, 워밍업

스포티파이 플레이리스트, 데이터베이스로는 어떻게 만들까?
"운동할 때 듣는 음악"에 담긴 곡을 "새벽 감성"에도 추가하고 싶다면?
한 곡이 여러 플레이리스트에, 한 플레이리스트에 여러 곡이 담기는 관계.

시작하며 - N:M 관계의 등장

지금까지 학생 정보, 도서관 대출, 주차 관리, 병원 예약 시스템을 만들어봤습니다.
이번이 SQL 시리즈의 마지막입니다!

음악 스트리밍 서비스를 만들어봅니다.

"한 플레이리스트에 여러 곡이 담기고, 한 곡이 여러 플레이리스트에 담긴다."
이런 복잡한 관계를 데이터베이스로 어떻게 표현할까요?

답은 중간 테이블 (Junction Table)입니다.
N:M 관계를 두 개의 1:N 관계로 분해하는 방법을 배워봅시다.


ERD 설계하기

음악 스트리밍 서비스에 필요한 것들:

  • artists (아티스트) - 가수/밴드 정보
  • albums (앨범) - 앨범 정보
  • tracks (트랙) - 곡 정보
  • playlists (플레이리스트) - 재생 목록
  • playlist_tracks (중간 테이블) - 플레이리스트와 곡의 N:M 관계 해결

테이블 구조

1. artists 테이블

  • artist_id (아티스트 ID) - Primary Key
  • name (이름)
  • genre (장르: K-POP, Rock, Jazz 등)

2. albums 테이블

  • album_id (앨범 ID) - Primary Key
  • artist_id (아티스트 ID) - Foreign Key → artists.artist_id
  • title (앨범명)
  • release_date (발매일)

3. tracks 테이블

  • track_id (트랙 ID) - Primary Key
  • album_id (앨범 ID) - Foreign Key → albums.album_id
  • title (곡제목)
  • duration (재생시간: 초 단위)

4. playlists 테이블

  • playlist_id (플레이리스트 ID) - Primary Key
  • name (플레이리스트명)
  • created_at (생성일)

5. playlist_tracks 테이블 (중간 테이블)

  • playlist_id (플레이리스트 ID) - Foreign Key → playlists.playlist_id
  • track_id (트랙 ID) - Foreign Key → tracks.track_id
  • added_at (추가일시)
  • ⚠️ PRIMARY KEY (playlist_id, track_id) - 복합 키!
음악 스트리밍 서비스 ERD

음악 스트리밍 서비스 ERD - 중간 테이블로 N:M 관계 해결

N:M 관계란?
한 곡이 여러 플레이리스트에 담기고,
한 플레이리스트에 여러 곡이 담기는 관계입니다.
이를 중간 테이블 (playlist_tracks)로 해결합니다!


테이블 생성하기

이제 5개의 테이블을 차례대로 만들어봅시다.

1. artists 테이블 생성

CREATE TABLE artists (
    artist_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    genre TEXT
);
CREATE TABLE artists 실행

아티스트 테이블 생성

2. albums 테이블 생성

CREATE TABLE albums (
    album_id INTEGER PRIMARY KEY,
    artist_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    release_date DATE,
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);
CREATE TABLE albums 실행

앨범 테이블 생성

3. tracks 테이블 생성

CREATE TABLE tracks (
    track_id INTEGER PRIMARY KEY,
    album_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    duration INTEGER,
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);
CREATE TABLE tracks 실행

트랙 테이블 생성

4. playlists 테이블 생성

CREATE TABLE playlists (
    playlist_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    created_at DATE DEFAULT CURRENT_DATE
);
CREATE TABLE playlists 실행

플레이리스트 테이블 생성

5. playlist_tracks 테이블 생성 (중간 테이블)

CREATE TABLE playlist_tracks (
    playlist_id INTEGER NOT NULL,
    track_id INTEGER NOT NULL,
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (playlist_id, track_id),
    FOREIGN KEY (playlist_id) REFERENCES playlists(playlist_id),
    FOREIGN KEY (track_id) REFERENCES tracks(track_id)
);

코드 설명:

  • PRIMARY KEY (playlist_id, track_id) - 복합 키로 중복 방지
  • FOREIGN KEY (playlist_id) - playlists 테이블 참조
  • FOREIGN KEY (track_id) - tracks 테이블 참조
  • 한 플레이리스트에 같은 곡이 2번 담기는 것 방지!
CREATE TABLE playlist_tracks 실행

중간 테이블 생성 - N:M 관계 해결


데이터 입력하기

1. artists 데이터 입력

INSERT INTO artists (artist_id, name, genre)
VALUES
    (1, 'BTS', 'K-POP'),
    (2, '아이유', 'K-POP'),
    (3, 'Coldplay', 'Rock');
아티스트 데이터 입력

아티스트 3명 데이터 입력 완료

2. albums 데이터 입력

INSERT INTO albums (album_id, artist_id, title, release_date)
VALUES
    (1, 1, 'MAP OF THE SOUL: 7', '2020-02-21'),
    (2, 2, 'LILAC', '2021-03-25'),
    (3, 3, 'Music of the Spheres', '2021-10-15');
앨범 데이터 입력

앨범 3개 데이터 입력 완료

3. tracks 데이터 입력

INSERT INTO tracks (track_id, album_id, title, duration)
VALUES
    (1, 1, 'ON', 243),
    (2, 1, 'Black Swan', 207),
    (3, 2, 'LILAC', 203),
    (4, 2, 'Coin', 216),
    (5, 3, 'Higher Power', 210),
    (6, 3, 'My Universe', 225);

duration은 초 단위입니다. (예: 243초 = 4분 3초)

트랙 데이터 입력

트랙 6곡 데이터 입력 완료

tracks 테이블 Browse Data

Browse Data로 입력된 트랙 6곡 확인

4. playlists 데이터 입력

INSERT INTO playlists (playlist_id, name, created_at)
VALUES
    (1, '내가 좋아하는 K-POP', '2026-01-01'),
    (2, '운동할 때 듣는 음악', '2026-01-15');
플레이리스트 데이터 입력

플레이리스트 2개 데이터 입력 완료


N:M 관계 - 중간 테이블 활용

이제 정말 중요한 부분입니다!
한 곡이 여러 플레이리스트에 담기는 N:M 관계를 중간 테이블로 구현합니다.

플레이리스트 1에 곡 추가

INSERT INTO playlist_tracks (playlist_id, track_id, added_at)
VALUES
    (1, 1, '2026-01-02 10:00:00'),
    (1, 2, '2026-01-02 10:05:00'),
    (1, 3, '2026-01-03 14:00:00'),
    (1, 4, '2026-01-03 14:10:00');

"내가 좋아하는 K-POP" 플레이리스트에:
ON, Black Swan, LILAC, Coin 총 4곡 추가

플레이리스트 1에 곡 추가

플레이리스트 1에 4곡 추가

플레이리스트 2에 곡 추가

INSERT INTO playlist_tracks (playlist_id, track_id, added_at)
VALUES
    (2, 1, '2026-01-16 08:00:00'),
    (2, 5, '2026-01-16 08:10:00'),
    (2, 6, '2026-01-16 08:20:00');

"운동할 때 듣는 음악" 플레이리스트에:
ON, Higher Power, My Universe 총 3곡 추가

주목! track_id=1 (ON)이 두 플레이리스트에 모두 담겼습니다!
이게 바로 N:M 관계예요.

플레이리스트 2에 곡 추가

플레이리스트 2에 3곡 추가

playlist_tracks 테이블 Browse Data

중간 테이블 확인 - ON(track_id=1)이 두 플레이리스트에 포함


JOIN - N:M 관계 조회

이제 실전 쿼리를 만들어봅시다.
4개 테이블을 한 번에 JOIN하는 복잡한 쿼리입니다!

플레이리스트별 곡 목록 조회

SELECT
    playlists.name AS 플레이리스트명,
    tracks.title AS 곡제목,
    artists.name AS 아티스트,
    tracks.duration AS 재생시간,
    playlist_tracks.added_at AS 추가일시
FROM playlist_tracks
JOIN playlists ON playlist_tracks.playlist_id = playlists.playlist_id
JOIN tracks ON playlist_tracks.track_id = tracks.track_id
JOIN albums ON tracks.album_id = albums.album_id
JOIN artists ON albums.artist_id = artists.artist_id
ORDER BY playlists.playlist_id, playlist_tracks.added_at;

코드 설명:

  • playlist_tracks (중간 테이블)을 중심으로
  • playlists, tracks, albums, artists를 모두 JOIN
  • 플레이리스트별로 어떤 곡이 담겼는지 한눈에 확인!
플레이리스트별 곡 목록 조회

4개 테이블 JOIN - 플레이리스트별 곡 목록 쿼리 실행

곡별 플레이리스트 개수 (GROUP BY)

SELECT
    tracks.title AS 곡제목,
    artists.name AS 아티스트,
    COUNT(*) AS 플레이리스트개수
FROM playlist_tracks
JOIN tracks ON playlist_tracks.track_id = tracks.track_id
JOIN albums ON tracks.album_id = albums.album_id
JOIN artists ON albums.artist_id = artists.artist_id
GROUP BY tracks.track_id, tracks.title, artists.name
ORDER BY 플레이리스트개수 DESC;

"어떤 곡이 가장 많은 플레이리스트에 담겼나?" 알아보는 쿼리입니다.

곡별 플레이리스트 개수 쿼리 실행

GROUP BY 쿼리 결과 - ON이 2개 플레이리스트에 포함됨을 확인

결과를 보면 ON (BTS)이 2개의 플레이리스트에 담겼네요!
나머지 곡들은 1개씩만 담겼고요.


축하합니다! SQL 시리즈 완성!

여러분은 N:M 관계까지 구현할 수 있는 데이터베이스 설계자가 되었습니다! 🎉

배운 내용 정리:

  • ✅ N:M 관계 - 중간 테이블로 해결
  • ✅ 복합 PRIMARY KEY - 두 컬럼 조합으로 중복 방지
  • ✅ 4개 테이블 JOIN - 복잡한 데이터 조회
  • ✅ GROUP BY + COUNT - 데이터 집계 및 순위 분석
  • ✅ 실전 데이터베이스 설계 - 음악 스트리밍 서비스

실생활 응용
N:M 관계는 정말 많은 곳에서 사용됩니다:

  • 쇼핑몰: 주문 ↔ 상품 (한 주문에 여러 상품, 한 상품이 여러 주문에)
  • SNS: 게시물 ↔ 해시태그 (한 게시물에 여러 태그, 한 태그가 여러 게시물에)
  • 학교: 학생 ↔ 수업 (한 학생이 여러 수업, 한 수업에 여러 학생)
  • 영화관: 영화 ↔ 배우 (한 영화에 여러 배우, 한 배우가 여러 영화에)

브리딩 관리라면?
개체(gecko) ↔ 특성(traits) 관계를 중간 테이블로!
"한 개체가 여러 특성을 가지고, 한 특성이 여러 개체에 나타난다"


SQL 시리즈를 마치며

총 6개의 글을 통해 배운 것들:

  1. 데이터베이스 입문 - SQL이란 무엇인가?
  2. 학생 정보 - 기본 테이블 설계와 CREATE TABLE
  3. 도서관 대출 - Foreign Key와 1:N 관계
  4. 주차 관리 - 복잡한 비즈니스 로직
  5. 병원 예약 - UNIQUE 제약으로 충돌 방지
  6. 음악 스트리밍 - N:M 관계와 중간 테이블

이제 여러분은 실전 데이터베이스를 설계하고 구축할 수 있습니다!
SQL 시리즈를 읽어주셔서 감사합니다. 👋

#SQL#N:M 관계#중간 테이블#복합 키#음악 스트리밍