データベース設計の基本とアジャイル開発 - そーだいが解説する変化に対応する実装

はじめまして。そーだい（@soudai1025）です。私は普段は技術コンサルティングや受託開発を請け負う合同会社HaveFunTechの代表として、また、予防治療の自社サービスを展開する株式会社リンケージのCTOという二足の草鞋を履き、日々、さまざまなWebサービスの開発に携わっています。

これまでの開発経験のなかで、データベース設計に関わるさまざまな問題に遭遇してきましたが、本稿ではとくに、アジャイル開発時に発生しやすい問題とその対処についてお伝えしたいと思います。開発の現場で目にしやすい実装におけるアンチパターンを示しつつ、アジャイルという指針を維持しながら、対処となるデータベース設計についてご紹介します。

会員登録のアンチパターンと処方箋

本稿ではWebアプリケーションを作成する際によくある、「会員機能」の実装をモチーフにします。会員機能は「ログイン機能」なども含め、Webアプリケーションフレームワークで標準的な機能として提供されていることも多いです。これら機能の実装が、ビジネスの成長に追従し仕様変化した結果を見てみましょう。まずは以下のサンプルをご覧ください。

create table users
(
    id       bigserial
        constraint users_pk
            primary key,
    name     text not null,
    birthday date not null,
    email    text not null,
    hashed_password  text not null
);

create unique index users_email_uindex
    on users (email);

よく見るユーザ情報と一緒にログインのためのアカウントとパスワードを持ったテーブル構成です。では、このテーブルにLINE認証を追加する場合はどうなるでしょうか。

まずはusersテーブルに素直にline_idとline_tokenを追加します。

alter table users
    add line_id text;

alter table users
    add line_token text;

create unique index users_line_token_uindex
    on users (line_token);

create unique index users_line_id_uindex
    on users (line_id);

この構成ではメールアドレスとパスワードで従前ログインしていたため、not nullになっています。そのためSSOを使用して、LINEログインで新規アカウントを作成し、line_idとline_tokenを埋めたとしてもメールアドレスとパスワードが必要になってしまいます。これではアカウント作成時にせっかくLINEアカウントの登録を行ったのにユーザの手間は減っていません。こうした問題は仮に別テーブルにLINEの情報を分けても問題は解決しません。

実際に私がusersを作成するときは次のようなテーブル設計を行います。

create table users
(
    id      bigserial
        constraint users_pk
            primary key,
    name    text    not null
);


create table user_personal_information
(
    user_id   bigint not null
        constraint user_personal_information_pk
            primary key
        constraint user_personal_information_user_id_fk
            references users,
    zip_code     varchar(7),
    address      text    not null,
    birthday     date    not null,
    phone_number varchar(11)
);

create unique index user_personal_information_phone_number_uindex
    on user_personal_information (phone_number);

create table user_email
(
    id         bigserial
        constraint user_email_pk
            primary key,
    user_id bigint not null
        constraint user_email_user_id_fk
            references users,
    email      text   not null
);


create unique index user_email_email_uindex
    on user_email (email);

create table user_login_hashed_password 
(
    id             bigserial
        constraint user_login_hashed_password_pk
            primary key,
    user_id     bigint not null
        constraint user_login_hashed_password_user_id_fk
            references users,
    login_hashed_password  text   not null
);

create unique index user_login_hashed_password_user_id_uindex
    on user_login_hashed_password  (user_id);

今回はわかりやすく、明確にテーブルを分割してみました。 emailとpasswordも別テーブルにしています。 passwordは認証情報のみの情報です。一方のemailは認証情報のみに使われる情報だとするとpasswordと一緒にしておくのも合理的かもしれません。しかし、emailは「email情報単体で変更される」こともあれば、たとえばGitHubのように複数のemailを持つこともあるでしょう。このように、emailは認証情報以外の属性も持っています。こうした場合、emailにpinコードを送ってWeb画面で認証する、といったワンタイムトークンのような認証機能を実装するとpasswordは不要になります。こうした運用を想定し、今回はemailとpasswordを別テーブルに分ける判断をしました。

