NULL排除について

Question

RailsにてベンチャーでBtoCの自社サービス開発をしている初心者です。
前提として、まだ割と小さなアプリケーションです。

対象のテーブルに関して、モデル名は伏せますがシステムの中心となるデータのモデルです(Modelとします)。
これには住所のデータがあり、管理側で入力しユーザーに表示されます。モデルの性質上、住所はほぼ必須です。また住所は一つだけしか紐づけられえません。

他に登場するモデルは、都道府県と市区町村を表すPrefectureとCityがあり、それぞれマスターテーブルとなっています。

さて、Modelの住所データの持たせ方として、今まではprefecture_id city_idといったカラムを直接作成し、マスターテーブルに対する外部キーとしていました。Modelと Prefecture Cityは一対一の関係です。

しかしながら、これらのカラムにはNOT NULLの制約は課されていませんでした。なぜなら、ModelのデータをCSVから大量にインポートするのですが、CSVの都道府県や市区町村のデータでマスターとマッチしない文字列がある場合はNULLにするしかないからです。

先輩によると、これはよくない設計のため以下のようなリファクタリングをすると言われました。
それは、以下のような中間テーブルを作成し、prefecture_idとcity_idを切り出すということです。

モデル名
- カラム名

ModelPrefecture 
- model_id
- prefecture_id

ModelCity
- model_id
- city_id

associationは

Model has_one ModelPrefecture
Model has_one ModelCity

のようになります。
これでNULLを排除できるというわけです。

私はNULL排除の意味もなんとなく知っていましたし、同じアプリケーション内でマスターデータに対して全く同じように設計されている箇所が他にも多くあります。

しかし、私の感覚としては、prefecture_id city_idは必須に近いデータなので、Modelのカラムとしてあるのが感覚的には自然でした。
また、ただprefecture_id city_idのデータがNULLになるという例外のためにこのような実装をしていると無駄に複雑性が増していくのではないかとも思いました。
住所の表示をするコードでdecoratorなどでprefectureやcityの存在確認をするということは変わりませんし、普通にアプリケーションを書けば致命的なエラーに繋がる部分でもないので、なんとなく腑に落ちない感じです。

私はこれ以外のプロジェクトをあまり見たことがないのでわかりませんが、ネットで検索するとNULL許容派の方もいらっしゃるようです。

このケースでは、

• 上のようなテーブル分割を行う
• テーブルは分けず、他の方法で解決する

のどちらが良いのでしょうか？

Answer 1

例えばModelを店舗とします(具体例があるほうがイメージしやすいので)。
店舗には、住所(都道府県IDと市区町村ID、町名番地)があるのは当然ですので、都道府県ID、市区町村IDはNOT NULLであるほうが自然ですね。

ModelのデータをCSVから大量にインポートするのですが、CSVの都道府県や市区町村のデータでマスターとマッチしない文字列がある場合はNULLにするしかないからです。

いやいや、まずDBに存在しない都道府県や市区町村がないかチェックするバッチを作成し、CSVのデータを修正(表記ゆれの修正)したり、住所DBのほうを更新したりといった作業をするべきでしょう。

不正なデータをそのまま入れようとする事自体が間違いだし、そもそもそんなデータを作らないようにするべきじゃないでしょうか？

なお、これは旧住所や合併のたびにデータ更新を苦労してでもやるという前提で言っています。
実際に私も業務でこのような設計で運用しており、定期的に住所マスタと関連テーブルの更新をしています。
20万件くらいの小規模な案件なので、なんとかなっています。

Comments

[namenamenameko]

ありがとうございます。
それでは、「都道府県または市区町村の項目がそもそも欠落している場合がある」時はいかがでしょうか。
yambejp様の回答のように、マスターに「不明」を入れるのが良さそうな気がします。
確かに、私が「最寄駅」モデルを実装したことがあり、こちらはhas_manyなのでテーブルを分けた上でマスターデータを随時更新するという運用にしています。

市区町村に関しても、2000件近くあり統廃合もあるでしょうからデータを更新したりするべきですね。

[mingos]

