seadwell回答ポイント 100pt

MySQLでのテーブル設計についてのアドバイスを求めます。

ユーザーtbl, 地方tbl, サブ地方tbl の3つのテーブルでユーザーtblの出身地をカテゴライズしたいのですが、すべてのユーザーにサブ地方が存在するわけではありません。
例を挙げれば、地方tblを、関東, 関西などのレコードを持ち、サブ地方tblには、東京, 大阪などのレコードがあると仮定します。
しかし、すべてのユーザーが東京や大阪に属するわけではありません、千葉の方は関東には属しますが東京には属しません。
つまりサブカテゴリを持ちません。

このような場合、後に柔軟なジョイントに対応させるにはどのようにIDを持たせどのような設計が必要でしょうか？
よろしくお願いします。

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ログインして回答する

みんなの回答

tama2132011/03/20 21:17:43
満足20pt

ユーザーテーブルの出身地に

地方ID、サブ地方IDを持つだけではだめなんでしょうか？

サブ地方IDはない場合もあるので、NULLを許可するか、00：該当なしなどのそれ用のコードを

割り当ててはどうでしょうか？

質問文だけではこれでよいのかよくわかりませんが

サブカテゴリがない場合の扱いを決めればよいだけだと思うのですがどうでしょうか？

サブカテゴリーがない状態が扱いにくいのなら、ダミーのサブカテゴリーを割り当ててしまえば

よいと思います。

スター
- seadwell 2011/03/20 22:09:49
  
  > 質問文だけではこれでよいのかよくわかりませんが
  
  そうですね。
  
  私自身が未熟なので、将来どういう抽出があるか？予測できないので、質問が漠然となっています。
  
  T字tblを使いスマートな設計は無いもんですかね？
  
  この場合T字は無意味ですか？

deflation2011/03/20 22:35:54
満足20pt

T字tblを使いスマートな設計は無いもんですかね？

T字型ER図のことでしょうか？

これはER図の表現方法の一種なので、ご質問の内容とは直接関係しないと思います。

まずはふつうのER図を描いてみることをお勧めします。

スター

うぃんど2011/03/20 22:38:30
満足20pt

正規化の過程で地方と地方サブにテーブルを分けることを考えたのだと思いますが、

２つのテーブルを組み合わせた情報量が少ない場合には、

あえて非正規化を採用して１つにまとめてしまったほうがスッキリしたりしますよ

地方ID	地方サブID	地方	地方サブ
3	0	関東	空白
3	1	関東	東京
4	0	中部	空白
4	1	中部	愛知
5	0	近畿	空白
5	1	近畿	大阪

テーブルがひとつになったことでSQLはシンプルになりますし、シンプルになれば応答速度も自然と上がります

上記では地方サブIDに適当な値を割り当てましたが地方サブIDはJIS都道府県コードにしておくといいかもしれません

都道府県コードにしておけば地方サブIDでの検索や集計などを行うSQLを書くのも楽になるでしょうし、

多くの情報が都道府県コードで分類されていたりしますので、新たな情報追加や他システムとの連携の際にも楽になることが期待できます

地方ID	地方サブID	地方	地方サブ
3	0	関東	空白
3	13	関東	東京
4	0	中部	空白
4	23	中部	愛知
5	0	近畿	空白
5	27	近畿	大阪

スター

seadwell 2011/03/20 23:05:50

> 正規化の過程で

おっしゃるとおりです。

これは考えて無かったです。

地方サブを追加するときも楽ですね。

もう少し皆さんの意見を聞いてみたいと思います。

taroe2011/03/20 23:31:53
満足20pt

＞後に柔軟なジョイントに対応させるにはどのようにIDを持たせどのような設計

正規化するのが、いろんなケースに対応しやすいですので

設計時に考察漏れがあってもあとからなんとかなることが多いですので

設計時は無意味だと思っても正規化することをお勧めします。

設計時に非正規化するのは、性能がネックになる場合等の特殊な場合だけです。

レコード数が少ない場合は非正規化することは手慣れた人はあまりしません。

設計時に非正規化してそれがベストだと思っていても、たいていはそうでないことが

多いからです。

非正規化すると一見わかりやすく感じたりするんですが、たいていその設計はよくないです。

データーベース設計の本でも機械的な正規化の方法が書いてるのはそのためです。

＞ユーザーtbl, 地方tbl, サブ地方tbl

テーブル構成はこれでよいと思います。

サブ地方TBLのほうに

地方サブID	地方サブ名	地方ID
3	東京	1

のように（地方ID-サブ地方ID）の関係がわかるような情報を入れるだけでよいかと思います。

ER図で、サブカテゴリがない場合は線でつなげないとか思ってるのでしたら

それは勘違いです。つなげます。

スター

seadwell 2011/03/21 02:40:03

ご回答ありがとうございます。

