図書館管理システム

各テーブルの役割

• 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 '予約は「図書」に入る。どの蔵書が返却されても充当できる';

よくある質問

ほかのサンプル

• 病院管理システム
• 学校管理システム
• サンプル一覧
• テーブル定義書の自動生成