データは正規化しよう

April 03, 2021

今回はデータの正規化に関わるお話。RDBの設計でよく聞く言葉ですよね。データ正規化の目的はデータの「独立性」を高めて、データ間の「不整合」を生じないようなデータ構造を作ることが目的だとされています。

よく見る例として、非正規系のスプレッドシートのデータからグループに分け第一正規系から第二、第三正規系へと進めていくものがあります。しかし、それとはすこし違い、ユニークなIDつまりは1つのカラムに入るデータの正規化についてです。

テーブル設計だけに正規化が必要ってことではないんだと、1種類の文字列でもビジネスに影響を大きく与えてしまう可能性があるっていうことをラーメン店を全国展開している会社の基幹システム設計の例を通して楽しんでいただければと思います。

基幹システムとはこういうものだ

K001mmoe

システムB担当:
「この暗号はなんですか?」

シニアエンジニア:
「新システム用のIDの仕様例だ。これだとすごく拡張性が高くてシンプルに使える。」

システムB担当:
「この前おっしゃっていた、現行システムA/Bを集中管理できる新基幹システム開発の仕様が決定されたんですか。K001の部分はシステムBで使っている、京都支店の001番目のラーメンを取り扱うということですよね?mmoeは何を意味しているのでしょうか?」

シニアエンジニア:
「そのとおり、そしてmmoeっていうのはメンマ・温泉卵トッピング付きっていうこと。すごくないか、IDだけでラーメンのトッピングまで把握できるんだぞ!」

システムB担当:
「なぜ、システムAのラーメンIDとトッピングの種類をくっつけたんですか?」

シニアエンジニア:
「管理がシンプルで拡張性が高くなるからと説明しただろ?システムBではトッピングは1個しか選択できないが、このIDであれば複数トッピングという機能がユーザーに提供するのも簡単だ。」

システムB担当:
「たしかに、複数のトッピングに対応はしていないですけど、それはラーメンを楽しんでもらうのであってトッピングで勝負するのは良くないという要望だったからでしたよね?それに実装上ではトッピングはラーメンとは独立していて、ユーザー注文時のUIを変えれば複数表示にも対応できるようになっているので、ラーメンとトッピングを結びつけたIDじゃなくても大丈夫です。」

SystemA orders

シニアエンジニア:
「⇑はシステムBのデータ設計か?新システムIDのほうを使えば注文トッピングが必要ないじゃないか、やっぱりこっちの方がシンプルだ。なにせ拡張性も高いからな。」

システムB担当:
「(これで拡張性高いとはどういうことだろう?シニアエンジニアがいっているんだからそうなんだろうけど、どう質問すればいいのかな)」

システムB担当:
「それではシステムA/Bはどうやってラーメンの種類を基幹システムから取得することになるんですか?」

シニアエンジニア:
「まずは基幹システムの方で、各支店のラーメンメニューを登録する。トッピングはメンマ・温泉卵・ネギの三種類のみだから、全部の組み合わせは「単体3種類」「2個選び3種類」「全部のせ1種類」の合計7種類に対して、ラーメンの種類毎にさっきの7種類のトッピングをつけたラーメンメニューが担当者によって作成されるんだ。つまりラーメンが3種類あれば、21種類のラーメンメニューが登録される可能性がある。だが、各支店の売上データをみながら売上がすくないトッピングがついたメニューは作らないかもしれない。毎朝店長が、システムA/Bの管理画面からラーメンメニュー同期ボタンを押すと最適なメニューが送られるんだ。これで余分な仕込みもしなくてすむ」

システムB担当:
「つまりは、担当者は同じラーメンの内容をトッピング組み合わせ毎に登録するということでしょうか?DBにもほとんど同じ内容の情報が登録されてしまいますよね?ラーメンメニューデータはこんな感じだと思いますが大変ではないですか?」

ラーメンID タイトル 価格
K001 醤油ラーメン 600
K001mmoe 醤油ラーメンメンマ温泉卵のせ 650
K001mmoeng 醤油ラーメンメンマ温泉卵ネギのせ 670

シニアエンジニア:
「醤油ラーメンの内容をコピー&ペーストすれば問題ないだろう。これぐらい運用でカバーできる。それに基幹システムはデータが多くなって当然なんだよ。」

システムB担当:
「・・・・それでは同期ボタンを押したときに基幹システムから返ってくるラーメンメニュー同期用データはどんな形式になるんでしょうか?」

[
  {"ramenID": "K001", "price": 600},
  {"ramenID": "K001mmoe", "price": 650},
  {"ramenID": "K001mmoeng", "price": 670},
  {"ramenID": "K003ng", "price": 710},
  {"ramenID": "K003oeng", "price": 730},
]

