이메일 캠페인 발송 시스템 DB 설계 노트

이메일 캠페인 도구 개발기 시리즈 (3/9). 전체 개요

여러 출처(기존 시스템 고객 데이터 + 신규 엑셀 명단)를 합쳐 이메일을 발송하고, 수신거부와 그룹별 타겟팅까지 지원하는 시스템을 설계하며 겪은 결정들을 정리한다.

요구사항

• 두 개의 서로 다른 원천에서 이메일 주소를 모은다 (기존 시스템 DB + 수기 관리 엑셀)
• 발송 대상을 기업 유형(분류)별로 나눠서 캠페인마다 다르게 타겟팅
• 수신거부는 이메일 주소 기준으로 통합 관리 (어느 원천이든 한 번 거부하면 끝)
• 캠페인은 예약 발송, 첨부파일 지원

1) 원본 데이터와 "발송용 데이터"를 분리했다

엑셀에서 옮겨온 원본 연락처 테이블(eco_contacts)과, 실제 발송 로직이 참조하는 구독자 테이블(subscribers)을 처음부터 분리했다.

CREATE TABLE eco_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),              -- 원본은 중복/공백 허용
    company_type_id INT,
    ...
    is_valid BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE subscribers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,  -- 발송용은 이메일 유일
    eco_contact_id INT NULL,
    company_type_id INT NULL,
    unsubscribe_token VARCHAR(64) NOT NULL UNIQUE,
    is_subscribed BOOLEAN NOT NULL DEFAULT TRUE,
    unsubscribed_at DATETIME NULL
);

이렇게 나눈 이유:

• 원본 데이터(부서 공용 메일, 중복 행, 빈 값 포함)를 있는 그대로 보존해야 나중에 "이 사람이 원래 명부에 있었는지" 추적 가능
• 발송/수신거부 로직은 이메일 유일성이 반드시 보장돼야 하는데, 원본에 그 제약을 걸면 임포트 자체가 실패함
• 두 원천(기존 DB + 엑셀)을 합칠 때도, subscribers라는 통합 계층 하나만 있으면 되고 각 원본 테이블의 구조 차이를 신경 쓸 필요가 없음

2) 중복 이메일 처리: GROUP BY + INSERT IGNORE

원본에 중복 이메일(부서 공용 메일 등)이 있는 상태에서 subscribers로 옮길 때:

INSERT IGNORE INTO subscribers (email, eco_contact_id, company_type_id, unsubscribe_token)
SELECT ec.email, MIN(ec.id), MAX(ec.company_type_id), UUID()
FROM eco_contacts ec
WHERE ec.email IS NOT NULL AND ec.email != '' AND ec.is_valid = TRUE
GROUP BY ec.email;

• GROUP BY email로 같은 이메일을 한 그룹으로 묶어 대표 값만 사용
• 서로 다른 원천(기존 시스템 DB, 엑셀)을 순서대로 넣을 때 INSERT IGNORE를 안전망으로 사용 — UNIQUE 제약에 걸리는 행은 에러 없이 건너뜀
• 두 원천을 합칠 때는 순서를 정해두는 게 좋다 (예: 항상 A 원천 먼저, B 원천 나중) — 그래야 어느 쪽 값이 대표로 남는지 예측 가능

3) 발송 그룹은 "고정 카테고리 하나"로 단순화

처음엔 다대다 태그 구조(구독자가 여러 그룹에 속하고, 캠페인도 여러 그룹을 타겟팅)까지 고려했지만, 실제 발송 기준이 "기업 유형 하나"로 충분하다는 게 확인되면서 단순화했다.

CREATE TABLE company_types (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

기존 시스템 쪽 분류(간단한 3종 구분)와 신규 데이터 쪽 분류(다수의 업종 구분)를 하나의 테이블에 합치되, ID 대역을 나눠서 관리했다 (예: 기존 쪽은 100번대, 신규 쪽은 1번대). 이렇게 하면 두 원천의 분류 체계가 섞여도 충돌 없이 공존한다.

교훈: 설계 초반에 "나중에 필요할 수도 있으니 유연하게"로 다대다 구조까지 만들었다가, 실제 요구사항을 다시 확인하고 단순한 단일 FK로 되돌린 경우다. 유연성은 실제 요구가 확인된 다음에 넣는 게 낫다 — 미리 만든 유연한 구조가 오히려 이해와 유지보수를 어렵게 만들 수 있다.

4) SMTP 인증 계정과 발신 주소는 별개 컬럼으로

회사 메일 시스템이 "개인 계정으로 로그인하되, 그룹 대표 메일 주소로 발신"하는 구조였다. 이걸 반영해 인증 정보와 표시 정보를 분리했다:

CREATE TABLE smtp_settings (
    id INT NOT NULL DEFAULT 1,  -- 항상 1행만 존재하도록 고정
    smtp_host VARCHAR(255) NOT NULL,
    smtp_port INT NOT NULL,
    smtp_username VARCHAR(255) NOT NULL,       -- 로그인용 개인 계정
    smtp_password_encrypted TEXT NOT NULL,
    from_email VARCHAR(255) NOT NULL,          -- 실제 발신 표시 주소 (대표 메일링)
    from_name VARCHAR(100)
);

메일 발송 코드에서도 이 둘을 구분해서 써야 한다:

server.login(smtp_username, password)               # 인증
server.sendmail(from_email, recipient, msg.as_string())  # 봉투 발신자
msg['From'] = f"{from_name} <{from_email}>"          # 헤더에 보이는 발신자

login()에 쓰는 계정과 sendmail()의 발신자가 달라도, 메일 서버가 그 계정에 대표 메일 별칭으로 보낼 권한을 부여했다면 정상 작동한다.

주의: id를 1로 고정해 테이블에 항상 최대 1행만 존재하게 만들었다. 담당자가 한 명일 때는 이 방식이 단순하지만, 담당자별로 다른 발송 계정을 써야 하는 상황이 되면 admin_id 같은 FK를 추가해 계정별 여러 행을 허용하는 구조로 확장해야 한다. 초기엔 단순하게 시작하고, 실제로 필요해지면 그때 확장하는 편이 낫다.

요약

• 원본 데이터와 발송용 데이터는 분리하고, 발송용에만 강한 제약(UNIQUE)을 건다
• 여러 원천을 합칠 때는 GROUP BY + INSERT IGNORE로 중복을 방어적으로 처리한다
• 유연한 구조(다대다 태그 등)는 실제 요구가 확인되기 전엔 과설계일 수 있다 — 단순한 구조로 시작하고 필요할 때 확장한다
• 인증 계정과 표시 정보(발신 주소 등)가 다를 수 있는 시스템은 이 둘을 애초에 별도 컬럼으로 설계한다