またこの設定であれば以下の図のように新たなログイン情報が必要になった際も対応することができます。

create table user_login_line
(
    id         bigserial
        constraint user_login_line_pk
            primary key,
    user_id bigint not null
        constraint user_login_line_user_id_fk
            references users,
    line_id    text   not null,
    line_token text   not null
);

create unique index user_login_line_line_id_uindex
    on user_login_line (line_id);

create unique index user_login_line_line_token_uindex
    on user_login_line (line_token);

次は「ユーザ課金を始めた」というパターンを考えてみます。実装の際、usersテーブルに直接クレジットカード情報を保存してしまうと、先に紹介したLINE認証機能追加のケース同様、新規会員登録の際に、クレジットカードを登録する必要がでてきます。そのためusersテーブルにカラムを追加する際は、多くの場合にクレジットカード情報のカラムにはnullが必要になってしまいます。

では次のような構成ではどうでしょうか。

create table user_credit_card
(
    id              bigserial
        constraint user_credit_card_pk
            primary key,
    user_id         bigint  not null
        constraint user_credit_card_users_id_fk
            references users,
    card_number_last4_digits      varchar(4) not null,
    exiration_date  varchar(4)    not null
);

create unique index user_credit_card_user_id_card_number_last4_digits_uindex
    on user_credit_card (user_id, card_number_last4_digits);

この構成ではユーザがクレジットカードテーブル（user_credit_card）に必要なクレジットカードを登録することができますし、複数のカードを登録することもできます。これならばユーザがわざわざ状況に応じてクレジットカードを登録し直す必要はありません。

この仕組み、一見完璧に見えますが、実際にユーザとして使ってみると毎回クレジットカードの選択が必要になり、ワンクリックで買い物をするような体験を提供することが難しくなってしまいます。対策として考えられるのは「デフォルトのクレジットカード」を設定することです。

もうuserテーブルにデフォルトのクレジットカードのカラムを追加するような前回の失敗はしません。しっかりとクレジットカードテーブルにdefault_usedカラムを設定します。

alter table user_credit_card
              add default_used boolean default false not null;

create unique index user_credit_card_user_id_default_used_uindex
              on user_credit_card (user_id, default_used) where user_credit_card.default_used = true;

サービスがさらに成長し、今度は「クレジットカード以外の支払い方法」を設定する必要が生じます。LINEアカウントとの連携があるので、LINE PayやPayPayといった支払い方法に対応するといった流れは自然です。しかし、これを実装するべく、LINE PayテーブルとPayPayテーブルを作成しようとすると、大きな問題に直面するでしょう。それは、デフォルトの支払い方法をどのように設定するか、です。

このあたりから、一気に仕様が難しくなってきます。考慮すべきはusersテーブルだけではありません。ユーザ情報と売上テーブルとの紐づけも想像してみてください。従前、売上テーブルにクレジットカードidがそのまま登録されているとしたら、支払い方法の多様化によって変更が必要になるでしょう。途中まではシンプルで良かったように思えるテーブル設計が、サービスの拡大や変化によって一気に破綻してしまいます。

この例では 支払い方法 が重要なキーワードです。支払い方法は実際にはクレジットカードだけでなく、コンビニ払いやQRコード支払い、Suicaなど多種多様ですし、将来的には仮想通貨払いといった手段が増える可能性もあります。こうした変化に対応するために、最初から次のような設計にしておくことで、来る仕様変更に対応できるようにしておきます。

create table user_docomo_payment
(
    id      bigserial
        constraint user_docomo_payment_pk
            primary key,
    user_id bigint not null
        constraint user_docomo_payment_users_id_fk
            references users
);

create unique index user_docomo_payment_user_id_uindex
    on user_docomo_payment (user_id);

