記事の目的

私が普段携わるプロジェクトでは、以下の理由からプログラムコードだけでなくデータベースも可能な限りイミュータブルを意識して設計している。

プログラムコードだけでなく、データだってイミュータブルのほうが安全
登録時も更新時もデータベース操作はほとんど同じ手続きになるので処理がシンプルになる
更新日時カラムに意味を感じない
- 最後の更新タイミングしか表現できないしどの情報がどう変わったのかもわからない
だからといって履歴テーブルはヤダ
- 履歴を登録するのはデータ登録の前なのか後なのかとかくだらないことで悩みたくない
- そもそも登録処理の途中で落ちたら履歴は残るのか？とか
多くの場合DBのトランザクションを無くせる可能性がある
- これについては別途また記事を書きたい

イミュータブルなテーブル設計のデメリットとして、一般的なテーブル設計よりもテーブル数が増えるのでSELECTが複雑になりがちとか、レコード量が膨大になりがちとかあるので、ありとあらゆるテーブルにおいてイミュータブルな構造にすべきとは思わないものの、一考の価値は間違いなくあると思うのでこの記事で取り上げてみる。

前提

PostgreSQL13およびh2db(SpringBoot2.6組み込み)で動作確認
DMLはmyBatisのマッパー形式

この記事のテーブル設計を使ったサンプルJavaプロジェクトはこちらで公開
https://github.com/value-it/springboot-apps-template

本題

簡単な書籍管理データベースをテーマにしてテーブル設計してみる

設計方針＆要点

情報の更新は新しいレコードのINSERTによって表現する
UPDATEは一切禁止とする
更新日付を表すカラムを作らない
履歴テーブルではなく、revisionテーブルによってデータの変遷が把握できる
latestテーブルによって現在のrevisionをポインタ的に指し示す
latestテーブルによって過去のデータ状態に容易に戻せる

ER図

DDL

DROP SCHEMA IF EXISTS bookcatalog CASCADE;
CREATE SCHEMA bookcatalog;

DROP SEQUENCE IF EXISTS bookcatalog.seq_book_id;
DROP SEQUENCE IF EXISTS bookcatalog.seq_book_revision;

CREATE SEQUENCE bookcatalog.seq_book_id START WITH 10000 INCREMENT BY 1;
CREATE SEQUENCE bookcatalog.seq_book_revision START WITH 1 INCREMENT BY 1;

CREATE TABLE bookcatalog.books
(
    id         bigint                   NOT NULL DEFAULT nextval('bookcatalog.seq_book_id') primary key,
    created_at timestamp with time zone NOT NULL DEFAULT now()
);

CREATE TABLE bookcatalog.books_revision
(
    revision   bigint                   NOT NULL DEFAULT nextval('bookcatalog.seq_book_revision') primary key,
    book_id    bigint                   NOT NULL,
    title      character varying(50)    NOT NULL,
    isbn       character varying(13)    NOT NULL,
    pages      integer                  NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now()
);
ALTER TABLE bookcatalog.books_revision
    ADD CONSTRAINT fk_books_revision_book_id FOREIGN KEY (book_id) REFERENCES bookcatalog.books (id);
CREATE INDEX ON bookcatalog.books_revision (book_id);


CREATE TABLE bookcatalog.books_revision_latest
(
    book_id    bigint                   NOT NULL primary key,
    revision   bigint                   NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now()
);
ALTER TABLE bookcatalog.books_revision_latest
    ADD CONSTRAINT fk_books_revision_latest_book_id FOREIGN KEY (book_id) REFERENCES bookcatalog.books (id);
ALTER TABLE bookcatalog.books_revision_latest
    ADD CONSTRAINT fk_books_revision_latest_revision FOREIGN KEY (revision) REFERENCES bookcatalog.books_revision (revision);
ALTER TABLE bookcatalog.books_revision_latest
    ADD CONSTRAINT uq_ UNIQUE (revision);
CREATE INDEX ON bookcatalog.books_revision_latest (revision);

参照時のSQL

books_revision_latestをINNER JOINすることで最新のリビジョンだけが取得できる

SELECT books.id
     , books_revision.revision
     , title
     , isbn
     , pages
FROM bookcatalog.books
INNER JOIN bookcatalog.books_revision_latest ON books_revision_latest.book_id = books.id
INNER JOIN bookcatalog.books_revision ON books_revision.revision = books_revision_latest.revision

登録時のSQL

books_revision_latestのDELETEは無くても良いが、更新時と共通にしたいので敢えて発行

INSERT INTO bookcatalog.books(id)
VALUES (#{bookId.value})
;
INSERT INTO bookcatalog.books_revision(revision, book_id, title, isbn, pages)
VALUES (#{revision.value},
        #{bookId.value},
        #{title.value},
        #{isbn.value},
        #{pages.value})
;
DELETE
FROM bookcatalog.books_revision_latest
WHERE book_id = #{bookId.value}
;
INSERT INTO bookcatalog.books_revision_latest(book_id, revision)
VALUES (#{bookId.value},
        #{revision.value})
;

更新時のSQL

登録時との違いはbooksテーブルのINSERTが無いだけ

INSERT INTO bookcatalog.books_revision(revision, book_id, title, isbn, pages)
VALUES (#{revision.value},
        #{bookId.value},
        #{title.value},
        #{isbn.value},
        #{pages.value})
;
DELETE
FROM bookcatalog.books_revision_latest
WHERE book_id = #{bookId.value}
;
INSERT INTO bookcatalog.books_revision_latest(book_id, revision)
VALUES (#{bookId.value},
        #{revision.value})
;