「LAPRASのDB設計についてそーだいさんに相談してみた」イベントレポート

こんにちは！LAPRAS でエンジニアをしていますモロズミ (@Chanmoro) です。

約3ヶ月前になりますが、今年の6月9日に LAPRAS 公開設計レビュー「LAPRAS の DB 設計について」そーだいさんに相談してみた というオンラインイベントを開催しました。

事前収録した動画を参加者の方にご覧いただく形式でしたが、イベント公開から本日まではイベント参加者の方のみに限定公開としていました。しかし、とても参考になる内容をお聞きできたのでより多くのエンジニアの方の参考になればという思いと、イベントに参加ができなかった方から「ぜひ内容を知りたい」というお声を多くいただいていたことから、この度イベントの動画を全ての方にご覧いただけるように一般公開しました。

動画を頭から最後まで全部見ていただける方がイベント中のそーだいさんと私たちの会話の文脈がよりわかるのでオススメではあるものの、全部で約1時間45分という長尺のためそこそこ長いです。

この記事では動画の一般公開に合わせて、この勉強会でどのような内容について話されていたかを把握しやすいようイベントレポートとして勉強会の概要をまとめました。主に取り上げられていたトピック毎に YouTube 動画での該当箇所へのリンクと質問・回答内容の要点を書いていますので、この記事は全体をざっくりと把握するために使っていただき、気になるトピックがあれば該当箇所の動画をご覧いただくような使い方をしていただければと思います。

そして動画をご覧いただいた方はぜひ感想を「#LAPRAS公開設計レビュー」のハッシュタグをつけて twitter で投稿をお願いします！いただいた貴重なご意見ご感想は今後のイベント運営改善のための参考とさせていただきます。

それではここよりイベントレポートです！

イベント開催の背景

LAPRAS ではそーだいさんのブログ記事「ユーザ情報を保存する時のテーブル設計」で解説されているエンティティの状態を表現するためのテーブル設計をそーだいさんへの敬意を込めて「そーだいモデル」と呼んでいて、以前からいくつかの機能の設計に取り入れています。その上で、今の使い方で正しいのか？や実際に適用して課題に感じた箇所がいくつかあり、それならご本人に直接質問させていただこう！という案が出てきたのが事の発端です。そこで弊社 CTO の興梠よりそーだいさんへ連絡したところ、快く引き受けてくださり本イベントを開催することとなりました。

こういった設計の議論をする際に、架空のシステムを題材とすると一般論や理想的な話になりやすいですが、本イベントではなるべく実際の開発・運用でトレードオフも理解した上でどう適用するか？という現実的なノウハウの話をしたかったので、LARPAS 社での実際のコードや設計をベースにそーだいさんへ質問させていただきました。

私たちの取り組みをご理解いただき、出演を快諾してくださったそーだいさんに改めてお礼を申し上げます。

そーだいさんにお聞きしたこと一覧

本イベントではこれらのトピックについて質問させていただきました。

LAPRAS での現状の設計について説明 (約20分)
現状の設計に対するそーだいさんからのフィードバック (約18分)
「ユーザ情報を保存する時のテーブル設計」への質問 (約2分)
状態毎の子テーブルをまたいで検索したい場合の設計 (約13分)
「ユーザ情報を保存する時のテーブル設計」への追加質問 (約2分)
そーだいさんが LAPRAS の求人機能の設計をするとしたら何を考えるか (約10分)
変更履歴を保持したい場合のテーブル設計について (約5分)
実装上の都合でテーブルを非正規化するのはアリか？ (約6分)
データの変更履歴を記録する場合の設計について (約6分)
RDBMS 依存の機能は使って良いものなのか (約6分)
画像データを RDBMS に保存する選択の是非 (約4分)
全体を通しての感想 (約5分)

LAPRAS での現状の設計について説明 (約20分)

より深い議論ができるようにするために LAPRAS の求人機能 (JOB 機能) にスコープを絞って今回の題材としています。求職者のCユーザーが利用する LAPRAS と、求人企業のBユーザーが利用する LAPRAS SCOUTのそれぞれに分けて画面の機能を説明しています。

