テーブル設計の基礎的なリファクタリングをする

株式会社FLINTERSの塚本です。
新卒3年目で、バックエンド・インフラ・データフローあたりを担当しています。(最近フロントエンドにも入門しました。)

データ構造の設計ってプロジェクトの成功を左右する最重要ポイントですよね。
最近、私の担当したテーブル定義リファクタリングタスクについて紹介します。

背景

私の担当しているシステムでは、ある程度の頻度で、ユーザー要望によって変更するシステム設定をDBで管理していました。このテーブル定義が歴史的経緯から、デグレードを引き起こしやすい設計で、設定内容の改変のたびに大きなレビューコストが掛かっており、変更手続きが安全でもないのでリリースのたびに恐ろしい気持ちでした。チームへリファクタリング提案を行い、改善を見たので本稿がその記録になります。

既存設計

なお、ドメイン知識の多い実際のテーブルの抽象化DDLのみだとわかりにくいので、親子関係に模した比喩を付記します。

DDL

CREATE TABLE 親テーブル (
    ID      TEXT NOT NULL, 
    大分類   TEXT NOT NULL,
    値      TEXT NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE 子テーブル (
    ID        TEXT NOT NULL
    子の種類    TEXT NOT NULL, -- 各親に対して一意
    大分類     TEXT NOT NULL,
    値        TEXT NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE 親子紐づけテーブル (
    表示順       INTEGER NOT NULL,
    表示箇所フラグ BOOLEAN NOT NULL, -- 親に対して一意
    親ID         TEXT    NOT NULL,
    子ID         TEXT    NOT NULL,
    UNIQUE (親ID, 子ID),
    FOREIGN KEY (親ID) REFERENCES 親テーブル (ID),
    FOREIGN KEY (子ID) REFERENCES 子テーブル (ID)
);

なお、表示箇所フラグが親テーブルではなくて紐づけテーブルにあるのは、ここで言う親ドメインは親子で例えると「成人」くらいの意味で、親子世帯に対して初めて表示箇所の設定が与えられるためです。

このように設定項目と、その具体的な内容、その組み合わせを3つのテーブルで管理する設計になっていました。

問題点

このテーブル構造は以下のような問題点を抱えていました。

問題点1

大分類が違う設定内容も紐づけられてしまう
別々のアパートに住んでいるのに勝手に同一世帯に設定出来るようなもの

問題点2

子の種類が同じ紐づけを出来てしまう
ある家庭に「長男」が2人居ることに設定出来てしまうようなもの

問題点3

表示順で、既存の項目の間に入れようとすると、既存のものがINTEGERで1刻みで入っているので、既存部分の定義に大量に変更が必要になってしまう
2階に住んでいる世帯と3階に住んでいる世帯の間に新しい世帯を入れたいけど、8階建てとして、3階~7階の世帯全員に1階ずつのお引越しを頼まないと行けないようなもの

問題点4

表示される親項目に対して一意のはずの表示箇所フラグや表示順を、紐づけレコード毎にバラバラに入れられる
ある世帯が市内に住んでいるかどうかや、住んでいる階数を聞きたいとき、子供によって市内に住んでるか否かバラバラのことを言い、しょうがないので最初に発言した子の証言を採用するルールになっているようなもの

新設計

DDL

親子紐づけテーブルを2つに分離しました

CREATE TABLE 世帯情報テーブル (
  親ID         TEXT    NOT NULL,
  表示順        NUMERIC NOT NULL,  -- 型を変更！！
  表示箇所フラグ BOOLEAN NOT NULL,
);

CREATE TABLE 親子紐づけ「だけ」テーブル (
  親ID         TEXT    NOT NULL,
  子ID         TEXT    NOT NULL,
  子の種類      TEXT    NOT NULL,
  大分類        TEXT    NOT NULL,
  UNIQUE (親ID, 子の種類)
  FOREIGN KEY (親ID) REFERENCES 世帯情報テーブル
  FOREIGN KEY (親ID, 大分類) REFERENCES 親テーブル
  FOREIGN KEY (子ID, 大分類, 子の種類) REFERENCES 子テーブル
)

ALTER TABLE 親テーブル
ADD CONSTRAINT 複合外部キー制約のための一意制約
        UNIQUE (ID, 大分類);
;
ALTER TABLE 子テーブル
ADD CONSTRAINT 複合外部キー制約のための一意制約
        UNIQUE (ID, 大分類, 子の種類);
;

検証

問題点が解決したかを見ていきましょう。

問題点1

大分類が違う設定内容も紐づけられてしまう

親子紐づけ「だけ」テーブルに大分類も保存することにして、親・子それぞれのテーブルと紐づけることによって、親・子で大分類が一致することが保証される

問題点2

子の種類が同じ紐づけを出来てしまう

親子紐づけ「だけ」テーブルのUNIQUE制約を変更し、親と「子の種類」に対して一意であることが保証される
(なお、ここでUNIQUE制約から子IDが消えているが、ここは親子関係と違って、別に同一の子項目が複数の役割を果たしたいケースが存在する可能性が判明したので消去した)

問題点3

表示順で、既存の項目の間に入れようとすると、既存のものがINTEGERで1刻みで入っているので、既存部分の定義に大量に変更が必要になってしまう

表示順をNUMERIC型に変更したので、既存の項目の表示順を変えなくとも、小数で間に挿入することが可能になった

問題点4

表示される親に対して一意のはずの表示箇所フラグや表示順を、紐づけレコード毎にバラバラに入れられる

正規化して世帯情報テーブルに集約したので齟齬が発生する可能性がなくなった

すべて解決しましたね、めでたしめでたし。

まとめ

今回のリファクタリングは、正規化や適切な整合性制約の追加といった、応用情報技術者試験でも頻出の概念を良く扱うことで整理できました。基礎って大事ですね。