3. 분석과 설계 - 데이터

1. 목표

테이블 3개(books, members, issues)의 역할 이해
- members: 회원 마스터
- books: 도서 마스터 + 상태(is_available)
- issues: 대여 업무 기록(대여/반납/연장 흐름의 중심)
테이블 관계(FK)와 JOIN 필요성 이해
- issues는 books, members를 참조하는 구조(FK)
- 대여 목록에서 title을 표시하려면 issues + books JOIN이 필요
기능별 SQL 흐름을 "업무"와 연결해서 이해
- 회원 등록 = members INSERT
- 도서 등록 = books INSERT
- 대여 = issues INSERT + books UPDATE(FALSE)
- 반납 = issues DELETE + books UPDATE(TRUE)
- 연장 = issues UPDATE(renew_count 증가, issue_date 갱신)
대여/반납은 "다중 쿼리로 구성된 하나의 작업"이며 트랜잭션이 필요한 이유 이해
- 일부 쿼리만 성공하면 데이터 불일치가 발생
- 실무에서는 트랜잭션으로 원자성(ALL OR NOTHING)을 보장

2. 테이블 구조

(1) members (회원)

컬럼	타입	의미	예시
member_id	VARCHAR(50) PK	회원 고유 ID	"M100"
name	VARCHAR(50)	이름	"김코딩"
mobile	VARCHAR(20)	연락처	"010-1234-5678"
email	VARCHAR(100)	이메일	"kim@test.com"

회원의 기본 정보를 관리하는 마스터 테이블
회원 삭제가 되면, 해당 회원의 대여 기록(issues)은 FK 규칙에 의해 함께 삭제될 수 있습니다(ON DELETE CASCADE)

(2) books (도서)

컬럼	타입	의미	예시
book_id	VARCHAR(50) PK	도서 고유 ID	"B001"
title	VARCHAR(100)	제목	"파이썬 기초"
author	VARCHAR(100)	저자	"홍길동"
publisher	VARCHAR(100)	출판사	"OO출판"
is_available	BOOLEAN	대여 가능 여부	TRUE/FALSE

도서 기본 정보 + 현재 상태(is_available)를 함께 관리
대여/반납 시마다 is_available 값이 변경됨
- TRUE: 대여 가능
- FALSE: 대여 중

(3) issues (대여 기록)

컬럼	타입	의미	예시
issue_id	INT PK AI	대여 기록 고유 번호	1, 2, 3...
book_id	VARCHAR(50) FK	대여된 도서 ID	"B001"
member_id	VARCHAR(50) FK	대여한 회원 ID	"M100"
issue_date	DATETIME	대여일/연장일(갱신)	"2025-12-18 10:30:00"
renew_count	INT	연장 횟수	0, 1, 2...

issues는 books, members를 "참조"하는 테이블입니다.
FK가 있으므로, 존재하지 않는 book_id/member_id로 대여 기록을 만들 수 없습니다.

3. 주요 사항

현재 테이블 상태만 보면, book_id가 issues에 여러 번 들어갈 수 있습니다.

즉, DB 레벨에서는 "한 권이 동시에 여러 명에게 대여 되는 것"을 완벽히 막지는 못합니다.

3.1 관계 요약

회원 1명 → 여러 대여 기록 (1:N)
members 1명은 issues 여러 건을 가질 수 있다
도서 1권 → 대여 중일 때 대여 기록 최대 1건 (0..1)
books 1권은 대여 중일 때 issues에 "최대 1건"만 존재해야 한다

3.2 현재 프로젝트의 제어 방식(로직 기반)

현재는 DB 제약으로 "동시 대여"를 강제하지 않고, 다음 로직으로 통제합니다.
대여 전 books.is_available = TRUE 일 때만 대여 수행
대여 시
- issues INSERT
- books.is_available = FALSE 업데이트

3.3 참고 사항

"DB 제약으로 막는 방법"과 "코드 로직으로 막는 방법"이 있습니다.
1. 지금 프로젝트는 학습 단순화를 위해 로직 기반 통제를 선택했고,
  'UI/로직 검증'으로 동시 대여를 막는 방법을 선택했습니다.
2. DB 레벨에서 강제하려면 다음 중 하나를 적용할 수 있습니다.
  - A) issues에 book_id UNIQUE 제약(가장 직관적)
    같은 book_id가 issues에 2번 들어가지 못하도록 제한
  - B) 트랜잭션 + 행 잠금(SELECT ... FOR UPDATE)
    동시성 환경에서 더 안전한 방식

4. TRANSACTION 살펴보기

(1) 발생 가능한 문제

issues INSERT 성공
books UPDATE 실패

→ 대여 기록은 있는데 도서는 대여 가능 상태
→ 데이터 불일치 발생

(2) 트랜잭션 개념

"대여/반납은 2개 쿼리가 하나의 작업이므로,
실무에서는 트랜잭션(START TRANSACTION/COMMIT/ROLLBACK)으로 묶어 사용합니다.
개념용 SQL 예시(대여)

