정규화를 위한 테이블 간 관계 모델링

오늘은 진짜 중요한 주제에 대해 좀 더 깊게 들어가 볼 거야: 테이블 간의 관계 모델링. 정규화라는 게 단순히 원자성 데이터랑 중복 제거만이 아니라, 테이블 간에 제대로 된 관계를 만드는 것도 포함된다는 거 알지?

데이터베이스가 정보를 저장하는 잘 정리된 시스템이라면, 테이블 간의 관계는 데이터들이 서로 어떻게 연결되는지 보여주는 논리적인 다리 같은 거야. 도서관을 생각해봐. 책 정보는 따로, 저자 정보도 따로 저장하지만, 각 책이 "자기" 저자를 외래키 같은 특별한 관계로 알고 있지. 아니면 인터넷 쇼핑몰을 예로 들어보자: 상품 데이터는 고객 정보랑 별개로 존재하지만, 누군가 주문을 하면 시스템이 주문 테이블을 통해 특정 고객과 특정 상품을 연결해줘.

병원에서는 환자가 자기 진료카드랑 연결되어 있고, 의사는 진료 스케줄이랑, 약은 처방전이랑 연결돼. 이런 관계들이 시스템이 어떤 정보가 어디에 속하는지 이해하게 해주고, 불필요하게 데이터를 중복 저장하지 않게 도와주는 거지.

이런 관계의 기본적인 타입들은 현실 세계의 관계랑 비슷해: 여권은 한 사람만 가질 수 있어(일대일), 한 명의 강사가 여러 과목을 가르칠 수 있어(일대다), 학생들은 여러 과목을 들을 수 있고, 각 과목마다 여러 학생이 있을 수 있지(다대다).

일대일 (1:1)

이건 테이블 "A"의 한 레코드가 테이블 "B"의 딱 한 레코드랑만 연결되는 관계야. 예를 들어, "직원" 테이블이랑 "여권 정보" 테이블이 있다고 해보자. 한 명의 직원은 여권을 하나만 가질 수 있고, 각 여권은 오직 한 명의 직원에게만 속해.

예시:

직원

여권 정보

여기서 직원_id라는 외래키가 "직원" 테이블의 id를 가리키고 있어.

일대다 (1:N)

이게 제일 많이 쓰이는 관계야. 여기서는 "A" 테이블의 한 레코드가 "B" 테이블의 여러 레코드랑 연결될 수 있지만, "B" 테이블의 각 레코드는 "A" 테이블의 한 레코드랑만 연결돼. 예를 들어, "강사" 테이블이랑 "과목" 테이블이 있다고 해보자. 한 명의 강사가 여러 과목을 맡을 수 있지.

예시:

강사

과목

이 관계는 "과목" 테이블의 강사_id 외래키로 만들어져.

다대다 (M:N)

뭔가 많아지면 재밌긴 한데, 좀 복잡해지지. 여기서는 "A" 테이블의 한 레코드가 "B" 테이블의 여러 레코드랑 연결될 수 있고, 반대로도 마찬가지야. 예를 들어, 학생들은 여러 과목을 들을 수 있고, 각 과목마다 여러 학생이 있을 수 있어.

예시:

학생

과목

이런 관계를 만들려면 학생과 과목을 연결해주는 중간 테이블이 필요해:

수강신청

외래키로 관계 모델링하기

외래키는 다른 테이블의 기본키를 가리키는 컬럼(혹은 컬럼들의 집합)이야. 이게 테이블 간 관계를 만드는 기본이야.

외래키 예시:

CREATE TABLE 과목 (
    id SERIAL PRIMARY KEY,
    과목명 VARCHAR(255)
);

CREATE TABLE 수강신청 (
    id SERIAL PRIMARY KEY,
    학생_id INT,
    과목_id INT,
    FOREIGN KEY (과목_id) REFERENCES 과목(id)
);

