개발 학습 실습실

이 사이트의 실습은 자바스크립트를 켜야 움직입니다. 아래는 같은 내용을 글로 적은 것입니다.

공부 › DB › 관계형 모델과 설계

ER 모델링

개체 사이의 관계를 테이블로 어떻게 옮기나

회원과 글(일대다), 글과 태그(다대다), 회원과 프로필(일대일)이라는 세 관계를 테이블로 옮깁니다. 관계마다 흔히 쓰는 설계와, 그럴듯해 보이지만 어긋나는 설계를 나란히 두었습니다. 양쪽에 같은 일(연결된 것 찾기, 하나 더 연결하기)을 시켜 보면 조회 결과가 달라집니다.

실행으로 대조 · MySQL 8.4.11에서 열네 가지 조합의 SQL을 그대로 실행해 쓰기 문장의 결과, 조회 결과의 모든 행, 끝난 뒤 테이블의 모든 행을 대조

이 실습에 나오는 개념

ER 모델(개체, 속성, 관계)
저장할 데이터를 개체(Entity), 속성(Attribute), 관계(Relationship)로 정리해 그림으로 나타내는 설계 방법이다. 개체는 회원이나 글처럼 따로 구별해서 저장할 대상이고, 속성은 이름이나 제목처럼 개체가 가진 값이며, 관계는 "회원이 글을 쓴다"처럼 개체 사이의 연결이다. 테이블을 만들기 전에 무엇을 저장하고 무엇끼리 이어지는지를 먼저 정하는 단계에서 쓴다. 테이블로 옮길 때 개체는 테이블이, 속성은 컬럼이, 관계는 외래 키나 연결 테이블이 된다. 이 그림을 ER 다이어그램(ERD)이라고 부른다.
관계의 종류(일대일, 일대다, 다대다)
두 개체가 서로 몇 개씩 이어질 수 있는지에 따라 관계를 나눈 것이다. 일대다는 회원 한 명이 글을 여러 개 쓰는 것처럼 한쪽 하나에 다른 쪽 여러 개가 이어지는 관계로, "다" 쪽 테이블에 외래 키를 둔다. 다대다는 글 하나에 태그가 여러 개 붙고 태그 하나가 여러 글에 붙는 것처럼 양쪽 모두 여러 개가 이어지는 관계로, 가운데에 연결 테이블을 둔다. 일대일은 양쪽 모두 하나씩만 이어지는 관계로, 외래 키에 유니크 제약을 더해 옮긴다. 한 칸에는 값이 하나만 들어간다는 점이 이 규칙들의 이유이다.
연결 테이블
다대다 관계를 테이블로 옮길 때 두 테이블 사이에 두는 테이블이다. 양쪽 테이블의 기본 키를 가리키는 외래 키 두 개를 가지며, 행 하나가 "이 글에 이 태그가 붙어 있다" 같은 연결 하나를 뜻한다. 보통 두 외래 키를 묶어 기본 키로 삼아 같은 연결이 두 번 들어가지 않게 한다. 연결이 늘어도 행만 늘어날 뿐 테이블의 모양은 바뀌지 않는다. 연결한 날짜나 순서처럼 연결 자체에 딸린 값이 생기면 이 테이블에 컬럼으로 추가한다. 교차 테이블, 매핑 테이블이라고도 부른다.
기본 키, 유니크 키, 외래 키
기본 키(PK)는 테이블의 각 행을 유일하게 식별하는 컬럼으로 값이 중복되거나 비어 있을 수 없고 테이블에 하나만 둔다. 유니크 키는 값의 중복을 막는 제약으로 여러 개 둘 수 있다. 외래 키(FK)는 다른 테이블의 기본 키(또는 유니크 키)를 참조하는 컬럼으로, 참조 대상이 실제로 존재하도록 보장한다.

이렇게 바꿔 보세요