これも全て要件や運用ポリシー次第です。
施設情報を扱っているので、都道府県または市区町村の項目がそもそも欠落しているなんてデータは作りませんし、登録しません。
きちんと統廃合の対応もやっていますよ。
っていうかそんなにしょっちゅうはないわけだから、定期的に見直しをかけるだけでいいですしね。

私の回答としては、NOT NULLで運用し、テーブルを分ける必要はないという事になります。
不明という項目を入れる事が許容できるのであれば、それでOKだと思います。

[namenamenameko]

ありがとうございます！

Answer 2

ちょっと論点が見えないのですが、NULLと’’が違うことはわかりますよね？

CSVから大量にインポートするのですが、CSVの都道府県や市区町村のデータでマスターとマッチしない文字列がある場合はNULLにするしかない

都道府県を正規化して、当てはまらないものを「その他」とする。
たとえば北海道=1、・・・・、沖縄=47、不明=98、その他=99
不明は住所なし、その他は海外、などルールを決めればよいでしょう。

また市区町村は正規化するとむしろ名称変更や合併時にマスターデータとの
整合性がとれないので、ダイレクトに書かせた上で、明らかにおかしなデータは
NULLではなく’’とすればよいでしょう。

NULL許容派の方

基本的には任意に入力される文字列のNULLは、集計にあまり利用しないので
さほど問題ないとは思いますが、集計用カラムにNULLが入ることは
SQLユーザーの基本姿勢として避けるべきだと思います。

Comments

[namenamenameko]

回答ありがとうございます。
```
都道府県を正規化して、当てはまらないものを「その他」とする。
たとえば北海道=1、・・・・、沖縄=47、不明=98、その他=99
不明は住所なし、その他は海外、などルールを決めればよいでしょう。

また市区町村は正規化するとむしろ名称変更や合併時にマスターデータとの
整合性がとれないので、ダイレクトに書かせた上で、明らかにおかしなデータは
NULLではなく’’とすればよいでしょう。
```
まさにその通りだと思うのですが・・・
yambejpとしては、「テーブルを分ける必要はない」ということでよろしいでしょうか。

[namenamenameko]

市区町村に関してはデータの分析などに使いたいので正確なデータが欲しい？という感じのようです

[yambejp]

>正確なデータが欲しい

であれば、NULLではなく''で十分だと思います

>「テーブルを分ける必要はない」ということでよろしいでしょうか。

むしろ原則必須条件なのですから、外出にするのは集計にデメリットしかないと思います。あくまでも例外をどう管理するかを考えるべきだと思います

[namenamenameko]

ありがとうございます。
納得できました！

Answer 3

RDBMSの大前提は例外を作らないこと、常に正の綺麗なデータが入っていることです。
ファイアボールというアニメでは
「ルールを曲げてしまわれては、それはもはやただの曲がった物でございます。」
と説いています。

これをしっかり守る事により下記のようなメリットを得られます、というかRDBMSの存在意義です。

• RDBMSから出てきたデータは常に信頼できるからバリデーションが不要
• 最小限のSQLやコードで書けるから利用する際のコードが短く綺麗、そして高速に動作する

---

これらのカラムにはNOT NULLの制約は課されていませんでした。

外部キーのNOT NULL制約が剥げてしまい、
Modelはただの曲がった物になってしまいました。

もしちゃんとルールを守っていればModelPrefectureとModelCityは不要でしたね。
従って、上記のテーブルを追加するリファクタリング案は不要です。

ルールを守らないで出来た不備を継ぎ接ぎで直してたらどんどん糞なシステムになってしまいますので、
出来るだけ綺麗なデータに修復する方向で頑張ってください。

---

CSVの都道府県や市区町村のデータでマスターとマッチしない文字列がある場合はNULLにするしかないからです。

住所があるのになんで都道府県や市区町村がわからないの？
国外に住んでるの？住所不定者？

そうではなく妥当ではないCSVなだけでしょう。
読み込めないCSVを無理やり読み込もうとしているのが今回の一番駄目な所です。

清書されていないCSVファイルの信頼度はユーザーの入力値と同じであり、
ちゃんとした値になるようシステム側が死ぬ気でチェックして守るべきです。

