【DB】テーブルを分ける？わけない？

質問

会員の非公開の個人情報データ（本名・電話・住所等）と公開される情報（ニックネーム、自己PR等）のデータがあったとして、この非公開と公開情報の編集更新画面は別だったとします。

データの正規化という面では同じテーブルにしても良いと思うのですが、情報の編集画面がそれぞれ別であればテーブルを分けたほうがよい？？

not null出来ないし

回答No.4

anmochi · ベストアンサー

私が設計するなら一緒にする。

ここで、まず言葉の定義をしたい。IDや本名やニックネームという項目の一つの値をラスタ、複数のラスタがまとまったものをレコード、レコードを複数格納する器をテーブル、テーブルの中でラスタを識別するものをカラムと呼ぶ。

一人分の非公開の個人情報（本名・電話・住所等）のラスタ達が個人IDという1つの主キーラスタで一意に決まり、それとは別に一人分の公開される情報（ニックネーム、自己PR等）のラスタ達が個人IDという1つの主キーラスタで一意に決まる、のであればそれは1つのテーブルに収まるべき情報だと思う。

個人IDで特定されるようなラスタ群を複数のテーブルに格納するように分けるようなパターンは、例えば会員登録制のSNSを作るとして、このSNSはまずアカウントを登録して、自身のブログページを持つ場合はプロフィールも登録する、他人のブログを閲覧するだけの人はアカウント情報だけでOKという場合にアカウント情報とプロフィール情報のテーブルに分けるような話が考えられるだろう。アカウント情報はアカウントIDとアカウント名とパスワード、プロフィール情報は名前とか生年月日とか性別とか自己PRとか。そうすると閲覧するだけの人はアカウント情報だけなので自己PR用のラスタを格納するハードディスク領域を無駄に取られなくて済む。

つまり、公開・非公開というメタデータ的な区分ではなく、ラスタを（人間やシステムにとって）意味があるまとまりにした時に分かれるべきかどうかで判断するのが良いのではないかと。
あなたが言ったようなカラム群はそれを全部ひっくるめて「個人マスター情報」と言えるのだろうか。それであればそれを全部ひっくるめて１つのテーブルにして、テーブル名はもちろん個人マスターテーブルだ。
意味のあるカラムのまとまりは自然と名前をつける事ができて、逆に言うとテーブル名に困るようなテーブルは正しい設計ができてないという事だ。

1つのテーブルがカラム数によってSELECT文の実行速度が変わる事は実際はほぼない。少なくともOracle Database、Microsoft SQL Server、PostgreSQL、MySQLの現行バージョンにおいてはそのような事象は発生しない。もちろんSELECT * FROM TABLE1とかして要らないカラムも取得するような書き方をしたら遅くなる。

not nullがダメならデフォルト値を決めとけば良いだけだと思う。

後、ぶっちゃけ同じキーで同格のテーブルを複数作るとJOINが面倒という現実もある。先ほどのアカウント情報とプロフィール情報だと単純に　アカウント LEFT JOIN プロフィール　でいけるけど（アカウントとプロフィールは同格ではなくプロフィールがアカウントに従属する形）、AのテーブルにしかないIDはLEFT JOIN、BのテーブルにしかないIDはRIGHT JOINすなわちFULL JOINしないと必要なレコード群が取れないとかもうDB設計が腐ってるとしか言いようがない。

お礼

有難うございました。

>>1つのテーブルがカラム数によってSELECT文の実行速度が変わる事は実際はほぼない。
さすがに select * はしていませんが勉強になります

回答No.3

chie65536（@chie65535）

＞非公開/公開データでなく、双方とも公開データの場合でお考えいただければ幸いです。

「双方とも公開データ」の場合は、レコードサイズ、アクセス頻度、テーブルのレコード件数に応じて、都度、分けるかどうか、個別に設計すべきです。

データ１件のレコードサイズが大きい場合、アクセス頻度の多い少ないで、テーブルを分けたり、分けなかったりします。

１件のレコードサイズが「充分に小さい」のであれば、全フィールドを読み書きしても、そんなに速度低下しません。

１件のレコードサイズが「かなり大きい」のであれば「滅多にアクセスしないフィールド」と「頻繁にアクセスするフィールド」は、別テーブルにした方が速度が向上します。

例えば「IDとパスワードでログインする時」を考えてみて下さい。

「ログイン」に必要な項目は「ユーザーID」と「パスワード」です。

「これからログインしようとする時」は、データベース上の「IDとパスワード」で認証します。

その時に、それ以外の「本名・電話・住所・ニックネーム・自己PR文・アイコン画像など」の「要らない情報」も１つのテーブルに一緒に入って居た場合、それらの「要らない情報」は「データベースから読み込んで、使わないで、捨ててしまう」ので、無駄に処理が遅くなります。

もし、ユーザー情報の中に「３００キロバイトくらいの画像情報」があったら、その「３００キロバイトの画像」は「データベースから読み込んで、使わないで、捨ててしまう」のです。

その「読んで使わずに捨てる」が「IDとパスワードを使ったログイン認証」の時に、毎回起こるとしたら？

そして、それが「１日に何十万人が利用するサイト」で起きたとしたら？

そう考えたら「どうすれば良いか？」は自明です。

答えは「フィールドのデータサイズと、使用頻度によって、テーブルを分離する」です。

なお「ハードディスクなどの記憶装置」は「ある程度のひとかたまりのサイズで読み書きする」ので、それも考慮します。

例えば、HDDは、１セクタ５１２バイト、または、１セクタ４０９６バイト、でアクセスするので、データベースの１レコード（全フィールド）が「４０９６バイト」で済む場合は、テーブルを分離しません。何故なら「HDDへの１回のアクセス」で、１レコード分を読み書き出来てしまうからです。

ですので、ISAMファイルなど、１レコードのデータサイズを思い通りに作れるデータベースを使う場合は、わざと「予備フィールド」をバイトサイズ単位で確保して、１レコードのサイズを「物理的なレコードサイズ」に一致させ、アクセス速度を向上させたりします。

このように「データベースの設計」は「時と場合に合わせて、その都度、最適に設計する必要」があります。

つまり、データの内容と使用頻度によって、その都度「分けたほうが良い」になったり「分けないほうが良い」になったりします。

そのへんがちゃんと理解できてないと「さっきは分けない方が良いって言ったのに、今度は分けた方が良いって言ってる。言ってる事に一貫性が無い」って感じで、悩む事になります。

お礼

詳しい説明ありがとうございましたー

回答No.2

doxob

回答No.1を全面的に支持します🙋

お礼

ありがとうございます。

ちょっと例示が悪かったようです。すいません。
非公開/公開データでなく、双方とも公開データの場合でお考えいただければ幸いです。

回答No.1

chie65536（@chie65535）

＞テーブルを分けたほうがよい？？

当然、別にします。

別にする理由は「非公開データのテーブルは外部から見えない場所に置くべき」だからです。

不正アクセス等で「テーブルの全件を外部から閲覧された」「テーブルの全件を外部に抜かれた」などの事件が起きたとしても「非公開データが外部から見えない場所」にあれば、漏洩被害を防ぐ事が出来ます。

これは「編集更新画面が別かどうか」の問題ではありません。たとえ「編集画面が１つになってて一緒の画面」だったとしても、非公開データと公開データは「保存する物理的な場所を別にして、非公開データは外部から直にアクセスできない場所」に置くべきです。

お礼

ありがとうございます。

ちょっと例示が悪かったようです。すいません。
非公開/公開データでなく、双方とも公開データの場合でお考えいただければ幸いです。