관계와 카디널리티

관계선 하나만 그리면 "한 게시판에 여러 회원"은 보이지만, 최소 몇 명이어야 하는지, 회원에게 게시판이 꼭 있어야 하는지, 가입한 시각과 역할을 어디에 둘지는 보이지 않습니다. 카디널리티는 최대의 수를, 참여도(participation)는 최소의 수를 나타내며, 둘 다 업무의 문장과 예제 데이터로 확인해야 합니다.

이 글의 요구는 이렇습니다. 회원은 게시판에 0개 이상 가입할 수 있고, 게시판은 회원이 한 명 이상 있어야 하며, 가입마다 역할과 상태, joined_at이 다릅니다. 양쪽 모두 여러 개이고 연결 자체가 속성을 가지므로, board_memberships가 독립된 관계 엔티티의 후보입니다.

쉼표로 이은 다중 값

게시판의 회원 id를 문자열 한 칸에 쉼표로 이어 저장해 봅니다. 아래 테이블은 게시판 한 행에 회원 id 목록 '11,42'와 같은 순서의 역할 목록을 각각 문자열로 넣습니다. 마지막 SELECT는 회원 1이 가입한 게시판을 찾으려고 member_ids LIKE '%1%'로 목록 안에 1이 있는지 검색합니다.

DROP TABLE IF EXISTS board_members_csv_bad;
CREATE TABLE board_members_csv_bad (
  board_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  member_ids VARCHAR(1000) NOT NULL,
  roles VARCHAR(1000) NOT NULL,
  PRIMARY KEY (board_id)
);
INSERT INTO board_members_csv_bad VALUES
  (1, 'DB 읽기 모임', '11,42', 'OWNER,MEMBER');

SELECT board_id, name
FROM board_members_csv_bad
WHERE member_ids LIKE '%1%';

board_id | name
1        | DB 읽기 모임

회원 1은 가입하지 않았는데, 문자열 11 안에 1이 들어 있어 게시판이 잘못 나왔습니다. LIKE는 쉼표로 나뉜 값의 단위를 모르므로 관계의 비교가 아닙니다. FIND_IN_SET을 쓰면 이런 거짓 일치는 줄지만, 외래 키와 인덱스를 쓸 수 없고 관계의 속성과 동시 쓰기의 문제도 그대로입니다.

역할을 같은 순서의 다른 문자열에 저장하면 두 목록의 길이가 어긋날 수 있고, 두 사람이 동시에 가입하면 서로의 UPDATE를 덮어씁니다. 관계형 모델은 M:N을 두 테이블 사이의 선으로 직접 저장하지 않고, 두 외래 키를 가진 연결 행으로 풉니다. 역할과 joined_at은 회원이나 게시판이 아니라, 특정한 가입이라는 사실에 종속됩니다.

문자열 목록이 잃어버린 규칙은 네 가지입니다. 한쪽의 행 하나가 다른 쪽의 몇 개와 이어질 수 있는지를 양쪽 방향으로 묻는 최대의 수, 0이 허용되는 쪽과 정확히 하나가 필요한 쪽을 가리는 최소의 수, 두 키의 조합이 없으면 뜻이 없는 관계의 속성, 그리고 이 규칙들을 문자열 검색이 아니라 외래 키와 UNIQUE, 건수로 확인하는 방법입니다.

다대다 관계를 구현한다

boards 1:N board_memberships, members 1:N board_memberships의 두 관계로 바꿉니다. (board_id, member_id)는 같은 게시판에 두 번 가입하지 못하게 하는 후보 키이고, 역할과 상태, joined_at은 가입에 둡니다.

회원이 가입한 게시판이 0개라는 선택적인 참여는, 그 회원의 가입 행이 없다는 것으로 나타납니다. 반면 "게시판에는 최소 한 명"이라는 규칙은 외래 키만으로 보장하기 어려우므로, 게시판을 만드는 일과 소유자의 가입을 한 트랜잭션으로 묶습니다.

Chen과 Crow's Foot 표기 읽기

ERD의 표기법은 같은 관계를 다른 모양으로 그립니다. Chen 표기법은 엔티티를 사각형, 속성을 타원, 관계를 마름모로 나누어 개념을 자세히 설명하기 좋습니다. Crow's Foot 표기법은 엔티티의 상자 안에 주요 속성을 함께 적고, 관계선 끝의 기호로 최소와 최대의 수를 나타내므로 논리 모델과 실무의 설계 도구에서 널리 씁니다.