create table user_line_payment
(
    id      bigserial
        constraint user_line_payment_pk
            primary key,
    user_id bigint not null
        constraint user_line_payment_users_id_fk
            references users
);

create unique index user_line_payment_user_id_uindex
    on user_line_payment (user_id);

create table user_default_payment
(
    id                   bigserial
        constraint user_default_payment_pk
            primary key,
    user_id              bigint not null
        constraint user_default_payment_users_id_fk
            references users,
    user_credit_card_id  bigint
        constraint user_default_payment_user_credit_card_id_fk
            references user_credit_card,
    user_line_payment_id bigint
        constraint user_default_payment_user_line_payment_id_fk
            references user_line_payment,
    user_docomo_payment  bigint
        constraint user_default_payment_user_docomo_payment_id_fk
            references user_docomo_payment
);

create unique index user_default_payment_user_id_uindex
    on user_default_payment (user_id);

create unique index user_default_payment_user_credit_card_id_uindex
    on user_default_payment (user_credit_card_id);

create unique index user_default_payment_user_docomo_payment_uindex
    on user_default_payment (user_docomo_payment);

create unique index user_default_payment_user_line_payment_id_uindex
    on user_default_payment (user_line_payment_id);

-- 不要なdefault_usedを削除する
drop index user_credit_card_user_id_default_used_uindex

alter table user_credit_card drop column default_used

このようにどれも最初のその時点では間違っていないように見えるテーブル設計でも、変化に対応していくなかで大きな問題を内包してしまう、というケースが多くあります。

イージーな実装とシンプルな実装

なぜこのような問題になるのでしょうか。データベース設計においてまず認識すべきは、イージーとシンプルは違う、ということです。「イージー」とはいわば「やりやすい対処」「やりやすい設計」です。ここまで紹介したアンチパターンの実装例は“イージーな”実装を優先した結果、最終的に仕様変更に追従することが難しくなってしまいました。（ただし、「イージー＝悪」ではありません。なぜなら、イージーとは、短期的な生産性は高い、ということも意味するからです）

一方の「シンプル」とは「小さな責務を果たす」と表現できます。UNIX哲学にある「一つのことを、うまくやれ」と同様の考え方です。先に紹介した「支払い方法追加」のテーブル設定の例のように、昇華した抽象的な概念を見つけ出すことで、テーブルが増えるものの、INSERTとSELECTだけで表現できる“シンプルな”テーブル設計を実現できました。つまり、シンプルさを維持するコツとは、エンティティのスコープ、責務を小さくすることなのです。

ではどのようにそれぞれを小さく分けていけばよいのでしょうか。

Userと言う名の罠

責務を小さく設計していくということはどのようにやっていくのでしょうか。実は今回ご紹介したusersというテーブル一つとっても、データモデリングのチャンスがあります。「user」を直訳すると「利用者」です。今回のWebアプリケーションにおいてもusersテーブルは利用者のテーブルなので名前として正しいように感じます。しかし考えてみてください。今回の実装しているのは会員機能です。

こう考えるとusersテーブルだけではなくmember=会員という責務も見えてきます。一言にuserと言っても会員、ゲスト、管理者、提携企業など様々な利用者がいます。安易にuserの属性としてuser_typeで種別を持ってしまうと、usersテーブルとJOINする際にuser_typeの種別によって必要なテーブルが代わり、結果、JOINの対象が混在するようになり、責務が肥大してしまう、つまりシンプルさが失われてしまいます。また、テーブルに分けず、usersのカラムとして持った場合は、user_typeによって意味の変わるカラムができてしまい、さらに複雑度が上がってしまいます。

create table users
(
    id        bigserial
        constraint users_pk
            primary key,
    name      text not null,
    user_type int  not null
);

comment on column users.user_type is '1:会員
2:管理者
3:クライアント';

create table admin_log
(
    id      bigserial,
    user_id int not null
        constraint admin_log_users_id_fk
            references users
);