START TRANSACTION;

INSERT INTO issues (book_id, member_id) VALUES ("B001", "M001");
UPDATE books SET is_available = FALSE WHERE book_id = "B001";

COMMIT;

실패 시:

ROLLBACK;

(3) 트랜잭션 동작 개

COMMIT: 모두 성공했을 때만 반영
ROLLBACK: 하나라도 실패하면 이전 상태로 복구

5. Join 살펴보기

대여 목록 화면

issues에는 book_id만 있음
title은 books에 있음
따라서 JOIN 필요

SELECT b.book_id, b.title, i.issue_date, i.renew_count, i.member_id
FROM issues i
JOIN books b ON i.book_id = b.book_id
WHERE i.member_id = 'M001';

6. 시나리오별 데이터 흐름

1) 데이터 흐름

2) 테이블 준비 SQL

-- 1. 데이터베이스 선택/생성
CREATE DATABASE IF NOT EXISTS library_db;
USE library_db;

-- 2. 기존 테이블이 있다면 안전하게 삭제 (옵션: 테스트 환경에서만 사용)
-- DROP TABLE IF EXISTS issues;
-- DROP TABLE IF EXISTS members;
-- DROP TABLE IF EXISTS books;

-- 3. 도서 테이블 (book_id 사용)
CREATE TABLE IF NOT EXISTS books (
    book_id VARCHAR(50) PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    publisher VARCHAR(100),
    is_available BOOLEAN DEFAULT TRUE
);

-- 4. 회원 테이블 (member_id 사용)
CREATE TABLE IF NOT EXISTS members (
    member_id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(50),
    mobile VARCHAR(20),
    email VARCHAR(100)
);

-- 5. 대여 기록 테이블
CREATE TABLE IF NOT EXISTS issues (
    issue_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id VARCHAR(50),
    member_id VARCHAR(50),
    issue_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    renew_count INT DEFAULT 0,
    FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE,
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE
);


-- 6. 샘플 데이터 입력
INSERT INTO members(member_id, name, mobile, email) VALUES
('M001', '김코딩', '010-1111-2222', 'kim@demo.com'),
('M002', '이파이', '010-3333-4444', 'lee@demo.com');

INSERT INTO books(book_id, title, author, publisher, is_available) VALUES
('B001', '파이썬 기초', '홍길동', '코딩출판', TRUE),
('B002', 'PySide6 실습', '김개발', 'Qt출판', TRUE),
('B003', '데이터베이스 입문', '박DB', 'SQL출판', TRUE);

6.1 회원 등록/조회(MemberManagerPage)

(1) 등록: INSERT 후 목록 갱신 SELECT

GUI에서 "회원 등록" 버튼을 누르면 하는 일
1. 입력값 검증(빈 값 체크)
2. members에 INSERT
3. 회원 목록 다시 SELECT해서 테이블 갱신

INSERT INTO members (member_id, name, mobile, email)
VALUES ("M010", "최학생", "010-9999-0000", "student@demo.com");

SELECT * FROM members;

(2) 검색: LIKE 기반 부분 검색

예시: 이름에 "김"이 들어간 회원 / 연락처 일부 "1111"

SELECT * FROM members WHERE name LIKE "%김%";
SELECT * FROM members WHERE mobile LIKE "%1111%";

6.2 도서 등록/조회(BookManagerPage)

INSERT INTO books (book_id, title, author, publisher)
VALUES ("B010", "Qt GUI 시작", "정큐티", "GUI출판");

SELECT * FROM books;

SELECT * FROM books WHERE title LIKE "%PySide%";

6.3 대여/반납/연장(CirculationPage)

Circulation은 단순 CRUD 화면이 아니라 "업무 프로세스"입니다.

(1) 업무 규칙

선행조건: 회원 조회 성공(current_member_id 필요)
도서 상태: books.is_available = TRUE일 때만 대여 가능
대여: issues INSERT + books UPDATE(FALSE)
반납: issues DELETE + books UPDATE(TRUE)
연장: issues UPDATE(renew_count, issue_date)

(2) 대여

SELECT book_id, is_available FROM books WHERE book_id = "B001";

INSERT INTO issues (book_id, member_id) VALUES ("B001", "M001");
UPDATE books SET is_available = FALSE WHERE book_id = "B001";

SELECT b.book_id, b.title, i.issue_date, i.renew_count, i.member_id
FROM issues i JOIN books b ON i.book_id = b.book_id
WHERE i.member_id = "M001";

(3) 반납 예시

DELETE FROM issues WHERE book_id = "B001";
UPDATE books SET is_available = TRUE WHERE book_id = "B001";

(4) 연장 예시

UPDATE issues
SET issue_date = NOW(),
    renew_count = renew_count + 1
WHERE book_id = "B002";

티스토리툴바