ErdDocs

ER 圖範例 / 圖書館管理系統

圖書館管理系統

圖書館資料庫設計的基礎，是把「書目」（目錄上的書名）和「館藏」（書架上一本一本的實體書）分開。讀者借的是館藏，預約則是對書目。因為有合著，作者要透過中介資料表連結。

下方由 ErdDocs 依 SQL 畫出這份結構，包含所有資料表與關聯。「資料字典」分頁有可列印的版本，在本頁免費完整顯示。SQL 在頁面更下方。

你的結構

把滑鼠移到資料表上會標示它的關聯，點一下即可固定。

每個資料表的用途

  • books / copies目錄上的書目與貼了條碼的實體書。圖書館設計題大多離不開這個區分。
  • authors / book_authors多對多。只要有一本合著，把 author_id 直接放在 books 的設計就會失敗。
  • memberscard_no（借書證號碼）是人實際使用的識別碼。
  • loans連到館藏，因為必須知道是哪一本實體書借出、是否逾期。
  • reservations連到書目。讀者要的是書名，先歸還的哪一本都可以。

值得借鏡的設計決策

  • 借閱參照館藏，預約參照書目。把這兩個層級混在一起，是圖書館結構的典型錯誤。這樣會讓「這一本在誰手上」和「通知下一位預約者」都無法回答。
  • returned_on 允許 NULL，一個欄位同時當作是否歸還的旗標與歸還日期的紀錄。
  • ISBN 唯一但允許 NULL。舊資料與內部資料確實存在，設成 NOT NULL 會讓真實資料變成約束違規。

SQL

PostgreSQL 方言。可以貼進上方工具（已經放好了）、你自己的資料庫或 migration，也可以自由複製。

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 本），或是把借閱直接連到書目，在應用程式裡檢查同時借閱數。

其他範例