외래키 설계할 때 실수 안 하려면? 일단 외래키랑 기본키 컬럼의 데이터 타입이 꼭 맞아야 해 — 아니면 DB가 아예 관계를 못 만들게 막아버려. 그리고 삭제할 때 어떻게 할지 미리 생각해두는 게 좋아. 예를 들어, 부모 테이블에서 행을 삭제하면 자식 테이블의 데이터는 어떻게 할 거야? 많이 쓰는 방법 중 하나가 ON DELETE CASCADE 옵션을 쓰는 거야. 이러면 관련된 데이터가 자동으로 같이 삭제돼서, 지저분한 "고아" 레코드가 안 남아. 질서 유지에 딱이지.

"다대다" 관계 구현하기

예를 들어보자: 학생이랑 과목이 있어. 한 학생이 여러 과목을 들을 수 있고, 한 과목에 여러 학생이 있을 수 있지. 다대다(M:N) 관계를 만들려면 학생, 과목, 수강신청 이렇게 세 개의 테이블을 만들어야 해.

CREATE TABLE 학생 (
    id SERIAL PRIMARY KEY,
    이름 VARCHAR(255)
);

CREATE TABLE 과목 (
    id SERIAL PRIMARY KEY,
    과목명 VARCHAR(255)
);

CREATE TABLE 수강신청 (
    id SERIAL PRIMARY KEY,
    학생_id INT,
    과목_id INT,
    FOREIGN KEY (학생_id) REFERENCES 학생(id),
    FOREIGN KEY (과목_id) REFERENCES 과목(id)
);

이제 수강신청 테이블에 데이터를 넣어서 학생과 과목을 연결할 수 있어.

실습 과제

강의 관리 시스템용 데이터베이스 구조를 만들어봐. 학생, 과목, 수강신청 테이블이 있어야 하고, 테이블 간의 모든 관계를 구현해야 해. 그리고 학생, 과목, 수강신청에 대한 예시 데이터도 넣어보자. 어떻게 하는지 같이 보자.

1. 테이블 만들기:

CREATE TABLE 학생 (
    id SERIAL PRIMARY KEY,
    이름 VARCHAR(255)
);

CREATE TABLE 과목 (
    id SERIAL PRIMARY KEY,
    과목명 VARCHAR(255)
);

CREATE TABLE 수강신청 (
    id SERIAL PRIMARY KEY,
    학생_id INT,
    과목_id INT,
    FOREIGN KEY (학생_id) REFERENCES 학생(id),
    FOREIGN KEY (과목_id) REFERENCES 과목(id)
);

2. 데이터 넣기:

INSERT INTO 학생 (이름) VALUES ('오토 린'), ('마리아 치');
INSERT INTO 과목 (과목명) VALUES ('SQL 기초'), ('DB 관리');
INSERT INTO 수강신청 (학생_id, 과목_id) VALUES (1, 1), (1, 2), (2, 1);

3. 데이터 확인하기:

SELECT
    학생.이름 AS 학생, 
    과목.과목명 AS 과목
FROM 수강신청
JOIN 학생 ON 수강신청.학생_id = 학생.id
JOIN 과목 ON 수강신청.과목_id = 과목.id;

결과:

관계 모델링의 어려움과 특징

테이블 간의 관계를 모델링하다 보면 이런 어려움이 생길 수 있어:

• 데이터 삭제할 때 오류(예: 부모 테이블의 레코드에 의존하는 자식 테이블의 데이터가 남아있는 경우).
• 데이터가 많아질수록 쿼리 성능 저하. 특히 M:N 관계는 조인이 많아져서 성능을 많이 잡아먹어.

이런 문제는 이렇게 해결할 수 있어:

• 외래키에 인덱스 걸기
• 잘 설계된 데이터베이스 구조
• 정규화와 성능 사이의 균형

우리는 테이블 간 관계 모델링을 아주 기초적인 수준에서 다뤄봤고, 강의 관리 시스템용 데이터베이스 구조도 직접 만들어봤어. 사실 더 큰 예시를 다뤄보고 싶긴 한데, 아직은 어떻게 해야 재밌게 설명할지 모르겠더라. 큰 예시는 너무 복잡하고 지루해질 수 있거든. 그래서 일단 여기까지 하고, 나중에 강의 끝날 때쯤 다시 얘기해볼게.