대여 관계와 키
65분 안팎
학습 목표
도서 제목과 실물 도서·회원·대여 이력의 관계를 구분합니다.
개념
왜 파일 다음에 관계를 설계하나요
앞 모듈에서 도서 ID·제목·대여 여부를 파일로 보존하고 GET·POST API로 공개했습니다. 담당자가 “누가 어느 책을 언제 빌렸고 언제 돌려줬나요”라고 물으면 boolean 하나로 답할 수 없습니다. 도서 객체에 마지막 회원명만 붙이면 반납 후 이전 대여가 사라지고 같은 사람이 다시 빌린 사건도 구별하기 어렵습니다. 지금 필요한 것은 저장 기술 교체보다 보존할 사실을 정하는 일입니다.
이번 레슨은 관계도와 데이터 사전을 제출하는 설계 실습입니다. SQLite 따라하기는 작은 사실을 확인하는 도구이고, 미션에서는 같은 관계를 H2 SQL로 표현합니다. HTTP의 저장소는 아직 FileRepository입니다. 관계도를 그렸다고 API가 DB에 저장된다고 주장하지 않습니다. 다음 모듈에서 저장소 구현을 교체하기 전에 ID와 제목을 보존할 기준을 만드는 단계입니다.
도서 제목과 실물 한 권을 구별합니다
books의 한 행은 동아리 소유 실물 한 권입니다. Java라는 제목의 책 두 권이 있으면 ID 10과 11로 두 행을 만듭니다. 제목이 같다고 한 행으로 합치면 한 권이 대여 중이고 한 권은 서가에 있는 상태를 표현하지 못합니다. ISBN도 출판물 식별에 가까워 이 실습의 실물 식별자로 사용하지 않습니다. 파일 키를 books.id로 이어받습니다.
회원도 이름으로 식별하지 않습니다. 민지라는 회원이 둘일 수 있고 이름이 바뀔 수도 있으므로 members.id를 둡니다. email은 이 실습에서 중복 가입을 막는 후보키로 정하고 유일성 제약을 둡니다. 이름과 제목의 중복은 정상 사례이고 ID와 email의 중복은 오류입니다. 어떤 값이 유일해야 하는지는 자료형 이름보다 업무 의미에서 출발합니다.
대여를 사건으로 독립시킵니다
loans의 한 행은 회원 한 명이 실물 한 권을 빌린 사건입니다. 자체 ID, book_id, member_id, loaned_at, returned_at을 갖습니다. 회원 1명이 대여 이력 0건부터 여러 건을 가질 수 있고 도서 1권도 시간에 따라 여러 건을 가질 수 있습니다. 대여 행 하나에서 도서와 회원은 각각 정확히 하나여야 합니다. 따라서 자식의 두 FK에 NOT NULL을 함께 둡니다.
회원과 도서는 이력을 통해 여러 대 여러 관계를 맺습니다. 이를 loans라는 연결 테이블의 두 개의 1:N 관계로 풀어냅니다. 한 회원이 같은 책을 반납하고 다시 빌릴 수 있으므로 (member_id, book_id)를 이력의 기본키로 삼지 않습니다. 같은 두 값이 다른 시각에 다시 등장할 수 있기 때문입니다. 사건 ID가 같을 때만 같은 대여로 봅니다.
관계의 최소와 최대를 적습니다
관계도 선 위에 members 1 — loans 0..N, books 1 — loans 0..N을 적고 loans에서 부모 방향에는 1이라고 표시합니다. 아직 대여하지 않은 회원과 한 번도 대여되지 않은 책도 보존해야 합니다. 최대 N만 쓰고 최소 0을 생략하면 나중에 INNER JOIN으로 빈 회원을 누락시키는 선택을 알아채기 어렵습니다. 그림과 조회 계약을 연결합니다.
returned_at이 NULL이면 이번 모델에서는 미반납입니다. 이를 빈 문자열이나 0으로 대신하면 시각 비교와 집계 조건이 복잡해집니다. loaned_at은 필수이고 returned_at은 대여 시각 이상이거나 NULL이어야 합니다. SQLite 따라하기는 같은 길이·같은 시간대 ISO 형식 문자열을 쓰며 H2 미션은 TIMESTAMP를 씁니다. 임의 형식 문자열을 모두 날짜로 검증하는 스키마는 아닙니다.
중복 저장을 줄이는 이유를 설명합니다
대여 행마다 현재 회원명과 현재 제목을 복사하면 제목을 바꿀 때 모든 대여 행도 고쳐야 합니다. 한 행만 놓치면 목록과 상세가 서로 다른 제목을 보여 줍니다. 이번 모델은 부모 테이블에서 현재 값을 조회합니다. 이것이 변경 이상을 줄이는 정규화의 실용적인 이유입니다. 이름을 분리한다는 표현보다 어느 변경이 어디 한 곳에서 일어나는지 설명합니다.
과거 대여 당시 제목을 그대로 남겨야 한다는 요구가 있으면 스냅샷 컬럼을 별도로 설계할 수 있습니다. 그 값은 현재 제목의 복사본이 아니라 당시 표시값이라는 다른 사실입니다. 정규화가 모든 중복을 금지한다는 뜻은 아닙니다. 이번 범위에는 당시 제목 스냅샷이 없으므로 제목 변경 후 과거 대여 조회에도 현재 제목이 나옵니다. 이 제한을 모델 문서에 씁니다.
삭제 정책과 동시 대여는 별개의 결정입니다
대여 이력이 참조하는 도서나 회원을 삭제하면 누가 무엇을 빌렸는지 연결을 잃습니다. 이번에는 FK의 기본 삭제 제한으로 거부합니다. 회원 탈퇴 개인정보 처리와 익명화는 별도 정책이 필요하며 CASCADE를 편의상 붙여 이력을 함께 지우지 않습니다. 참조가 전혀 없는 도서는 삭제할 수 있다는 경계도 함께 적습니다.
FK는 존재하는 도서·회원만 연결되게 하지만 한 책의 활성 대여가 두 행 생기는 문제를 막지 않습니다. 행 하나의 CHECK로 다른 행의 미반납 상태를 보장할 수 있다고 설명하지 않습니다. 동시 대여 방어는 후속 모듈에서 다룹니다. 관계도 리뷰에서는 이번 제약이 보장하는 것과 아직 보장하지 않는 것을 구체적인 반례로 보여 줍니다.
이관에서 모르는 사실을 만들지 않습니다
앞 모듈 파일은 id, title, borrowed 세 필드입니다. borrowed=false인 도서는 ID·제목을 그대로 이관할 수 있지만 true인 도서는 회원과 대여 시작 시각이 없습니다. 임의 회원을 만들거나 오늘 시각으로 채우면 새 DB의 대여 기록은 실제 사건과 달라집니다. 미션의 이관 코드는 true가 있으면 전체 이관을 시작하기 전에 거부합니다. 원본 파일을 보존하고 담당자에게 확인 대상을 전달합니다.
제출 관계도에는 세 테이블의 행 의미, PK·FK, 최소·최대 참여, 삭제 정책을 적습니다. 데이터 사전에는 각 컬럼의 의미·필수 여부·예시를 씁니다. 동일 제목 두 권과 동명이인, 대여 0건 회원, 같은 책 재대여 사례를 직접 표시해 모델이 사건을 잃지 않는지 확인합니다. 도식 기호의 모양보다 이 사례를 설명할 수 있는지가 완료 기준입니다.
따라하기
같은 제목 두 실물을 확인합니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
아래 초기 SQL을 새 SQLite 메모리 DB에서 실행한 뒤 조회합니다. ID가 다른 두 권을 확인합니다.
SELECT id,title FROM books WHERE title='Java' ORDER BY id;실행 결과
10 Java 11 Java
대여 사건과 현재 제목을 읽습니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
FK와 부모 키를 따라 사건마다 한 행을 읽습니다.
SELECT l.id,m.id,b.id,b.title FROM loans l JOIN members m ON m.id=l.member_id JOIN books b ON b.id=l.book_id ORDER BY l.id;실행 결과
1 1 7 SQL 2 2 10 Java
아직 대여하지 않은 회원을 찾습니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
대여 0건 회원은 부모가 존재하되 자식 짝이 없습니다.
SELECT m.id,m.name FROM members m LEFT JOIN loans l ON l.member_id=m.id WHERE l.id IS NULL ORDER BY m.id;실행 결과
3 민지
같은 실물 재대여를 보존합니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
같은 도서·회원 조합도 서로 다른 사건 ID로 두 행을 유지합니다.
INSERT INTO loans VALUES(3,10,2,'2026-10-02T10:00:00',NULL);
SELECT id,book_id,member_id FROM loans WHERE book_id=10 ORDER BY id;실행 결과
2 10 2 3 10 2
확인 문제
실습
한국어 관계도와 데이터 사전을 제출합니다. books·members·loans의 행 의미, PK·FK, 양방향 최소·최대 관계, 대여 시각 필수·반납 NULL, 참조 부모 삭제 제한을 표시합니다. 동일 제목 두 권·동명이인·대여 0건·재대여 사례를 각 키로 설명합니다. 파일 true의 회원·시각 누락과 임의 이관 거부 이유, 현재 제목 조회와 당시 스냅샷 차이도 기록합니다. 앞 API의 ID 계약을 유지하는지 동료가 읽고 판단할 수 있어야 합니다.
더 읽기
면접 질문
- 도서와 대여 기록의 테이블 관계를 설명합니다.