create table client_information
(
    id           bigserial,
    user_id      bigint  not null
        constraint client_information_users_id_fk
            references users,
    company_name text    not null,
    phone_number integer not null
);

create unique index client_information_user_id_uindex
    on client_information (user_id);

このようにドメインにこだわり、一つ一つに対して向き合って、小さな抽象化を繰り返していくところにシンプルさは宿ります。これがまさにデータモデルと向き合う一歩目なのです。

拡張と破綻

会員に関していえば、会員の種別だけでも複数存在するケースもあるでしょう。たとえば一時会員、プレミアム会員、ゲストといった種別（type）が考えられます。さらに、種別それぞれに退会、休会、未払い、仮会員などの状態（status）に関する情報も付加されることもあり、種別、状態の多種多様な組み合わせを表現する必要があります。

これらを表現するためには、memberテーブルに種別を表すtypeカラムと、状態を表すstatusカラムを追加するといった実装が考えられるかもしれません。しかし、データベースには「プレミアム会員が『未払いstatus』の場合、通常会員として扱う」といったビジネスロジックが注入されることが往々にしてあるでしょう。さまざまな要件を詰め込みすぎてしまうと、適切なデータを取り出すためのSQLは複雑になり、 今いる通常会員を数える といった、本来簡単なはずの分析SQLも非常に難解なものになってしまいます。

要件が拡張すればするほど、システムの複雑度は増していきます。イージーな選択肢に基づく拡張の末、破綻する日がやってくるのです。だからこそシンプルな設計を目指していくことが必要なのです。

データベースは変化に弱い

データベースはDELETEやUPDATEなどのSQLは一度commitしてしまうと気軽に元に戻すことができません。またALTERによるカラム追加などに対しても、INSERTなどの前後のアプリケーションの互換性を考慮する必要があり、同様に簡単にDROPすることもできません。これら事象はデイリーで何回もリリースするようなアジャイル開発においてはボトルネックになります。実際にアジャイル開発で起こり得る事例を見ながら考えていきます。

仕様変更とテーブル変更

開発初期の予定と最終的にビジネスとして必要になるプロダクトの形が変わることは珍しくありません。ここでは「会員向けブログサービス」として始めたサービスが予想外にヒットし、「大規模SNS」に発展させる、という仮のストーリーを考えてみます。

この場合、ブログ機能以外にもコミュニケーションのためのQ&A機能や掲示板機能も必要になってきます。ここで注意すべき罠は、「ブログ機能をそのまま転用してQ&A機能や掲示板機能を作ってしまう」といったパターンです。

下記のような構造はどうでしょう。

create table blog
(
    id          bigserial
        constraint blog_pk
            primary key,
    title       text not null,
    writer_name text,
    created_at  timestamp with time zone,
    type        integer
);

comment on column blog.type is 'nullのときはblog
1のときはQAサイト
2のときは掲示板';

create table article
(
    id         bigserial
        constraint article_pk
            primary key,
    blog_id    bigint                   not null
        constraint article_blog_id_fk
            references blog,
    article    text                     not null,
    created_at timestamp with time zone not null,
    updated_at timestamp with time zone
);

create table comment
(
    id          bigserial
        constraint comment_pk
            primary key,
    blog_id     bigint not null
        constraint comment_blog_id_fk
            references blog,
    writer_name text   not null,
    comment     text   not null
);

たしかにそれぞれの機能はメインの文章とコメント欄があれば、あとは表示だけの違いのように見えます。しかし、前述のuserテーブルと同様に、「Q&A機能」と「掲示板機能」は似て非なるものです。サービスが成長すればQ&A機能については称号やベストアンサーのような機能が追加で欲しくなってきます。テーブルを共有しているとコメント欄にさらにブログとは別のカラムが追加されていき、機能拡充によって肥大化していきます。