처음 설정으로 실행하면

  1. CREATE TABLE, INSERT: 글과 태그의 다대다 관계를 연결 테이블 없이, post 테이블의 tags 컬럼에 태그 이름을 쉼표로 이어 붙여 저장했습니다. DB가 보기에 tags는 글자들이 든 칸 하나일 뿐이고, 그 안에 태그가 몇 개 들어 있는지는 알지 못합니다.
  2. 글 1의 tags 'java,spring,jpa' LIKE '%java%': 문자열 안에 java라는 글자가 있으므로 조건에 맞습니다. 이 글에는 실제로 java 태그가 붙어 있습니다.
  3. 글 2의 tags 'java' LIKE '%java%': 문자열 안에 java라는 글자가 있으므로 조건에 맞습니다. 이 글에는 실제로 java 태그가 붙어 있습니다.
  4. 글 3의 tags 'javascript' LIKE '%java%': 문자열 안에 java라는 글자가 있으므로 조건에 맞습니다. 하지만 이 글의 태그는 javascript입니다. LIKE는 글자가 들어 있는지만 보고, 쉼표로 나눈 태그 하나와 같은지는 보지 않습니다.
  5. SELECT id, title FROM post WHERE tags LIKE '%java%' ORDER BY id: 조건에 맞은 글은 3행입니다. 나와야 하는 것은 글 1, 2인데 결과가 다릅니다. java 태그가 붙지 않은 글 3이 섞여 나왔습니다.

실행 결과

코드

-- 테이블
CREATE TABLE post (
  id    INT PRIMARY KEY,
  title VARCHAR(50) NOT NULL,
  tags  VARCHAR(100)
);

-- 처음 데이터
INSERT INTO post (id, title, tags) VALUES
  (1, '스프링 입문', 'java,spring,jpa'),
  (2, '자바 기초', 'java'),
  (3, '화면 만들기', 'javascript');

-- 연결된 것 찾기
SELECT id, title
  FROM post
 WHERE tags LIKE '%java%'
 ORDER BY id;

이 모형의 전제

회원 3명, 글 3개, 태그 5개의 작은 예입니다. 열네 가지 조합(설계 일곱 가지 × 시켜 볼 일 두 가지) 모두, 화면의 SQL을 실제 MySQL 8.4.11(InnoDB)에서 그대로 실행해 쓰기 문장의 오류 번호와 바뀐 행 수, 조회 결과의 모든 행, 끝난 뒤 각 테이블의 모든 행이 모형과 같은 것을 대조했습니다. "나와야 하는 것"은 이 예에서 정한 사실(김철수가 글 1과 2를 썼다, java 태그는 글 1과 2에 붙어 있다, 회원의 프로필은 하나다)이며 DB가 알려 주는 값이 아닙니다. 요약에 적은 다음 내용도 같은 MySQL에서 따로 실행해 확인했습니다. 쉼표 문자열에 FIND_IN_SET을 쓰면 글 1과 2만 나오는 것, tags에 인덱스를 만들고 행을 2,000개 넣어도 FIND_IN_SET 조건의 실행 계획은 인덱스로 찾지 않고 모든 행을 읽는 것(type이 ALL), 쉼표 문자열에는 이미 있는 태그를 또 붙여도 그대로 들어가는 것, 연결 테이블에서는 같은 연결을 한 번 더 넣으면 오류 1062로, 없는 태그를 연결하면 오류 1452로 거부되는 것입니다. LIKE가 javascript까지 찾는 것은 어느 DB에서나 같지만 FIND_IN_SET은 MySQL의 함수입니다. 오류 번호와 메시지도 MySQL의 것입니다. 일대다의 외래 키를 "일" 쪽에 두는 설계는 잘못을 보이려고 만든 예입니다. 일대일에서 외래 키를 어느 테이블에 둘지, 테이블을 합칠지는 조회 방식에 따라 정하는 설계 판단이며 이 실습은 그 비교까지는 다루지 않습니다. 정규화와 이상 현상은 "정규화" 실습에서 다룹니다.

앞 실습: 테이블, 키, 제약 조건 · 다음 실습: 정규화

이 실습에 나오는 개념

이 실습에서 보는 것

설정

코드


      
    


    

      
그림

      

      

    


    

      

      

      

    


  


  

    

      
실행 비교

      

      

    

    

      
이렇게 바꿔 보세요