RDB で テーブルを分割するか NULL を許容するか

Question

テーブルを分割するか NULL を許容するか

MySQL などの RDB の設計で悩んでいます。

NULL は排除できるなら極力排除するべきだという教えが一般的だと思うのですが、許容しても良い判断基準をどのように持てば良いのかが、自分の中でわかっていません。

例えば、ユーザテーブルがあり、結婚した日というカラムがあったとします。ユーザには結婚した人もいれば、していない人もいます。このとき、結婚日を NULL 許可するのか、結婚日テーブルのように分割するのか…。

使い方としては、全ユーザ一覧の中で結婚した人の絞り込み・結婚していない人の絞り込み・今年結婚した人の絞り込みなどの用途があります。つまり、テーブルを分割したとしても結局多く使わる SQL では LEFT OUTER JOIN をすることになるので NULL が入ってくることになります。

これが詳細プロフィールのように任意のデータが複数あるような集合だった場合は別テーブルに分割するという感覚はあるのですが、それが1列の場合はどうなのか。その NULL を含めた列を WHERE などの条件として使うような用途の場合はどうなんだろう…と色々な条件で考えていくとキリがなく。

おそらく色々なケースによって判断されると思うので一概にこっちだとは言えないと考えているのですが、みなさんの判断基準を参考にさせてもらえると助かります。

Answer 1

NULL は排除できるなら極力排除するべきだという教えが一般的だと思うのですが

最近データベースは触ってないので最近の一般論はわからないですが、少なくとも私の中にはそのような考えはないです。
NULL（値がない）という情報が必要なシーンは多数ありますから、状況に応じて使い分ければよいと思います。

で、今回の「結婚日」で言えばNULLを許可でよいと思います。
結婚に付随する情報が多数ある場合、例えば、配偶者の情報や過去の婚姻暦などが必要であれば、結婚テーブルを用意するとよいと思います。

Answer 2

RDB はその概念設計の時点から、NULL を特別な値として扱うことを前提として構築されています。
※true/false の二値論理ではなく true/false/null の三値論理であることとか

ですからデータの構成上 NULL はあってはならないなら排除するのはいいのですが、逆に構成上 NULL があってよいならば、それは正しく NULL を扱うべきだと思います。

今回の例でいえば、未婚の場合は当然結婚日が存在しないのですから、null を許容すべきだと思います。

Answer 3

項目がNullを取りうるかどうかがテーブルを分割する条件とは成り得ません。

また、Nullを排除する必要があるのは必須であるかどうかです。

Nullはインデックスに含まれませんから、
Nullとなる項目に対して、Null値が検索対象となる場合は、
Nullに特定の値を与える事で、インデックスの効果を得る事ができます。

このような事に対して、「NULL は排除できるなら極力排除するべき」という結果だけが浸透してしまったのではないでしょうか。
※逆にNULL値が検索対象から除外が前提なら、Nullのままとした方が、インデックスの件数が少なくなる分効率的です。

Answer 4

結婚した日は個人に属する情報ですから、通常はユーザーテーブルに含めるでしょう。

結婚相談所のシステムなら何度も結婚・離婚しているような相手は避けたいって要望があるから、結婚・離婚の履歴を別テーブルで用意するでしょう。

Answer 5

NULLを排除すべき具体例

趣味のように複数選択ができて、不特定多数のものなどについて

人名	趣味
Aさん	読書,映画鑑賞
Bさん	映画鑑賞,野球
Cさん	釣り
Dさん	なし

上記をまともにテーブルで管理すると

人名	趣味・読書	趣味・映画鑑賞	趣味・野球	趣味・釣り
Aさん	1	1	NULL	NULL
Bさん	NULL	1	1	NULL
Cさん	NULL	NULL	NULL	1
Dさん	NULL	NULL	NULL	NULL

こうなって、これではあまりに無駄が多いです（横に無限に広がっていく）

人名	趣味1	趣味2	趣味3	趣味4
Aさん	読書	映画鑑賞	NULL	NULL
Bさん	映画鑑賞	野球	NULL	NULL
Cさん	釣り	NULL	NULL	NULL
Dさん	NULL	NULL	NULL	NULL

