質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

91.35%

  • Ruby

    5176questions

    Rubyはプログラミング言語のひとつで、オープンソース、オブジェクト指向のプログラミング開発に対応しています。

  • Ruby on Rails

    5053questions

    Ruby on Railsは、オープンソースのWebアプリケーションフレームワークです。「同じことを繰り返さない」というRailsの基本理念のもと、他のフレームワークより少ないコードで簡単に開発できるよう設計されています。

  • MySQL

    4382questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    1692questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • PostgreSQL

    720questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

NULL排除について

解決済

回答 4

投稿 2017/12/07 18:04 ・編集 2017/12/07 18:42

  • 評価
  • クリップ 5
  • VIEW 220

nameko_moto

score 176

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

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

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

さて、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許容派の方もいらっしゃるようです。

このケースでは、

  • 上のようなテーブル分割を行う
  • テーブルは分けず、他の方法で解決する
    のどちらが良いのでしょうか?
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

+6

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

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

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

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

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

投稿 2017/12/07 18:36

編集 2017/12/07 18:39

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/07 18:49

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

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

    キャンセル

  • 2017/12/07 18:53

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

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

    キャンセル

  • 2017/12/07 18:56

    ありがとうございます!

    キャンセル

+4

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

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

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

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

NULL許容派の方

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

投稿 2017/12/07 18:26

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/07 18:40

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

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

    キャンセル

  • 2017/12/07 18:41

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

    キャンセル

  • 2017/12/07 18:45

    >正確なデータが欲しい

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

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

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

    キャンセル

  • 2017/12/07 18:55

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

    キャンセル

checkベストアンサー

+3

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

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

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

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

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

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

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


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

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

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

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

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


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

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

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

投稿 2017/12/07 18:41

編集 2017/12/07 19:13

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/07 18:54

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

    キャンセル

  • 2017/12/07 19:15

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

    キャンセル

+2

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

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

miyabi-sun さんの

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

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

統廃合については

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

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

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

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

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

いずれにせよ、

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

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

投稿 2017/12/12 11:08

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/12 11:35

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

    キャンセル

  • 2017/12/12 11:56

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

ただいまの回答率

91.35%

関連した質問

同じタグがついた質問を見る

  • Ruby

    5176questions

    Rubyはプログラミング言語のひとつで、オープンソース、オブジェクト指向のプログラミング開発に対応しています。

  • Ruby on Rails

    5053questions

    Ruby on Railsは、オープンソースのWebアプリケーションフレームワークです。「同じことを繰り返さない」というRailsの基本理念のもと、他のフレームワークより少ないコードで簡単に開発できるよう設計されています。

  • MySQL

    4382questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    1692questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • PostgreSQL

    720questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。