シニアエンジニア:
「こんな感じになるな。」

システムB担当:
「(・・・どうやってトッピングの種類を取得すればいいんだ・・・。システムA担当にも相談しないと)」

システムB担当:
「分かりました。考えてみますので少しお時間ください。」

それは基幹システムの役割じゃない

システムB担当:
「先日はありがとうございました。システムA担当とも相談してきたんですが、さっきのラーメンメニュー同期用データの形式をかえてもらえたらなと思っています。」

シニアエンジニア:
「どういうことだ?」

システムB担当:
「ご存知だと思いますが、システムAは本店しか使っておらず、トッピングなしのラーメンをアプリから注文できるっていうだけのものでした。そしてシステムBは、支店が増えることになった際にそれぞれの支店でも簡単に導入して使えるシステムAよりも機能が多いシステムとして作成されています。」

System A/B apps

システムB担当:
「どちらもラーメンとトッピングは独立したデータの種類として使われるので、ラーメンメニュー同期データも分けてもらえないでしょうか?」

[
  {
    "ramenCode": "K001",
    "toppings": ["mm", "oe", "ng"],
    "basePrice": 600
  },
  {
    "ramenCode": "K003",
    "toppings": ["oe", "ng"],
    "basePrice": 700
  }
]

システムB担当:
「このようにしてもらえると、ramenIDの仕様を気にせず、システムAはトッピングのデータを気にぜず定番ラーメンのデータがわかり、システムBも、どのメニューが有効なのかがわかりやすくなります。」

シニアエンジニア:
「そんなのシンプルじゃないじゃないか。ramenIDに全部の情報があるのに、キーが増えているんだから複雑だろ。ramenCodeという概念は基幹システムにはないんだ。そちらのシステムの都合をこちらに持ち込んではだめだ。レイヤードアーキテクチャをしっているか?Domain層(基幹システム)がApplication層(システムA/B)に依存していてはだめだろう?」

システムB担当:
「文字列からトッピングを取り出すんですね。それだと、システムA/Bも同じ処理を組まなくてはいけないのですが・・・それと⇓のような画面を見たのですが」

All toppings

システムB担当:
「この全部のせというのは、K001mmoengっていうramenIDになるのでしょうか?」

シニアエンジニア:
「それは違う。ZenbuNoseとなるだろ?K001znだ。これはif文で条件を書けばすぐにかけるし、同じ処理を書くといっても最初の6文字以降が2文字ずつがトッピングを表しているだけだぞ?それぐらいは簡単なコードだろう。」

システムB担当:
「簡単ですが、ramenIDの仕様を変えないと行けない場合はどうすればいいでしょうか?セットメニューが出てきたりしたら。」

{"ramenID": "K001", "price": 900, "set": {"setID", "gyyk"}},

シニアエンジニア:
「これで醤油ラーメンの餃子焼肉セットが表現できるだろう。シンプルだ。setキーがあるかないかで判断すればramenIDに変更加えなくてもいいだろう」

システムB担当:
「なるほど、ではK001という京都の醤油ラーメンはsetメニューか単品しか注文できないのでしょうか?」

シニアエンジニア:
「そういう場合は、K101という醤油ラーメン餃子焼肉セットっていう新しいIDにするんだ。100件も単品のラーメンメニューができることは無いからな。このIDの仕様だと拡張性高いからなんにでも対応できるだろ。」

システムB担当:
「全店舗で1xxは餃子焼肉セットってことになるのでしょうか?」

シニアエンジニア:
「今はそんなことは仕様にないから気にしなくていいんだ。しっかりレイヤーを意識した設計を考えることを意識しろよ。そうだ、UIもこんな感じでトッピングが複数できるように変更しておいてくれ。」

updated system b app

システムB担当:
「なるほど、複数対応のUIですね。便利そうです。在庫チェックは基幹システム側で行われるんですよね?」

シニアエンジニア:
「もちろんだ。注意点としては、K001のあとのトッピングの種類の順番指定は絶対にmm->oe->ngの順を守ってくれ。メンマ・ネギならmmngっていう順番だ。もしかしてトッピングが増えたり基幹システムの都合上順番が変わるときは教えるからコードを書き換えてくれ」

システムB担当:
「なるほど、基幹システムの変更はシステムA/Bという上位が意識することですもんね。レイヤードアーキテクチャでしたっけ?」

シニアエンジニア:
「そういうことだ。よくわかっているな。」