LAPRAS 求人一覧画面
- LAPRAS ユーザーがタグ検索、職種での絞り込み、未応募の求人への絞り込みをする画面
LAPRAS 求人詳細画面
- LARPAS ユーザーが求人の詳細を参照、求人への応募をする画面
LAPRAS SCOUT 求人の作成・編集画面
- LAPRAS SCOUT ユーザー(企業ユーザー) が LAPRAS に公開する求人を作成・編集する画面
- 求人には公開中、下書き、アーカイブの状態がある

ER図の説明

求人機能に関連したデータについて、今回の勉強会用に主要なテーブルとカラムに絞った ER 図を元にテーブル設計を説明しています。

求人データの親となる JobDescription テーブルを中心として、公開、下書き、アーカイブの状態毎に子テーブルを定義している
求人に紐づく画像、タグ、セクションなどのデータは JobDescription と 1対N で紐づく
最後に公開した時のスナップショットのデータを持っている

現状の設計に対するそーだいさんからのフィードバック (約18分)

説明させていただいた求人機能についてそーだいさんよりフィードバックを頂きました。

JobDescription テーブルは他のテーブルから参照されるところなので変更しない前提になっているか？
- JobDescription の id は big int にしておいた方が良いのではないか
- 親テーブルに update を流さなくて良いように設計する
交差テーブルが少なく感じる
状態ごとに同じ名前のカラムがいくつかあるので別テーブルに切り出す形でもよかったのではないか

フィードバックに対して LAPRAS エンジニアからの質問

質問内容

同じ名前のカラムでも状態によって制約を変えたい場合にどうしたら良いでしょうか？

そーだいさんからの回答

Optional か必須かだけの違いであれば EVA パターンが使える場合がある。その他には制約が違うカラムを状態毎のテーブルにそれぞれ持たせるのはアリ。その場合には状態ごとに必要なテーブルを JOIN した結果をビューやマテビューで参照を簡単にするやり方もできる。また、全てのカラムを Optional にしておいて制約はアプリケーション側でチェックするやり方もあり。

この質問から派生して以下についてもお話いただきました。

制約を DB でやるか、アプリケーションでやるかをどう判断するか
- ※DBリファクタリングの勘所と所感の記事も合わせて参照ください
守りたいデータの制約はテストだけでなく監視も活用して防ぐ

「ユーザ情報を保存する時のテーブル設計」への質問 (約2分)

質問内容

そーだいさんのブログ記事「ユーザ情報を保存する時のテーブル設計」で解説されているテーブル設計について、ここでの状態を表す子テーブルの主キーは親テーブルを参照する外部キーとなっていますか？

そーだいさんの回答

その通りで親テーブルと必ず1対1になることを表している。Rails, Django などの ORM を使う場合はサロゲートキーが付与されるのでこのテーブルするのはやりにくいためこの形にするは必須ではない。 (この設計を妥協して良いかについては別の質問で再度お聞きしています)

状態毎の子テーブルをまたいで検索したい場合の設計 (約13分)

質問内容

複数の状態に対して子テーブルに持つカラムを検索対象として検索したい場合、ORM を使ったクエリではパッと見でわかりにくいコードになってしまうのを課題に感じています。どんな設計の選択肢が考えられますか？

そーだいさんの回答

参照用にマテビューやビューを作成し、ORM のモデルからはそのビューを参照するように設定するやり方がある。Repository パターンを使った実装にしなくても、アクティブレコード型の ORM の仕組みに乗った形で実現できるところがこの方法のメリット。

この質問から派生して以下についてもお話いただきました。

ORM で自動生成されるマイグレーションを使うか、 DML を書いて適用するか
本番環境での DB マイグレーションの適用
サービス停止を伴う DB メンテナンスの対処方法

「ユーザ情報を保存する時のテーブル設計」への追加質問 (約2分)

質問内容

