履歴管理ができるテーブル構造を考えてみた

アソビュー！ Advent Calendar 2019 の16日目の記事です。

アソビュー株式会社でバックエンドエンジニアをしている土屋です。
最近はスマブラにハマっており、大会に向けて日々練習中です。

本記事の概要

DBでデータを効率的に管理するためのテーブル構造と、そのメリット・デメリットを考えてみたお話です。

背景

アソビュー！では様々なデータをRDBで取り扱っていますが、テーブル構造はメンテナンスが難しいです。
モノリシックなマスターテーブルが存在していて、更新日カラムが付属しているけどどこを更新したのかわからない。
また、過去のデータがほしいけどマスターテーブルなので元々がどんなデータだったかわからない。
ということがたまに発生したりします。

上記から履歴管理ができるテーブル構造を作る必要があったため、検討をしました。

検証

ショップと商品というデータの格納方法について、以下の2つの方法でメリット・デメリットを検討してみます。

• マスターテーブルにまとめた場合
• 履歴管理を視野に入れて設計した場合

マスターテーブルにまとめた場合

DDL

create table sandbox.SHOP_MASTER
(
    ID          serial primary key comment 'ショップID',
    NAME        varchar(128) not null comment 'ショップ名',
    INSERT_DATE datetime     not null default CURRENT_TIMESTAMP() comment 'システム用 登録日時',
    UPDATE_DATE datetime     not null default CURRENT_TIMESTAMP() comment 'システム用 更新日時'
) comment 'ショップ';

create table sandbox.SHOP_ITEM_MASTER
(
    ID          serial primary key comment '商品ID',
    SHOP_ID     bigint unsigned not null comment 'ショップID',
    NAME        varchar(128)    not null comment '商品名',
    PRICE       int unsigned    not null comment '店頭価格',
    NET_PRICE   int unsigned comment 'ネット価格',
    INSERT_DATE datetime        not null default CURRENT_TIMESTAMP() comment 'システム用 登録日時',
    UPDATE_DATE datetime        not null default CURRENT_TIMESTAMP() comment 'システム用 更新日時',
    constraint fk_shop_item_master_SHOP_ID foreign keY (SHOP_ID)
        references sandbox.SHOP_MASTER (ID)
) comment '商品';

ER

メリット

• テーブル数が少ないので、ぱっと見でどういうデータ構造かがわかりやすい
• テーブル数が少ないということは、検索のSQLが組みやすい

デメリット

• 更新日をもっていても、何を更新したかがわからない
• 情報を増減させるために、ALTER TABLE文を使う可能性がある
  • 例1：店頭料金、ネット料金以外に宅配料金を追加したい場合
  • 例2：店舗に住所を保持させたい
• ALTERを続けることにより、データにnullが入る可能性が高くなります

履歴管理を視野に入れて設計した場合

DDL

create table sandbox.SHOPS
(
    ID          serial primary key comment 'ID',
    INSERT_DATE datetime not null default CURRENT_TIMESTAMP() comment 'システム用 登録日時'
) comment 'ショップID';

create table sandbox.SHOP_REVISIONS
(
    ID          serial primary key comment 'ショップリビジョンID',
    SHOP_ID     bigint unsigned not null comment 'ショップID',
    INSERT_DATE datetime        not null default CURRENT_TIMESTAMP() comment 'システム用 登録日時',
    constraint fk_shop_revisions_SHOP_ID foreign key (SHOP_ID)
        references sandbox.SHOPS (ID)
) comment 'ショップリビジョン';

create table sandbox.LATEST_SHOP_REVISIONS
(
    SHOP_ID     bigint unsigned not null comment 'ショップID',
    REVISION_ID bigint unsigned not null comment 'ショップリビジョンID',
    INSERT_DATE datetime        not null default CURRENT_TIMESTAMP() comment 'システム用 登録日時',
    constraint fk_latest_shop_revisions_SHOP_ID foreign key (SHOP_ID)
        references sandbox.SHOPS (ID),
    constraint fk_latest_shop_revisions_REVISION_ID foreign key (REVISION_ID)
        references sandbox.SHOP_REVISIONS (ID)
) comment '最新のショップリビジョン';

create table sandbox.SHOP_NAMES
(
    REVISION_ID bigint unsigned not null comment 'ショップリビジョンID',
    NAME        varchar(128)    not null comment 'ショップ名',
    INSERT_DATE datetime        not null default CURRENT_TIMESTAMP() comment 'システム用 登録日時',
    constraint fk_shop_names_REVISION_ID foreign key (REVISION_ID)
        references sandbox.SHOP_REVISIONS (ID)
);

create table sandbox.SHOP_ITEMS
(
    ID          serial primary key comment '商品ID',
    REVISION_ID bigint unsigned not null comment 'ショップリビジョンID',
    INSERT_DATE datetime        not null default CURRENT_TIMESTAMP() comment 'システム用 登録日時',
    constraint fk_shop_items_REVISION_ID foreign key (REVISION_ID)
        references sandbox.SHOP_REVISIONS (ID)
);

create table sandbox.SHOP_ITEM_NAMES
(
    ITEM_ID     bigint unsigned not null comment '商品ID',
    NAME        varchar(128)    not null comment '商品名',
    INSERT_DATE datetime        not null default CURRENT_TIMESTAMP() comment 'システム用 登録日時',
    constraint fk_shop_item_names_ITEM_ID foreign key (ITEM_ID)
        references sandbox.SHOP_ITEMS (ID)
) comment '商品名';

create table sandbox.SHOP_ITEM_PRICES
(
    ITEM_ID     bigint unsigned not null comment '商品ID',
    AMOUNT      int unsigned    not null comment '価格',
    INSERT_DATE datetime        not null default CURRENT_TIMESTAMP() comment 'システム用 登録日時',
    constraint fk_shop_item_prices_ITEM_ID foreign key (ITEM_ID)
        references sandbox.SHOP_ITEMS (ID)
) comment '商品店頭価格';

create table sandbox.SHOP_ITEM_NET_PRICES
(
    ITEM_ID     bigint unsigned not null comment '商品ID',
    AMOUNT      int unsigned    not null comment '価格',
    INSERT_DATE datetime        not null default CURRENT_TIMESTAMP() comment 'システム用 登録日時',
    constraint fk_shop_item_net_prices_ITEM_ID foreign key (ITEM_ID)
        references sandbox.SHOP_ITEMS (ID)
) comment '商品ネット価格';

ER

メリット

• 履歴管理をすることで、いつどんな変更があったかを確認することができます
  • この情報を機械学習で利用することで、何らかの相関関係を見つけて業務に利用できる可能性もあります
• 宅配価格や住所を追加したい場合、CREATE TABLEだけでOKになります
• 1つのテーブル情報が肥大化しないので、変更容易性に富みます
• データが独立しているので、nullのデータが入りにくいです

デメリット

• テーブル数が増えるため、命名にルールがないと管理が大変になります
• 履歴を管理するという要件の特性上、レコード数が膨大になりやすいです
  • リビジョンを上げるタイミングなどは要件として考慮する必要があります

おわりに

履歴管理を行うことで、どんな操作が行われたかわかるようになるので、利用者も開発者にも恩恵があると思っています。
とはいえ不要なデータを管理する必要はないので、履歴管理の対象はどれなのかを都度検討できるとよいかなと思います。

以上です。お疲れ様でしたー。

参考

• 変更履歴を持つテーブルの設計 - Qiita
• イミュータブルデータモデル(世代編)