データベース正規化でデータの時限爆弾を解体

今回のテーマは、データベースの正規化です。 はい。 なんだか専門用語で、自分には関係ないかなーなんて思いますよね。 まあ、そう聞こえますよね。 でもこれ、要するに情報の断捨離というか、デジタルな整理整頓術なんですよ。 えー、まさに。 今回参照するのは、その要点をまとめた解説テキストです。 で、早速なんですけど、一つ想像してみて欲しいんです。 はい。 スーパーで買い物したあとのあの、長いレシート。 ああー、ありますね。 そこには店名、日付、買った商品と値段がずらーっと並んでますよね。 あれを全部一つの大きな表に書き込んでいく。 一目で見られて便利だと思いませんか？ その考え方、一見するとすごく合理的ですよね。 はい。 でも実は、データの中に時限爆弾をいくつも仕掛けているのと全く同じ状態なんです。 爆弾ですか？ ええ。 情報をわざわざテーブルに分けるっていうのは、管理を面倒にするためじゃないんです。 ほう。 むしろ将来の自分を、そしてチーム全体を圧倒的に楽にするための知恵なんですよね。 楽にするためにあえて分ける、なんだか逆説的で、がぜん興味が湧いてきました。 ええ、この問題は実は今に始まったことではなくてですね。 はい。 ソースによれば、この考え方の基礎が生まれたのは1970年代。 企業が紙の台帳からコンピューターに記録を移行し始めた時代です。 ああ、なるほど。 それまで人の目でなんとなく修正できていたデータの矛盾が、コンピューター上だともう、致命的なエラーを引き起こしたわけです。 在庫リストの入力ミス一つで全部が狂ってしまう、みたいな。 まさに。 そこでエドガー・コッドという研究者が、データの安全性を保つための数学的なルールを確立した。 それが正規化の始まりです。 なるほど、デジタル化によって問題が深刻になったからこそ生まれた考え方なんですね。 そういうことです。 では、ソースにある具体的な例で、その爆弾がどんなものか見ていきましょうか。 お願いします。 あなたの目の前に、大学の成績を管理する巨大な表があると想像してください。 はい。 そこには学生の名前、学籍番号、受講科目名、科目コード、担当教授の名前、教授の部屋番号、そして成績が全部こちゃっと一つの表に入ってます。 なるほど。 一人の学生が5科目取っていたら、その学生の名前とか学籍番号が5回ずらーっと縦に並ぶわけですね。 そうですそうです。 でも、フィルターをかければすぐに見られて便利そうですけど。 では、もしA教授が研究室を引っ越して、部屋番号が301号室から505号室に変わったらどうしますか？ ああ。 A教授の授業を受けている学生全員の行を探し出して、部屋番号のセルを一つ一つ手で505に修正しないといけない。 その通りです。 もしA教授が100人の学生を教えていたら、100箇所のデータを書き換える必要があります。 うわあ。 想像してみてください。 その作業の途中で電話がかかってきて、85人分まで修正したところで中断してしまったら。 うわあ。 同じA教授なのに、ある行では505号室、残りの行では古いままの301号室、とデータに矛盾が生まれてしまう。 そうなんです。 どっちが正しい情報か分からなくなりますね。 それが「更新異常」という一つ目の爆弾です。 たった一つの修正漏れがデータの信頼性そのものを破壊してしまう。 なるほど。 今回の目的は、この非常に危険な爆弾を3つのステップで丁寧に解体していくことなんです。 了解しました。 では、早速爆弾の解体を始めましょう。 最初のステップ、最初のワイヤーカットはどこから手を付ければいいんでしょうか？ 最初のステップは、最もシンプルで、最も基本的なルールです。 「第1正規化」と呼ばれます。 第1正規化。 ええ。 これは一つのセル、つまりマスの中に、複数の情報を入れない。 という、ただそれだけのルールです。 1マス1データ、ですね。 まさに。 でもこれ、意外とみんなやってしまうんですよね。 あ、わかります。 ソースの例も分かりやすいですが、例えば顧客リストの電話番号の欄に、03の1234の5678会社、090の1111の2222携帯みたいに詰め込んじゃう。 やってしまいますね。 備考欄に火曜日にフォローアップ、猫好きとかコンマで区切って書いちゃったり。 そうですよね。 でも後から上司に「携帯電話の番号だけリストアップして」とか「猫好きの顧客リスト作って」と言われたらどうでしょう？ んー、面倒ですね。 そのセルの中から必要な情報だけを取り出すのは。 ええ。 データがコンピューターで処理できる形になってないんです。 ですから、まず大原則として、一マスには一つのデータだけ。 はい。 これが第1正規化の鉄則です。 すべての爆弾解体の、まあ基本の「き」ですね。 なるほど。 1マス1データ。 これは直感的で分かります。 ごちゃごちゃした引き出しの中から、まず同じ種類のものをちゃんと分ける、みたいな。 ええ、そんなイメージです。 でも、正直これだけだとまだ爆弾解除というほど大げさな話には聞こえませんね。 おそらく本当の罠はこの先にあると。 ええ、その通りです。 ここからが本番ですよ。 おっ。 次のステップ、第2正規化に進みましょう。 はい。 ここで重要になるキーワードが2つあります。 これはもう、セットで覚えてください。 セットで。 「主キー」と「依存」です。 主キーと依存。 そうです。 先ほどの学生名簿の例に戻りましょう。 あの巨大な表で、ある一行を完全に特定するためには、何の情報が必要でしょうか？ えーっと、学籍番号があれば個人は特定できますね。 でもそれだけだとどの科目の成績かわからない。 ですよね。 ではその学生のどの科目の成績かを知るには？ 学籍番号と、どの科目かを示す科目コード。 この2つがセットになって初めて、どの学生のどの科目の情報かという1行が完全に特定できるわけですね。 ええ、まさにその通りです。 そして、データベースの世界では、その1行を完全に特定できる情報の組み合わせに特別な名前を与えています。 それが主キーなんです。 主キー。 ええ。 この例では学籍番号と科目コードの2つで一つの主キーを構成しています。 さてここからが核心です。 はい。 あなたに投げかけたい問いはこれです。 その表の中にある他のデータは、本当に主キーの全体にべったり依存していますか？ 主キー全体にべったり依存。 面白い表現ですね。 えーっと、例えば「成績」というデータはどの学生かとどの科目かの両方が決まらないと一つの点数に決まりませんよね。 はい。 これは、主キー全体にべったり依存していると言えそうです。 その通りです。 完璧な依存関係ですね。 では、学生の氏名はどうでしょう？ あ、氏名は、学籍番号さえ決まればもう決まってしまう。 そう。 どの科目かという科目の情報は、氏名を決めるのに関係ないですね。 そうなんです。 氏名は、主キーの一部である学籍番号にしか依存していない。 これをソースでは「部分関数従属」、もっと分かりやすく言うと「中途半端な依存関係」と表現しています。 なるほど、中途半端な依存。 主キー全体を見ておらず、その一部だけを見ている状態ですね。 待ってください。 でもそれって、逆に面倒じゃないですか？ と言いますと？ 学生の名前を知りたいだけなのに、もし氏名を別の表に移してしまったら、わざわざ学生マスターみたいな別の表を見に行かないといけないんですよね。 ええ。 一つの表にまとまってたほうが探すのは楽な気がしますけど。 良い質問ですね。 そこが多くの人が引っかかるポイントなんです。 あ、やっぱり。 短期間に見るだけなら、一つの大きな表のほうが楽に感じるかもしれません。 しかし、データを管理・維持するという長期的な視点で見ると、その楽さは非常に危険なんです。 ほう。 氏名が成績の表に残っていると、一人の学生が5科目取っていれば同じ名前が5回記録される。 もしその学生が結婚して苗字が変わったら？ あー。 5箇所すべてを正確に直せますか？ 一つでも直し忘れたらまた更新異常の爆弾が爆発します。 なるほど。 目先の閲覧のしやすさとデータの正確性を天秤にかけるわけですね。 確かに、修正漏れのリスクを考えたら分けたほうが安全ですね。 そういうことです。 第2正規化はまさにその作業。 こうした主キーの一部にしか依存していないデータを、別の表に追い出すんです。 追い出す。 ええ。 具体的には学籍番号と氏名だけの「学生マスターテーブル」を新しく作る。 そして、元の大きな表からは氏名を削除します。 ふむふむ。 これで、学生の名前は学生マスターで一元管理される。 名前が変わっても、直すのはたった一箇所で済むんです。 理解しました。 主キーであるボス二人組がいるのに、片方のボスの顔色しか見ていないデータは、別の部署に移動させると。 ははは、そんな感じですね。 これでテーブル内の風紀はかなり引き締まった気がしますが、まだ何か見落としがあるんでしょうか？ はい。 最後の仕上げが残っています。 これが第3正規化です。 さらに細かく、さらに綺麗にしていくんですね。 ええ。 第2正規化で主キーに対する余所見はなくなりました。 全てのデータは主キー全体にちゃんと依存するようになった。 しかし、まだ表の中に巧妙に隠れた爆弾が存在する可能性があるんです。 まだあるんですか？ 一体どんなタイプの？ 最後のステップは、主キー以外のもの同士で勝手に依存関係にあるデータを見つけ出すことです。 主キー以外で勝手に。 ええ、ソースの例が秀逸ですね。 主キーとは直接関係ないところで勝手にカップル成立しているデータ。 これを探します。 あー、なるほど。 例えば先ほどの学生と科目の表に、教授の名前と教授の部屋番号というデータがありましたよね。 はい。 どの教授が担当か、部屋はどこか、という情報ですね。 では、教授の部屋番号というデータは何によって決まりますか？ それは、教授の名前が決まれば部屋番号も一つに決まりますね。 そうです。 教授の名前と教授の部屋番号の間には、明確な依存関係があります。 しかし、このカップルは、この表の主キーである学籍番号と科目コードと直接関係があるでしょうか？ ないですね。 誰がどの部屋にいるかという教授の情報は、学生が誰かとかどの科目かとは全く別の次元の話です。 その通りです。 このように、主キーを介さずに主キー以外の項目同士で成立している依存関係。 要は、テーブル内に紛れ込んだ部外者のカップルです。 部外者のカップル。 このカップルも、主キーに依存しているとは言えません。 だからこれも別の表に分けて独立させるんです。 教授マスターテーブルを作って、教授の名前と部屋番号をそこに移す。 これが第3正規化。 なるほど、これも一種の重複排除なんですね。 同じ教授が100人の学生を教えてたら、100回も同じ部屋番号を記録することになる。 そうなんです。 もし教授が引っ越したら、また全学生100人分のデータを直して回らないといけない。 これも最初に話した「更新異常」爆弾そのものじゃないですか？ 御名答です。 ここまで第1、第2、第3正規化を行えば、データの矛盾という爆弾はほぼ完全に取り除くことができます。 ふむ。 データの重複がなくなり、更新が必要なときはたった一箇所を直せば、それに関連する全ての情報が正しく更新される状態が作れるのです。 つまり、一見すると一つの大きな表をわざわざバラバラに分割しているように見えて、実は一箇所直せば関係各所に自動で正しく反映されるという最強の布陣を作っていたわけですね。 まさに。 それが正規化のゴールです。 一つ一つの情報は、あるべき場所にただ一つだけ存在。 そしてそれらの情報は、キーを通じて互いに関係付けられている。 無駄がなく矛盾もない、非常に美しく、強固な構造です。 なるほどなー。 ただ、この美しい構造にもトレードオフはあります。 トレードオフですか。 これだけメリットを聞くと完璧な方法に思えますが。 ええ。 トレードオフは情報を取得する際の速度です。 速度。 例えば、ある学生の名前、彼が取っている科目名、その担当教授の名前を一目で見たい時。 正規化されたデータベースは複数のテーブルを見に行き、それらを結合して一つの結果を返す必要があります。 あー、なるほど。 この結合という処理には、ほんの少しですがコストがかかるんです。 いろんな引き出しを開けて必要なものを集めてくる手間がかかる、みたいな感じですかね？ そうです。 なので、例えば何十億件ものログデータを分析するような、とにかく読み込み速度が最優先される特殊なシステムでは、あえて正規化を崩す非正規化という戦略を取ることもあります。 へえー。 ただ、それはあくまで例外的なケース。 あなたが日常的に扱うほとんどのデータにとっては、正規化によるデータの整合性のメリットが、速度のデメリットを遥かに上回ります。 データの重複をなくし矛盾の発生を防ぐ。 最初に抱いていた、分けると面倒くさそうというイメージが、180度変わりました。 そして、この考え方はデータベースの専門家だけのものではないということをあなたに伝えたいんです。 はい。 これは、情報をどうやって構造化し、その信頼性をどう保つかという非常に本質的な思考法なんですよ。 日常生活にも応用できるということですか？ もちろんです。 例えば、あなたが仕事で使っているエクセルのスプレッドシート。 はい。 ある列には顧客名、別の列にはその顧客の会社名、さらに別の列には会社の住所が入っているとします。 もしその会社が移転したら、あなたは住所を一つ一つ手で書き換えていませんか？ ドキッとしますね。 間違いなくやっています。 コントロールプラスFで検索して、一個ずつ。 それはまさに更新異常の爆弾を抱えている状態です。 私も昔、あるプロジェクトで共有されていた巨大なエクセルファイルで痛い目に遭いまして。 あ、そうなんですか？ 誰かが良かれと思って会社名の株式会社を株に修正したせいで、月の売り上げ集計で使っていたVLOOKUP関数が一部エラーになって、原因特定に半日かかったことがあります。 わあ、それは辛い。 あれこそまさに正規化されていないデータが引き起こした事故でした。 身に覚えがありすぎます。 正規化の考え方を使えば、顧客リストのシートとは別に会社マスターというシートを作って、会社情報はそこで一元管理すべきだという発想に至るはずです。 なるほど。 そうすれば、会社の住所変更は会社マスターのたった一行を修正するだけで完了しますから。 個人の情報整理、例えばスマートフォンの連絡先の管理など、あらゆる場面でこの視点は役立ちますよ。 正規化の3ステップ、いかがでしたでしょうか？ ここで一つ、考えてみてください。 あなたの身の回りにある情報。 例えば日々の業務で使うスプレッドシートや、個人の趣味のコレクションリスト、スマートフォンの連絡先の中に、今回話したような更新異常の爆弾は潜んでいないでしょうか？ 主キーの一部にしか依存していないデータや、主キーとは無関係に成立している部外者のカップルはあなたのテーブルに紛れ込んでいませんか？ もし、そこにこのデジタルの断捨離を適用して、一つ一つの情報にあるべき一つの住所を与えたとしたら、一体何が起きるでしょう？