ユーザー情報を保存する時のテーブル設計について子テーブルの主キーを親テーブルを参照する外部キーとするのは妥協しても良いポイントかについて改めてお聞きしたいです。

そーだいさんの回答

ActiveRecord 型の ORM を使った時のようなサロゲートキーを持つテーブル構造の場合でも、親テーブルを参照する外部キーに uniq 制約をつけたり交差テーブルを入れることで1対1とする制約を入れられるので、この部分は必須ではなく妥協できる。

そーだいさんが LAPRAS の求人機能の設計をするとしたら何を考えるか (約10分)

質問内容

LARPAS の求人関連のテーブル設計について、今回共有した内容について考えられる改善点やそーだいさんが設計するとしたらどうするかをお聞きしたいです。

そーだいさんの回答

JobDescription の名前がスコープが大きすぎるように感じるので、本当にそれでいいか？の論理設計を見直すところから始める
求人票に関するデータを、C ユーザーに表示するためのメディアとマスタデータとしての求人票のような構造にテーブルに分けるというやり方もあり
index のチューニングの観点で、update が発生する箇所を極小化するために子テーブルに分けるというのも考える

質問から派生して以下についてもお話いただきました。

テーブルを後からくっつけることは比較的簡単だが、後から分けるのは大変
DB への接続ユーザーをコンポーネント毎や利用者毎に細かく分ける

変更履歴を保持したい場合のテーブル設計について (約5分)

質問内容

変更履歴を保持したいデータについて、update は使わずに insert のみで過去のデータを残す設計にしている箇所がいくつかあります。この設計にした場合に何か問題はありそうですか？

そーだいさんの回答

変更履歴を保持する場合によく使うパターンなので選択肢として問題はないが、この設計が抱える問題点はデータが大きくなることと、最新データを取得する際の手間が考えられる。データが大きくなることについては任意のタイミングで古いデータを削除したり、全ての履歴を保持する必要があれば履歴は別のテーブルへ退避させるやり方で対処できる。最新データを取得する際の手間については先ほど紹介したようなビューを使うことで回避できる。

質問から派生して以下についてもお話いただきました。

ログ用のテーブルを作ってトリガーで insert するようにしておくやり方もある
参照のパフォーマンスがネックであれば更新が少ない場合は参照用のキャッシュを作る場合もある

実装上の都合でテーブルを非正規化するのはアリか？ (約6分)

質問内容

パフォーマンスではなく実装上の都合でテーブルを非正規化するのは許容できるものですか？例えば、join しようとすると多くのテーブルを経由することになるので、ショートカットのための外部キーを持たせておくというような場合です。

そーだいさんの回答

質問内容のような問題を感じるとき、そもそもの正規化に失敗しているパターンがある。例えば同じテーブルへの参照が重複して存在している場合に、本来はデータの内容は同じに見えても別々のライフサイクルで変化するデータを保持しないといけないケースがあり、この場合はそもそもの正規化が間違っている可能性がある。

join の手間が面倒という問題についてはビューを使うやり方で回避できる。ORM によってはリレーションを辿るときに中間のテーブルをショートカットできるような書き方ができるものもあるのでその利用を考えるのも良いと思う。

DB は可能な限り正しく正規化することを意識して、アプリケーション側の都合に合わせる場合はアプリケーション側で吸収できないかを考える方が良い。

質問から派生して以下についてもお話いただきました。

repository パターンを使って参照用のモデルに詰め替えることで、物理データの構造がどうなっているかは隠蔽するやり方もある
アプリケーションは変更しやすいがDB は変更しにくいので、DB テーブルはなるべく正規化した状態にしておく

データの変更履歴を記録する場合の設計について (約6分)

質問内容

データに差分があった場合に変更履歴を残したい場合の設計はどういった選択肢が考えられますか？

そーだいさんの回答

どのカラムに変更があったかを比較したい場合は、比較対象のカラムを結合した値からハッシュ値を計算して新しいデータと比較することで、データの内容に変更があるかを検知するやり方がある。ハッシュ値を使うことで RDB のインデックスが利用できるので変更されたかの検知を高速にできる。このやり方を選択する場合にハッシュ値を別のテーブルに分けるテーブル設計も考えられる。