もし、そのCSVが社員の人海戦術等によって作られており、ある程度の曖昧さを許容しなければならないのであれば、
Ruby側で死ぬほど考えてなんとかしましょう。
不正な住所データや郵便番号を探してきたりして、何とか都道府県を見つけて宛がうとか、
どうにも見つからなかったら、不正なデータだけCSVに固めて出した人に差し戻すとか

---

…えっ？であるべき論ではなくて、今すぐなんとかしたい？
しょうがないなぁ…じゃあ私だったらこう動くってやり方を共有するよ。

まずはPrefectureとCityのマスターテーブルに「見当たらなかった」用のUnknownレコードを追加しよう。
日本語名称は空文字がいいかな？falsyだし。
それと共に現行のModelの問題のレコードをunknown_idに書き換え、これでNot Null制約を付与できる。

短期的にはCSV登録時見つからなければUnknownIDを付与するよう作り変えるといいね。
長期的に不正なCSVが送られてくる状況ははっきり言ってまずいのでバリデートをしっかり作り込むのは必須。
ToDoやタスクのカードに登録しておこう！

Comments

[namenamenameko]

ありがとうございます。
確かにその通りですね。
「PrefectureとCityのマスターテーブルに「見当たらなかった」用のUnknownレコードを追加」というのはとても現実的な解決策のように見えます。
CSV側のバッチ側をしっかりと組むという視点が抜けていました。

[miyabi-sun]

私を含め3人全員が同じような事を発言しているので、
それも踏まえて先輩に相談しにいってみてください。

Answer 4

意見の大筋は今までの3人の方全てに同意です。少し別の視点を

• 「解析不能」を示す id を追加し、定期的にお客様とすり合わせをする

miyabi-sun さんの

不正な住所データや郵便番号を探してきたりして、何とか都道府県を見つけて宛がうとか、
どうにも見つからなかったら、不正なデータだけCSVに固めて出した人に差し戻すとか

と同じような意見ですね。解析不能は「良くない」のですが、利便性を考えて登録は許容し、あとでシステムユーザに通知して、改善を促したり、解析プログラムに不具合があれば、プログラム側の改修が必要という考え方です。

統廃合については

• 新規 Prefecture や City をユーザが追加できるようにする
• マスターとマッチしない文字列がある場合は登録させない
• Prefecture や City を統合・別名に変更可能にする

ことで、ユーザに対応させる手もあるかもしれません。3つ目の要件がないと「同じはずなのに、別の id を振ってしまった」場合に困ります。

改修の難易度はあがりそうですが

• 別名を管理できるようにし、プログラムが頑張る

こともあるかもしれません。

いずれにせよ、

都道府県または市区町村の項目がそもそも欠落しているなんてデータは作りませんし、登録しません。

というご意見が全てかと思います。

Comments

[namenamenameko]

ありがとうございます。
CSVデータに関しては、「お客様のデータを許可を得てスクレイピングしてきたもの」になるのでこちらで処理はできるのですが、性質上スクレイプした文字列の信頼度がかなり低いようです。
これをバッチ(rake task)で直接インポートしています。
個人的には解決策は、
- 「解析不能」を示す id を追加
- 不正なレコードを振り分けるバッチを別に作成し、不正レコードの処理法を考える
- スクレイピングをできるだけ改善
のいずれかと考えています。
先輩の考えとしては、データを完全にスクレイプできない中で「不明」を示すidは作りたくないかつNULLを排除したいということのようです。「不明」を示すidを作るという解決は良い設計ではないと言われました。
Cityの統廃合に関しては、適宜マスターのnameカラムのみを更新する形で対応できるかと思います。

[takotakot]

(敢えて申し上げると) 不明と NULL は、見方や設計方針がちょっと違うだけで、同じことな気がします。

City については、name 更新では(文字列しか見ていない場合を除いて)本来はおかしいはずです。

[namenamenameko]

確かに、「不明」というのはDBの仕組み上はNULL排除できていても、概念としてはNULLに近いですよね。
一般的には「不明」も排除する努力をするものなのでしょうか。

[takotakot]

使用を定義する段階でどこまで議論できるか…もあると思いますが、不明も排除できるのであれば、排除することが望ましいと考えられます。
設計・柔軟性・利便性・堅牢性 いろんなものを考えると思います。