ユーザーtbl, 地方tbl, サブ地方tbl の3つのテーブルでユーザーtblの出身地をカテゴライズしたいのですが、すべてのユーザーにサブ地方が存在するわけではありません。
例を挙げれば、地方tblを、関東, 関西などのレコードを持ち、サブ地方tblには、東京, 大阪などのレコードがあると仮定します。
しかし、すべてのユーザーが東京や大阪に属するわけではありません、千葉の方は関東には属しますが東京には属しません。
つまりサブカテゴリを持ちません。
このような場合、後に柔軟なジョイントに対応させるにはどのようにIDを持たせどのような設計が必要でしょうか?
よろしくお願いします。
ユーザーテーブルの出身地に
地方ID、サブ地方IDを持つだけではだめなんでしょうか?
サブ地方IDはない場合もあるので、NULLを許可するか、00:該当なしなどのそれ用のコードを
割り当ててはどうでしょうか?
質問文だけではこれでよいのかよくわかりませんが
サブカテゴリがない場合の扱いを決めればよいだけだと思うのですがどうでしょうか?
サブカテゴリーがない状態が扱いにくいのなら、ダミーのサブカテゴリーを割り当ててしまえば
よいと思います。
T字tblを使いスマートな設計は無いもんですかね?
T字型ER図のことでしょうか?
これはER図の表現方法の一種なので、ご質問の内容とは直接関係しないと思います。
まずはふつうのER図を描いてみることをお勧めします。
正規化の過程で地方と地方サブにテーブルを分けることを考えたのだと思いますが、
2つのテーブルを組み合わせた情報量が少ない場合には、
あえて非正規化を採用して1つにまとめてしまったほうがスッキリしたりしますよ
地方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 | 近畿 | 大阪 |
> 正規化の過程で
おっしゃるとおりです。
これは考えて無かったです。
地方サブを追加するときも楽ですね。
もう少し皆さんの意見を聞いてみたいと思います。
>後に柔軟なジョイントに対応させるにはどのようにIDを持たせどのような設計
正規化するのが、いろんなケースに対応しやすいですので
設計時に考察漏れがあってもあとからなんとかなることが多いですので
設計時は無意味だと思っても正規化することをお勧めします。
設計時に非正規化するのは、性能がネックになる場合等の特殊な場合だけです。
レコード数が少ない場合は非正規化することは手慣れた人はあまりしません。
設計時に非正規化してそれがベストだと思っていても、たいていはそうでないことが
多いからです。
非正規化すると一見わかりやすく感じたりするんですが、たいていその設計はよくないです。
データーベース設計の本でも機械的な正規化の方法が書いてるのはそのためです。
>ユーザーtbl, 地方tbl, サブ地方tbl
テーブル構成はこれでよいと思います。
サブ地方TBLのほうに
地方サブID | 地方サブ名 | 地方ID |
---|---|---|
3 | 東京 | 1 |
のように(地方ID-サブ地方ID)の関係がわかるような情報を入れるだけでよいかと思います。
ER図で、サブカテゴリがない場合は線でつなげないとか思ってるのでしたら
それは勘違いです。つなげます。
ご回答ありがとうございます。
>設計時は無意味だと思っても正規化することをお勧めします。
なるほどです・・・。
ちなみにユーザーtblの方には、地方IDと地方サブIDの両方持つのでしょうか?
両方無いとだめですよね。
それとも地方サブIDだけ持ち、地方サブ名にNULLをセットしたレコードを地方tbl分持つのと、どちらがスマートなんでしょうか?
ぼくも正規化しない、というのは反対。
で、ぼくが思うに、サブ地方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 が簡単になる。
広域な地方を明示的に区別しておきたいなら、別のフィールドで属性を持たせる、かな?
このような考え方もあるのですね。
参考になります。
> 質問文だけではこれでよいのかよくわかりませんが
そうですね。
私自身が未熟なので、将来どういう抽出があるか?予測できないので、質問が漠然となっています。
T字tblを使いスマートな設計は無いもんですかね?
この場合T字は無意味ですか?