ErdDocs

ERD 예제 / 도서관 관리 시스템

도서관 관리 시스템

도서관 데이터베이스 설계의 바탕은 「도서」(목록상의 제목)와 「소장본」(서가에 있는 한 권 한 권의 실물)을 나누는 것입니다. 회원이 빌리는 것은 소장본이고, 예약은 도서에 겁니다. 공저가 있으므로 저자는 중간 테이블로 연결합니다.

아래에서 ErdDocs가 이 스키마의 모든 테이블과 관계를 SQL로 그립니다. 「데이터 사전」 탭에는 인쇄용 문서가 있고, 이 페이지에서 전체를 무료로 볼 수 있습니다. SQL은 페이지 아래쪽에 있습니다.

내 스키마

테이블에 마우스를 올리면 관계가 강조되고, 클릭하면 고정됩니다.

각 테이블의 역할

  • books / copies목록상의 도서와 바코드가 붙은 실물. 도서관 설계 문제는 대부분 이 구분으로 귀결됩니다.
  • authors / book_authors다대다. 공저가 하나만 있어도 books에 author_id를 바로 두는 설계는 깨집니다.
  • memberscard_no(회원 카드 번호)가 사람이 쓰는 식별자입니다.
  • loans소장본에 연결합니다. 어느 실물이 대출 중이고 연체 중인지 알아야 하기 때문입니다.
  • reservations도서에 연결합니다. 회원이 원하는 것은 제목이고, 먼저 돌아오는 한 권이면 됩니다.

가져갈 만한 설계 결정

  • 대출은 소장본을, 예약은 도서를 참조합니다. 이 두 수준을 섞는 것이 도서관 스키마의 대표적인 실수입니다. 섞으면 「이 책을 누가 가지고 있나」와 「다음 예약자에게 알리기」 둘 다 답할 수 없게 됩니다.
  • returned_on을 NULL 허용으로 두면 반납 여부 플래그와 반납일 기록을 한 컬럼으로 해결합니다.
  • ISBN은 고유하지만 NULL을 허용합니다. 오래된 자료와 내부 자료가 실제로 있으므로 NOT NULL로 두면 실데이터가 제약 위반이 됩니다.

SQL

PostgreSQL 방언입니다. 위의 툴(이미 들어 있습니다), 내 데이터베이스, 마이그레이션에 자유롭게 복사해서 쓰세요.

CREATE TABLE authors (
    id         bigserial    PRIMARY KEY,
    full_name  varchar(120) NOT NULL
);

CREATE TABLE books (
    id        bigserial    PRIMARY KEY,
    isbn      varchar(17)  UNIQUE,
    title     varchar(240) NOT NULL,
    published smallint
);
COMMENT ON COLUMN books.isbn IS 'ISBN 이전 자료나 내부 자료는 NULL';

CREATE TABLE book_authors (
    book_id    bigint NOT NULL REFERENCES books(id),
    author_id  bigint NOT NULL REFERENCES authors(id),
    PRIMARY KEY (book_id, author_id)
);

CREATE TABLE copies (
    id        bigserial   PRIMARY KEY,
    book_id   bigint      NOT NULL REFERENCES books(id),
    barcode   varchar(20) NOT NULL UNIQUE,
    shelf     varchar(20),
    condition varchar(20) NOT NULL DEFAULT 'good'
);
COMMENT ON TABLE copies IS '실물 소장본. 인기 있는 도서는 여러 권';

CREATE TABLE members (
    id         bigserial    PRIMARY KEY,
    card_no    varchar(20)  NOT NULL UNIQUE,
    full_name  varchar(120) NOT NULL,
    joined_on  date         NOT NULL
);

CREATE TABLE loans (
    id          bigserial PRIMARY KEY,
    copy_id     bigint    NOT NULL REFERENCES copies(id),
    member_id   bigint    NOT NULL REFERENCES members(id),
    loaned_on   date      NOT NULL,
    due_on      date      NOT NULL,
    returned_on date
);
COMMENT ON COLUMN loans.returned_on IS '대출 중에는 NULL';

CREATE TABLE reservations (
    id          bigserial   PRIMARY KEY,
    book_id     bigint      NOT NULL REFERENCES books(id),
    member_id   bigint      NOT NULL REFERENCES members(id),
    placed_at   timestamptz NOT NULL DEFAULT now(),
    fulfilled   boolean     NOT NULL DEFAULT false
);
COMMENT ON TABLE reservations IS '예약은 「도서」에 건다. 어느 소장본이 반납되어도 충족된다';

자주 묻는 질문

연체료는 어디에 두나요?

loans를 참조하는 fines 테이블(연체된 대출이 원인)에 금액, 발생일, 납부일을 둡니다. 과제 수준이라면 반납 기한으로 그때그때 계산해도 됩니다. 어느 쪽을 택했는지 이유와 함께 적어 두세요.

전자책이 생기면 무엇이 달라지나요?

전자책에는 실물이 없으므로 대출은 라이선스 문제가 됩니다. copies와 나란한 licenses 테이블(동시 대출 N권)을 만들거나, 대출을 도서에 바로 연결하고 동시 대출 수는 애플리케이션에서 확인합니다.

다른 예제