多少効率化をはかってこうしてみても趣味1～4が違う集計項目になるし
一人あたりの趣味の上限が決まってしまうのも具合が悪いでしょう。

これを解消するには

• personテーブル

pid	人名
1001	Aさん
1002	Bさん
1003	Cさん
1004	Dさん

• hobbyテーブル

hid	人名
2001	読書
2002	映画鑑賞
2003	野球
2004	釣り

※idを見やすくするため便宜上1000、2000番台をつかっています

というマスター管理をして

• person_hobbyテーブル

phid	pid	hid
1	1001	2001
2	1001	2002
3	1002	2002
4	1003	2004

中間テーブルをつかって管理するのです

Comments

[yambejp]

あともう一点
「結婚した日」は必ずしも１人あたり１日ではないこと。
離婚したら結婚した日の情報を消すのか？
消さないなら再婚したときに２つもつのか？
など仕様がかたまってないとテーブルの設計はできません

Answer 6

NULL排除の観点ではないですが、結婚日を複数保持する必要があるかどうかでテーブルを分割するかどうかを考える必要もあるのではないでしょうか。
直近の結婚日1件だけ保持したいならテーブルを分割するまでもないと思いますが、過去の結婚日の履歴が全て必要ならテーブル分割すべきかと思います。

Answer 7

NULL は排除できるなら極力排除するべきだという教えが一般的だと思うのですが

一部に「テーブルを分割してでもNULLは入れるべきでない」という過激派がいますが、そうではなくて「排除できるなら」がNULLが来るのが正当ではない（たとえば、電話帳での名前や電話番号の列など）なら、というもう少しゆるい意味で捉えている可能性も考えられます。

Answer 8

例えば、ユーザテーブルがあり、結婚した日というカラムがあったとします。ユーザには結婚した人もいれば、していない人もいます。このとき、結婚日を NULL 許可するのか、結婚日テーブルのように分割するのか…。

これくらいであれば分割する必要はないかと思います。
例えば「結婚記念日テーブル」を別で持ってユーザーIDと紐づけたとしても、検索の際には仰っているようにJOINする必要がありますし。

検索の要件次第でもありますね。
結婚記念日だけを検索する機能があるのでしたら分割するメリットはあるかもしれません。
でもその機能に「未婚」というのがあるのでしたら分割するメリットはなくなります。

テーブルを分割するのでしたら子テーブルに複数行入ることが想定されるケースが多く当てはまるのではないかなと。
teratailの質問に対する回答やコメントなんかはまさにそれですね。

もちろん1行しかなくても分割するメリットがないわけではないかなと思います。
すごく項目数が多いとか。
実際は名前しか表示されない、検索も名前のみ検索しかしないのに、明細情報まで引っ張ってくる必要はありませんよね。
SNSにおける詳細プロフィールなんかがそれにあたるでしょうか。
明細にしか表示しないようであれば、検索に必要な項目のみ親として、子に明細情報を持っておくというのもあります。

ある程度理解はされているように見受けられますが、「検索条件に利用するかどうか」で決めてはいかがでしょうか。
もちろん「将来的に検索項目が増えるかもしれない」のであれば分割せずに突っ込んでおいても良いですし。
レコード数が多くなると当然分割したテーブルとJOINするのも結構負荷が高いですから、「将来的な観測」は幅広めに持っておいて良いかなと思います。
拡張性の観点からすると難しいところはあるかもしれませんが、そこは設計時にどこまで見越すかということになりますので、DBだけの問題ではないので、
実際の要件・設計と相談して決めてください。

Answer 9

的外れかもしれませんが、親レコードとの関係が 1:n である可能性はご考慮の上でしょうか。
すなわち、ご質問の例で言えば人は2回以上結婚することがありえます。

結婚した日というのが【現在婚姻中の場合はその成立日、そうでなければnull】という定義ならnullableカラムで十分です。
【結婚経験があるならその成立日】だったら 1:n ですから別テーブルにせざるを得ません。

「結婚した日」というのがお仕事で本当に扱っている要件でなくて質問のための仮の要件だとしても、似たような構造がありえるので仕様を一度洗い直しておく必要性を感じました。