>設計時は無意味だと思っても正規化することをお勧めします。

なるほどです･･･。

ちなみにユーザーtblの方には、地方IDと地方サブIDの両方持つのでしょうか？

両方無いとだめですよね。

それとも地方サブIDだけ持ち、地方サブ名にNULLをセットしたレコードを地方tbl分持つのと、どちらがスマートなんでしょうか？

a-kuma32011/03/21 06:35:49
満足20pt

ぼくも正規化しない、というのは反対。

で、ぼくが思うに、サブ地方tbl があるからすっきりしないんだと思うんだけど。

■地方tbl

地方ID	地方	従属地方ID
100	関東	(空)
101	東京	100
102	千葉	100

■ユーザtbl

ユーザID	ユーザ	出身地ID
0001	東京出身	101
0002	千葉出身	102
0003	八丈島出身	100

関東地方出身者を抽出する SQL。

select * from ユーザtbl where 出身地ID = '100' or 出身地ID in (select 地方ID from 地方tbl where 従属地方ID = '100')

地方tbl で、関東のレコードで、従属地方ID を空にするかどうかは、一考の余地あり。

従属地方ID に、関東のID を入れておくと、SQL が簡単になる。

広域な地方を明示的に区別しておきたいなら、別のフィールドで属性を持たせる、かな？

スター

seadwell 2011/03/21 10:59:32

このような考え方もあるのですね。

参考になります。

その他の回答を読む

この質問へのコメント

うぃんど2011/03/21 11:35:26

地方は７種類しかありませんし都道府県との組み合わせもユニークなものですから、下記の場合に性別tblを用意したりはしないのと同様に扱うというのが私の回答（提案）です
|*コード|*氏名|*性別|
|001|一郎|男|
|002|花子|女|
　
a-kuma3 さんのおっしゃるようにテーブルを再帰的に使うという手法も便利ですが、再帰的利用は以下のような場合に行います
|*社員コード|*名|*上司コード|
|001|一郎||
|002|花子|001|
|003|三郎|002|
|004|四郎|002|
１つのフィールドに２つの異なる情報（地方名と都道府県名）が入るというやりかたは正規化以前にテーブル設計の基礎部分でおかしいですし、ちいさなテーブルとはいっても再帰的な問い合わせを実装していないMySQLでの利用コストは利用頻度に応じて問題になってくるかもしれません
　
他の方で、非正規化という言葉にのみ反応している人もおられますが、正規化をすでに行ってきておられる現状において、「この部分はここから先、正規化を進めて良いのかどうか？」という質問であるという点には気づいていないのだと思います
　
もちろん、私の回答は完全解答ではありません。あくまでも提案ベースです
（そちらの組もうとしておられるシステムを俯瞰できるような情報があればロジック側との調整なども含めて）まだまだ別の方法が出てくることでしょう
sayo219sayo2011/03/21 12:27:51

いつからここ回答欄になったっけか？
もっとも、イルカ賞取得率を自慢にしている回答者もいるらしいが。
a-kuma32011/03/21 12:32:45

windofjuly> １つのフィールドに２つの異なる情報（地方名と都道府県名）が入るというやりかたは正規化以前にテーブル設計の基礎部分でおかしい

のではなくて、地方と都道府県（質問では、地方とサブ地方）を異なる情報ととらえることはないんじゃない？、というのが僕の回答。

異なる情報ととらえるべきかどうかは、問題のカテゴリーによってどちらが正しいということは無いのだろうけど、
質問では「後に柔軟なジョイントに対応させるには～」とあるので、そういう場合（先にどうなるか分からない）には、
抽象的に設計しておく方がベターだろう、というのが僕の考え。

「柔軟に」というので、想像したのは以下のようなパターン。
・北海道の支庁
・瀬戸内地方
・越後とか甲府とか、昔っぽい呼び方

こういうのを思いついてしまった時点で、地方ID のフィールドを二つだけ持つような設計は、
僕としてはあり得ない。
taroe2011/03/21 13:42:58

私の回答はあくまでも普遍的な一般論です。
今回のケースが十分この範囲に入ると思って回答しました。

コードを付けて管理したいという場合は、テーブルを分けて正規化されたほうが良いと
私は思います。
多くの人が正規化したデータベースを見るのになれてますというのもメリットの一つです。
正規化はわかりやすくするための方法ではありません。
不具合を作りこんだり不整合を起こすようなデータを作らないための手法です。
seadwell2011/03/21 15:03:46

いつのまにか回答が閉め切られていました。
失礼しました。
回答を延長したつもりでしたが、再開ボタンを押さないといけなかったんですね。

皆様の回答のおかげで、いろんな考え方を頂きだいぶ頭が柔らかくなりましたしだんだんと構想が固まってきました。
ありがとうございました。

MySQLでのテーブル設計についてのアドバイスを求めます。

みんなの回答