これとは逆に、「機能が不要になる」というケースもあるでしょう。たとえば、掲示板機能をつくったものの、トレンドではないので廃止する、というを決定がされたとします。こうした場合、廃止される機能に付随するデータをどのように処理するかに注意が必要です。Webアプリケーション上は廃止しても、実際の掲示板データが入ってる元記事テーブルも合わせて成長していたといったケースでは、データサイズが多い場合にDROP COLUMNがロックを取るため、気軽にメンテナンスをすることはできなくなってしまいます。こうしてサービス上は不要なはずのカラムが、いわゆる「歴史的経緯」によって存在し続けることになってしまうのです。

Addで変化に追従する

こうしたさまざまな変化に追従するためには、どのように実装が必要だったのでしょうか。上記のような「変化に追従できない」テーブルの場合、以下のようなコードが散見されることでしょう。

function get_blog($id) {
    return $db->blog->find_by(['type' => null, 'id' => $id]);
}

function do_blog_viewer() {
    if($blog->type !== null ) {
        // blog以外は処理しない
        return;
    }
    (省略)
}

$blog が本当はQ&Aなのかblogなのかは取り出さなければ分からず、度々中身をチェックするif文が必要になってしまいます。このようなテーブル構造に出会ってしまった場合はそれぞれのtypeのClassを作ってプログラム側で丁寧に表現する必要があります。

ここでもusersテーブルと同様に小さくテーブルを追加していくことで対応できます。今回のサンプルでも小さくテーブルを分けたほうがアプリケーションとしてメリットがあります。「blog」「掲示板」「Q&A」がそれぞれ同様のテーブル構造だったとしても、「小さなテーブル」として都度設ける形で実装した場合は、それぞれのテーブルからデータを取り出すことになるので、素直なSQLになります。この場合はORMの恩恵を受けながらシンプルに実装することができます。プログラムと同様に一見同じような構造に見えても、意図的に共通化を避けることで“シンプルさ”を実現できる場合があるのは、テーブル設計についても同様なのです。

このようにアジャイル開発においては機能追加や仕様変更がたびたび発生するものであり、データベースはこうした変化に向き合っていく必要があります。だからこそテーブルを小さく分けてAddで対応していける設計が重要になります。

正規化とSimple is beautiful

ここまでシンプルな実装を目指しましょうと強調してきましたが、「シンプルな実装」とはなんでしょうか。RDBMSを使う上でシンプルな実装のヒントは正規化です。正規化のコツは次のように表現できます。

事実だけを保存する
重複がない
不整合がない
nullがない

これらを意識して設計していくとシンプルな設計に近づいていきます。

また正規化を行う際はここまで説明したとおり、種別と状態を考えることも重要です。ライフサイクルが違うデータは往々にして状態や種別が異なります。場合によってはnullになるようなカラムやUPDATEが必要なレコードは状態を持っている可能性があります。こうしたテーブルが見つかった場合はより深く考察する必要があります。

そして最後にINDEXの数にも注目しましょう。主キーは必ずありますが、外部キー制約とユニーク制約を除いたINDEXは主に検索のために必要なINDEXです。検索のWHEREの対象の数だけそのテーブルの責務が大きいといえ、4つ以上のINDEXが必要な場合も同じく深く考察する必要があります。隠れた状態をWHEREで絞り込んでいたり、種別をWHEREで絞り込んでいるケースが見えてくることがあります。

このようにシンプルな設計を目指して考察を繰り返していくことが重要です。そして同じくらい重要なこととして認識すべきはイージーとシンプルは両立できる、ということです。シンプルを目指し考察を繰り返すことがまさにデータモデリングであり、変化に強い設計につながっていくのです。

終わりに

データモデリングがアジャイル開発に必要な「変化に強い設計」につながっていることは、本稿を読む方の多くに感じていただけていると思います。変化に満ち満ちたアジャイル開発にあっても、シンプルさは十分に追求可能です。ぜひ皆さんも、simple is beautifulを目指し、データベースを洗練させていってください。