표기법이 달라도 읽을 질문은 같아서, 관계선의 양 끝에서 각각 "최소 몇 개, 최대 몇 개인가"를 읽습니다.

회원 게시판의 관계를 간단한 문자 표기로 적으면 다음과 같습니다.

members ||--o{ posts             : writes
boards  ||--|{ board_memberships : contains
members ||--o{ board_memberships : joins

첫 줄은 "게시글은 저마다 정확히 회원 한 명에게 속하고, 회원 한 명은 게시글이 없거나 여러 개일 수 있다"는 뜻입니다. 둘째 줄은 "가입은 저마다 정확히 게시판 하나에 속하고, 게시판은 가입을 하나 이상 가져야 한다"는 업무의 규칙이며, 셋째 줄은 가입하지 않은 회원도 있을 수 있다는 뜻입니다. Chen 표기에서는 같은 내용을 회원 — 쓴다 — 게시글이라는 관계와, 양 끝의 1, N, 선택과 필수 참여의 표시로 나타냅니다.

어느 표기법을 쓰든, 용어 사전의 관계 문장과 ERD의 기호, DDL의 외래 키와 NULL, UNIQUE가 같은 규칙을 말해야 합니다. 다만 ERD의 최소의 수가 모두 외래 키 하나로 강제되지는 않습니다. posts.author_id의 NOT NULL과 외래 키는 게시글 쪽의 "회원 정확히 1"을 강제하지만, "게시판은 가입을 최소 하나 가진다"처럼 부모가 자식을 반드시 가져야 하는 규칙은 여러 행을 함께 보아야 하므로, 만드는 트랜잭션과 제한된 권한, 사후 점검 쿼리로 보완합니다.

외래 키는 저장하는 위치일 뿐, 양쪽 방향의 JOIN을 막지 않습니다. 연결 행이 두 부모의 외래 키를 가져 유효한 조합만 저장하게 하고, 최소 하나라는 규칙은 행을 만들고 지우는 트랜잭션과 권한의 규칙으로 지킵니다. 구성원과 소유자, 활성이라는 말의 뜻은 용어 사전에서 하나로 고정합니다.

게시판 가입을 관계 엔티티로

게시판과 가입을 나누고, 조합의 UNIQUE와 상태의 CHECK를 둡니다. 아래 예제는 9장 1편의 concept_members를 쓰고, 관계의 수를 고정하려고 회원 11과 42를 id까지 정해 더합니다. 회원 11과 42가 이미 있으면 첫 INSERT가 1062로 실패합니다. 다시 실행할 때는 블록 맨 앞의 세 DROP 문장을 실행한 뒤 DELETE FROM concept_members WHERE member_id IN (11, 42);로 두 회원만 지우고, 회원 INSERT부터 이어서 실행합니다. 9장 1편의 블록은 9장 2편의 concept_post_attachments가 concept_posts를 참조하고 있어 첫 DROP에서 오류로 멈춥니다. 게시판을 만든 뒤 가입 행을 넣는 두 DML은, 빈 게시판이 확정되지 않도록 실제로는 한 트랜잭션으로 묶고 실패하면 함께 롤백합니다.

DROP TABLE IF EXISTS concept_board_invitations;
DROP TABLE IF EXISTS concept_board_memberships;
DROP TABLE IF EXISTS concept_boards;

INSERT INTO concept_members (member_id, email, display_name)
VALUES
  (11, '[email protected]', '게시판 소유자'),
  (42, '[email protected]', '게시판 회원');

CREATE TABLE concept_boards (
  board_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  created_by_member_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (board_id),
  FOREIGN KEY (created_by_member_id) REFERENCES concept_members (member_id)
);
CREATE TABLE concept_board_memberships (
  membership_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  board_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  role VARCHAR(16) NOT NULL,
  status VARCHAR(16) NOT NULL,
  joined_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (membership_id),
  UNIQUE (board_id, member_id),
  FOREIGN KEY (board_id) REFERENCES concept_boards (board_id),
  FOREIGN KEY (member_id) REFERENCES concept_members (member_id),
  CHECK (role IN ('OWNER', 'MEMBER')),
  CHECK (status IN ('ACTIVE', 'LEFT'))
);

INSERT INTO concept_boards (name, created_by_member_id)
VALUES ('데이터베이스 게시판', 11);

SET @concept_board_id := LAST_INSERT_ID();

INSERT INTO concept_board_memberships
  (board_id, member_id, role, status, joined_at)
VALUES
  (@concept_board_id, 11, 'OWNER', 'ACTIVE', '2026-07-14 12:00:00'),
  (@concept_board_id, 42, 'MEMBER', 'ACTIVE', '2026-07-14 12:10:00');

SELECT member_id, role, status
FROM concept_board_memberships
ORDER BY member_id;

member_id | role   | status
11        | OWNER  | ACTIVE
42        | MEMBER | ACTIVE

이제 회원 1을 찾는 조건은 member_id = 1이라는 동등 비교여서 정확히 0행을 돌려주고, 가입마다 역할과 상태가 같은 행에 저장됩니다. 같은 조합의 중복 가입과 없는 회원의 가입은 제약이 거부합니다.

INSERT INTO concept_board_memberships
  (board_id, member_id, role, status)
VALUES (@concept_board_id, 11, 'MEMBER', 'ACTIVE');

INSERT INTO concept_board_memberships
  (board_id, member_id, role, status)
VALUES (@concept_board_id, 999999, 'MEMBER', 'ACTIVE');

ERROR 1062 (23000): Duplicate entry '1-11' for key 'concept_board_memberships.board_id'
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`board_lab`.`concept_board_memberships`, CONSTRAINT `concept_board_memberships_ibfk_2` ...)

이름을 붙이지 않은 UNIQUE는 첫 열의 이름인 board_id로, 이름을 붙이지 않은 외래 키는 테이블_ibfk_번호로 자동으로 이름이 붙습니다. 이런 이름은 오류 로그에서 어떤 규칙인지 알기 어려우므로, 최종 DDL에서는 제약마다 업무의 이름을 붙입니다.

created_by_member_id는 만들 때의 감사 속성이고, 지금의 소유자는 가입 행의 역할입니다. 만든 사람과 지금의 소유자가 언제나 같아야 하는지는 따로 정할 규칙이며, 같은 사실을 두 곳에 두면 서로 어긋날 위험이 있습니다. 지금의 DDL이 무엇을 보장하는지는 다음 도표와 같고, 여러 가입 행을 함께 보는 규칙은 만들기와 이전, 탈퇴의 트랜잭션에서 지켜야 합니다.

관계의 수와 참여를 확인한다

게시판별 활성 회원의 수와 회원별 가입한 게시판의 수를 각각 집계합니다. 지금의 스키마에는 게시판 자체의 활성 상태 열이 없으므로, 아래 쿼리는 모든 게시판을 검사하며 가입 행의 ACTIVE와 구별합니다.

SELECT g.board_id, g.name,
       COUNT(CASE WHEN e.status = 'ACTIVE' THEN 1 END) AS active_members,
       COUNT(CASE WHEN e.status = 'ACTIVE' AND e.role = 'OWNER' THEN 1 END) AS owners
FROM concept_boards AS g
LEFT JOIN concept_board_memberships AS e ON e.board_id = g.board_id
GROUP BY g.board_id, g.name;

SELECT member_id, COUNT(*) AS active_boards
FROM concept_board_memberships
WHERE status = 'ACTIVE'
GROUP BY member_id
ORDER BY member_id;

board_id | name                | active_members | owners
1        | 데이터베이스 게시판 | 2              | 1

member_id | active_boards
11        | 1
42        | 1

정상인 게시판은 active_members가 1 이상이고 owners가 정확히 1입니다. 가입 조회가 0행인 회원 1은 게시판이 0개라는 선택적인 참여를 만족합니다.

DDL을 통과했다는 것과 업무의 규칙을 지켰다는 것은 따로 판정합니다. 회원 11만 LEFT로 바꾸면 활성 회원은 1명이고 활성 OWNER는 0명인데, 상태의 CHECK와 두 외래 키, 조합의 UNIQUE는 이 변경을 거부하지 않으므로 "소유자 한 명"의 규칙만 깨집니다. 반대로 회원 1을 새 OWNER로 ACTIVE하게 더하면 기존 두 행과 조합이 다르므로 UNIQUE를 통과하면서 활성 OWNER가 두 명이 됩니다. OWNER라는 값을 허용하는 CHECK와, OWNER 행의 수를 정확히 하나로 만드는 규칙은 다릅니다.

소유자를 넘길 때는 예전 소유자와 새 소유자의 역할과 상태를 함께 바꾸고, 같은 게시판에 대한 다른 이전이 끼어들지 않도록 잠금의 범위와 권한을 정합니다(8장 3편). 위의 진단 쿼리는 잘못된 결과를 찾는 도구일 뿐, 쓰기 전에 규칙을 자동으로 강제하는 장치가 아닙니다.

카디널리티는 지금 데이터의 최댓값이 아니라 허용하는 규칙입니다. 지금 회원이 한 명뿐이어도 요구가 여러 명을 허용하면 1:N이고, 반대로 지금 중복된 데이터가 있다고 M:N이 정답인 것도 아닙니다. 용어 사전에는 게시판의 구성원과 회원, 소유자, 탈퇴 상태의 정의와 같은 뜻의 다른 말을 정해, 열과 API가 서로 다른 말을 쓰지 않게 합니다.

게시판을 지울 때 가입까지 연쇄로 지우면 가입의 이력이 사라지므로, 보존의 규칙과 내보내기, 감사의 요구를 정한 뒤 RESTRICT나 소프트 삭제를 고릅니다. 활성 회원의 수를 게시판에 캐시로 둔다면, 가입이 바뀔 때 트랜잭션으로 함께 맞추고 원래의 건수와 일치하는지 확인하는 작업을 둡니다.

탈퇴한 뒤의 재가입을 같은 조합 행의 UPDATE로 볼지 새 가입이라는 사건으로 볼지는 이력의 요구에 따라 키가 달라집니다. 초대 상태가 있다면 가입 전의 관계와 실제로 포함되었는지를 같은 상태 기계에 둘지 나눌지 정하고, 소유자가 탈퇴하기 전에는 새 소유자를 정하는 일과 탈퇴를 한 트랜잭션으로 묶습니다. 테넌트가 있는 시스템이라면 게시판과 회원이 같은 테넌트인지, 단일 외래 키 두 개만으로 충분한지 검토합니다.

Crow's Foot의 기호를 그린 뒤 끝내지 말고, 허용되는 예제 데이터와 위반하는 예제 데이터를 함께 만듭니다. 데이터베이스의 제약이 직접 강제하지 못하는 최소 참여는, 서비스의 트랜잭션과 진단 쿼리로 운영합니다.

정리

• 카디널리티는 양쪽 방향의 최대의 수를, 참여도는 최소의 수를 나타냅니다.
• M:N은 두 외래 키를 가진 관계 엔티티로 풀고, 쉼표로 이은 문자열에 저장하지 않습니다.
• 관계에 종속된 역할과 상태, 시각은 연결 행의 속성입니다.
• 부모가 자식을 반드시 가져야 하는 규칙처럼 여러 행을 보는 규칙은 트랜잭션과 진단 쿼리로 지킵니다.

연습 문제

1. 게시판 초대

회원은 여러 게시판에 초대될 수 있고, 초대는 초대한 사람과 만료 시각, ACCEPTED나 DECLINED 같은 상태를 가집니다. 가입 여부와 초대를 한 테이블에 둘지 나눌지 판단해 보세요.

CREATE TABLE concept_board_invitations (
  invitation_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  board_id BIGINT UNSIGNED NOT NULL,
  invitee_member_id BIGINT UNSIGNED NOT NULL,
  invited_by_member_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL,
  expires_at DATETIME(6) NOT NULL,
  PRIMARY KEY (invitation_id),
  UNIQUE (board_id, invitee_member_id, status),
  FOREIGN KEY (board_id) REFERENCES concept_boards (board_id),
  CHECK (status IN ('PENDING', 'ACCEPTED', 'DECLINED', 'EXPIRED'))
);

2. 소유자 두 명이 생기는 경우

본문의 게시판에서 회원 1을 OWNER로 ACTIVE하게 더하는 INSERT는 어떤 제약에도 걸리지 않습니다. 소유자가 정확히 한 명이라는 규칙을 데이터베이스가 직접 지키게 하려면 어떤 방법이 있나요?

다음 글 ERD와 데이터 모델 검증에서는 하나의 실제 요구 사항을 처음부터 ERD와 용어 사전, DDL, 검증 쿼리까지 완성합니다.