nullは撲滅？

Question

###前提・実現したいこと
日々の仕入データに対して、任意のタイミングで別システムへの連携（CSV出力）を行う仕組みを検討しています。

（仕入データ）
仕入No 仕入日付 数量 単価 商品CD CSV出力No
1      5/1  10 100  102   1
2      5/3  20 300  105   1
3      5/12  5 100  102   2
4      5/10  30 500  101   
5      5/3  -10 300  105   

CSV出力Noに数字が入っていれば、別システムへ連携（CSV出力）済みを意味します。
なお、性能のためにCSV出力データも別に保持します。

（CSV出力データ）
CSV出力No 件数 合計金額
1      2   7000
2      1    500

仕入データのCSV出力Noカラムについて、どのように設計するか悩んでいます。

###案１ nullを許容する

（仕入データ）
仕入No 仕入日付 数量 単価 商品CD CSV出力No
1      5/1  10 100  102   1
2      5/3  20 300  105   1
3      5/12  5 100  102   2
4      5/10  30 500  101  null
5      5/3  -10 300  105  null

###案２ nullの代わりに0を入れる。

（仕入データ）
仕入No 仕入日付 数量 単価 商品CD CSV出力No
1      5/1  10 100  102   1
2      5/3  20 300  105   1
3      5/12  5 100  102   2
4      5/10  30 500  101   0
5      5/3  -10 300  105   0

###案３ 仕入データに持つのではなく別テーブルに切り出す。

（CSV出力Noデータ）
仕入No CSV出力No
1      1
2      1
3      2

どのようにするのが良いでしょうか。

Answer 1

kaputarosさん、ttyp03さんとも案3・別テーブルを作るのはまずやらないとはおっしゃっていますし、私もまずその選択肢は選びませんが、一点認識しておくべきは、この案3が教科書的には最も正しい方法であるということです。
http://nippondanji.blogspot.jp/2013/11/blog-post.html
DBスキーマの正規化において、「NULLが登場するカラムがあるなら、それは別テーブルに切り出すべき情報なのだ」は基本中の基本です。そのことをきっちり押さえた上で、それ以外のもう少しやりやすいやり方（もしくはパフォーマンスの出るやり方）を選んでいく必要があります。

案1と案2の違いは、「どんなエラーを未然に防ぎたいか」によって決まってきます。
案1・NULLを使用するのはどんなエラーを起こし得るか？ それはWHERE句の書き間違いです。NULLは通常の値とまったく異なる挙動（NULLの伝播）を起こしますから、ちょっと複雑な問い合わせロジックを書こうとしたときに意図と異なる挙動を引き起こすことがあります。
案2・0を代入するのはどんなエラーを起こし得るか？ それは、存在しないCSV出力番号を誤って格納してしまうこと、データ不整合です。0というCSV出力データにないIDを持とうとすると、テーブル間の外部キー参照がかけられなくなりますから、不整合を未然に防げません（案1や案3ならば外部キー参照をかけることが前提になります）。

WHERE句の書き間違いが怖いか、データ不整合をチェックできないことが怖いか、それによって案1と案2を選択してください。どちらも怖い、となったら迷わず案3です。

Comments

[jk233]

リンク先が大変勉強になりました。
SQLを書いた経験が乏しいため、WHERE句の書き間違いの方が怖いです。
今回は案２で行こうと思います。

[yuba]

SQLに強くなるために押さえておきたいエンジニアの人は二人いて、一人がこのブログの奥野幹也さん、もう一人がミックさんといって、おそらく「NULL撲滅」というタイトルを付けているということはミックさんのウェブサイトはもうお目を通しておられるのでしょうね。
お二人とも書籍・雑誌の執筆がいろいろあります。書店で手に取ってみてください。

Answer 2

案１～３、いずれの案でも問題ないと思います。
ただ案３は冗長な気がするので、自分では選択肢に入れません。
さて、質問としてはNULLを使った方が良いのかどうか、ということでしょうか。
撲滅、とまで言っているので、NULLの存在意義のようなことでしょうか？

例えば今回のように0は未出力、0以外は出力済み、とフラグのように定義づけられるものはNULLを使わなくても良いでしょう。
金額などはどうでしょうか。
金額の場合、0円という値と、未入力（登録）という値は別物ですから、NULLを許容するケースもあるでしょう。
もちろん金額の場合でも、今回の質問のように、入力済みフラグみたいなものを設ければ、0と入っていても未入力扱いにすることはできます。
どちらが使いやすいかは、作ろうとしているシステムによると思います。

Comments

[jk233]

CSV出力Noが0になることは理論上ないので、「0は未出力」というルールにしようと思います。
ありがとうございました。

Answer 3

別テーブルである必要はないと思います。
１つのテーブルで済んだほうが、副問い合わせが減ってSQLの処理速度も速くなりますし。

nullだと想定外の動きをすることもあるそうで。
データベースのINT型項目にNULLはNG?

入力区分のカラムを追加して、そこで判断するのも手かと思います。

Comments

[jk233]

入力区分のカラムを追加することを検討してみましたが、（メリットもあると思いますが）冗長になってしまうような気がするので、今回は見送ろうと思います。
ありがとうございました。