質問から派生して以下についてもお話いただきました。

外部 API のレスポンスは JSON 型に生データを入れておき、アプリケーションで利用したい箇所のみカラムに切り出すやり方もアリ

RDBMS 依存の機能は使って良いものなのか (約6分)

質問内容

特定の RDBMS に依存した機能を使いたい場合にアプリケーションコードで表現できるものですか？そうでない場合にはどうやってコードと DB の同期を取っていますか？

そーだいさんの回答

前提としてメインの機能ではなるべく RDBMS 依存の機能を使わない方がいいと考えている。トリガーなどの飛び道具を使いたい場合は一時的な利用に抑えて恒久的には使わないようにする。RDBMS 依存の機能に頼らなければいけなくなる場合にはアプリケーションの設計ミスがどこかにあるはず。その設計ミスを一時的にカバーするために技術的負債を選んでいるという意識を持って使う。

一時的な調査のためにトリガーを仕込む場合であればアプリケーションコードを変更せずに適用できることはメリットでもある。恒久的な機能で RDBMS 依存の機能を使いたい場合は、ORM などのライブラリが対応していてアプリケーションコードで表現できるものであれば使っても問題ない。

質問から派生して以下についてもお話いただきました。

RDB だけで済む場合はその他のデータストアは本当に必要になるまでなるべく増やさない
キャッシュ前提の設計をあらかじめ考えておく

画像データを RDBMS に保存する選択の是非 (約4分)

質問内容

先ほど RDB だけで済む場合はなるべく他のデータストアを増やさないという話がありましたが、SQL アンチパターンでは画像などのファイルを RDB に保存にする方が良いとの記述があり、あれは今でも有効なのでしょうか？

そーだいさんの回答

今の時代は S3 などのオブジェクトストレージを使う方がいい。S3 がファイルのバックアップを自動で取ってくれるのでその点でファイルが消えてしまう心配がない。SQL アンチパターンが書かれた時代には Ajax の考慮がほとんどなかったが、今は状況が違ってファイルアップロードは非同期で実行される場合が多いのでその場合のテーブル設計はまた違ったものになる。

全体を通しての感想 (約5分)

LAPRAS のエンジニアからの感想

そーだいモデルへの理解が深まった
- ※ユーザ情報を保存する時のテーブル設計の設計を LAPRAS 社内では敬意を込めて「そーだいモデル」と呼んでいます
RDB とアプリケーションとの住み分けについて経験に基づいたお話を聞けて勉強になった
DB は可能な限り正しく設計するという考え方は自分もそう思うので自信が持てた
DB だけを個別に考えるのではなく運用やアプリケーション全体を総合的に考える視点と言語化がとても参考になった

そーだいさんからの感想

DB はサービスのコアになるので様々なところに影響を与える
ビジネス、サービス、チームなどに広く関心を持ち設計に活かせるのが良いソフトエンジニアだと思っている

イベントレポートまとめ

この記事では一部を抜粋して要約していますが、書き起こしきれていない部分や発言のニュアンスが伝わりにくい部分もあると思いますので、ぜひ動画本編も合わせてご覧ください！

LAPRAS では今回のように「公開設計レビュー」と称し、LAPRAS の開発チームが実際の開発で直面している課題についてその分野の第一線で活躍している開発者の方からフィードバックをいただくイベントを実施しています。私たちの実際の開発から生じている課題やその解決策についてこのようなイベントを通して公開することで、より多くのエンジニアの方々へノウハウを共有できればと思っております。

これからもこのようなイベントを続けていきますので今後もぜひご参加・ご視聴いただけると幸いです。

それでは、最後に繰り返しとなってしまいますが、この記事や動画をご覧いただいた方はぜひ感想を「#LAPRAS公開設計レビュー」のハッシュタグをつけて twitter で投稿していただけるととても嬉しいです！