開発はなんとか終わり本番稼働がはじまりました

在庫の設定にすごく時間がかかっても運用でカバーだ

在庫担当:
「例えば京都支店の一日の在庫では、各ラーメンが50杯ずつ、メンマが50セット、温泉卵が50個、ネギが50セットが用意できるとします。醤油ラーメン、醤油ラーメンメンマつき、醤油ラーメン温泉卵つきとかの在庫を設定するにはどうすればいいんですか?」

在庫担当:
「システムBであれば、各ラーメン、各トッピングの在庫を別々に設定していたんです。」

シニアエンジニア:
「そういうことですか。日頃の売上データをみて、それぞれのラーメンメニューにうまく計算して、在庫を設定するんですよ。醤油ラーメンのトッピングなしが一番人気であれば、30杯を在庫設定して、その他に残り20杯をわけておけばうまくわけれるでしょ?」

在庫担当:
「なかなか細かく設定できるんですが、計算がすごく大変そうですね。たとえば設定ミスで醤油ラーメンの合計が60杯とかになってしまった場合はどうすればいいですか?」

シニアエンジニア:
シンプルな足し算の計算なんだから、何回も見直して、そういうミスが起きないように設定してください。システムをシンプルに保つためにはそれぞれが運用でカバーして助け合わないと。」

在庫担当:
「なるほど、エクセルでしっかり計算表をつくって管理しますね!」

利用するユーザーから見て

カスタマーサポート:
「なぜか、ユーザーからアプリが使いにくいという問い合わせが大変多いです。」

在庫担当:
「なぜか、各支店の送ってくる売上データと、在庫の残量がいつも合いません。これではうまく在庫設定ができないです。」

シニアエンジニア:
「一体どういう使い方をしているんですか。ちょっと実際の支店に調査に行ってきます。」

近くの系列支店を訪問してみたシニアエンジニア

シニアエンジニア:
「店長、味噌ラーメンのトッピングなしが選択できないんですが」

店長:
「お客さん、うちのラーメンははじめてかい?」

シニアエンジニア:
「はい。どうすれば味噌ラーメン単品を注文できますか?」

店長:
「適当にトッピングを選択するんだよ。注文後に本当にほしいトッピングを教えてくれたらいいよ。」

シニアエンジニア:
「トッピングによって料金が変わるんですけど、どうするんですか?」

店長:
「しっかり差分は返しますよ。お手数おかけしますね・・・、ほんとアプリを作った本社の人間を見てみたいよ」

シニアエンジニア:
「(・・・・あ、味噌ラーメントッピングなしは在庫切れだけど、何かしらのトッピングありの味噌ラーメンだと残っているのか・・・)」

おわりに

何やら大きな問題に気づいたシニアエンジニアでした。ここから基幹システムの見直しがされ、関係者が幸せになることを願うばかりです。

さて、今回の例では正規化しないといけない部分がカラムではなく、1カラムに保存されている文字列でした。1種類の文字列でも、ラーメンを示すものとトッピングを示すものの2種類が使われてしまっており、このままだとその2種類の役割から逃れることができなくなるからです。

2種類のものを1つで表現することは、一見管理することが減るので楽になると思ってしまいそうですが、分解するときにどうやって分解すればいいのかというルールや元の2種類のものからつなげて1つのものに表現する時にどういう順番でつなげるのが正解なのかというルールも管理が必要になってきます。

特に、RDBのプライマリーキーになるようなものは影響範囲が広いので、あまりそういったルールや状態をもったデータにしないほうがいいでしょう。

ちょっとでも、設計時に気をつけようと思ってもらえると幸いです。

このようなご相談募集しています

株式会社KASUGAでは、一般のシステムベンダーでは手を出さない以下のような状況をパートナーと一緒に歩んできた多くの経験があります。

  • 肥大化して負債だらけのシステムを大切な機能のみに整理して、担当エンジニアの工数やインフラコストの削減を成し遂げています。
  • 社内IT担当者の経験不足による外部ベンダーまかせのシステム設計で、満足いかない、もしくはベンダーから開発途中で投げ出されてしまったものの再生を手伝っています。

新しいものを作るではなく既存のシステムをコアなところを大切に整理することで、システムの保守する複雑性が下がり、社内の運用コストを大きく減らすことが可能です。

このようなお悩み以外にもシステム開発関連でお困り事やご相談したい事例がございましたら、お問い合わせフォームよりお気軽にご連絡お待ちしております。

ブログを見ていただき、弊社にご興味を持ってくださった方は、ぜひ弊社Webサイトもご覧ください。お仕事のご相談も可能です。