データベースの正規化についてアドバイスください。

Question

こんにちは。データベース設計について勉強中です。

調べて自分なりに正規化してみました。アドバイスいただけると嬉しいですm(__)m

例題として大学の学生を管理するデータベースとします。

ポイントとしては下記になります。

• 大学名・学部・学生の追加、削除、変更あり
• 異なる大学でも同名の学部がある
• 二重学籍は認めないので、一人の学生が複数の大学に所属することはない
• 学生は退学、編入する場合あり
• 管理画面から大学と学部を選択し、学生の情報を入力して登録できる（大学を選択すると該当する学部だけが選択可）
• 表示画面では大学または学部を選択すると該当する学生を表示する
• 登録するデータ（学生）は数千件を予定、多くても数千前半（〜5000件）で、万件はいかない

表示画面では下記のように大学または学部のカテゴリを選択すると該当する学生を表示するイメージでいます。

非正規形を下記のようにしました。

私なりに正規化したものが下記です。

理由と悩んだ箇所

• 「学生表」に「大学ID」と「学部ID」の項目を入れて管理しても問題なさそうに思えましたが、「学生・大学・学部表」として別々にしました。（別々にすると無駄なテーブルが増えるだけ？）
• 最初は大学ごとに学部を管理するテーブルを作成しようと思いましたが、同じ学部名があるため、「学部表」として一つのテーブルまとめました。（まとめると不都合ありますか？）
• 管理画面上で大学を選択すると該当する学部のみを選択できるようにしたいので、それを管理するために「大学・学部表」というテーブルを作成しました。

私が一番気にしてる点は、「学部」です。
気にする点が的外れかもしれませんが、「大学」と「学部」は親子関係にもかかわらず、「学部」を一つにまとめて管理しても問題ないのかという点です。

データベース設計をしたことがないため、質問での情報が少なかったり、正規化に問題があるかと思います。
気になる点などございましたら、何でも良いのでご意見くださいm(__)m

回答しやすいように表組みのテンプレを下記に記載しておきます。

表組みテンプレ

学生・大学・学部表
|学生ID|大学ID|学部ID|
|:--|:--|:--|

学生表
|学生ID|氏名|電話番号|住所|登録年月日|
|:--|:--|:--|:--|:--|

大学・学部表
|大学ID|学部ID|
|:--|:--|

大学表
|大学ID|大学|
|:--|:--|

学部表
|学部ID|学部|
|:--|:--|

Comments

[mather]

正規化の例を見る限り言外の条件が増えているようですが、「二重学籍は認めないので、一人の学生が複数の大学に所属することはない」は正しくは「二重学籍は認めないので、一人の学生が複数の大学、複数の学部に所属することはない」ということですか？

[退会済みユーザー]

言葉足りずで、ごめんなさい。仰るとおり「二重学籍は認めないので、一人の学生が複数の大学、複数の学部に所属することはない」です。

Answer 1

学生を基準にされているので、大学を跨いだサークル活動での、所属大学を割り当てることをイメージしました。

「学生表」に「大学ID」と「学部ID」の項目を入れて管理しても問題なさそうに思えましたが、「学生・大学・学部表」として別々にしました。（別々にすると無駄なテーブルが増えるだけ？）

二重学籍を許さないのなら、n:nは発生しないので、不要です。

最初は大学ごとに学部を管理するテーブルを作成しようと思いましたが、同じ学部名があるため、「学部表」として一つのテーブルまとめました。（まとめると不都合ありますか？）
管理画面上で大学を選択すると該当する学部のみを選択できるようにしたいので、それを管理するために「大学・学部表」というテーブルを作成しました。

学部については、全ての大学をターゲットにするのですから、学部の名称だけを対象とするなら、重複を除外すると纏めるという事になります。
※各大学の学部についてユニークな属性まで持たせるなら別ですけど。
この場合、大学：学部=n:nとなるので中間テーブルが必要になり、「大学・学部表」は定義すべきものです。

追記

他の方の回答を見て学部を別テーブルにする場合について追記しておきます。

質問内容からは、どうしても大学が主には感じないので、学部というのはカテゴリーというイメージです。
なので、学部の詳細な情報を必要な場合には別テーブルとして追加します。
その際に、そのテーブルから学部は外部参照します。
そうすれば、全国の大学の学部別の詳細な一覧などが取り出せますので。

Answer 2

■　正規化について
他の方もおっしゃっていますが、学部は学校ごとに登録すべきかと、私も思います。

■　それ以外で気になる点
※　現実の話をし始めると、キリがなさそうですので、パッと見で・・

・入学・退学・卒業の情報がない
学生が入学する前に、データの登録が完了しなければいけないと思うので、
登録年月日と、入学年月はきっと別ですよね。

・学年に関する情報がない
学生管理なら、学年情報は必要不可欠・・と思いましたが、そうでもないのでしょうか？

情報として必要なら、留年や休学は考慮したほうがいいかな、と思うので、
仮に入学年月の情報があったとしても、それだけでは学年を判別できません。

・学校間、学部間の編入履歴を管理するテーブルがない
編入有の前提条件があるならば、必要なのでは・・？

--

例えばですが。
上記全て含めて1つのテーブルで管理しちゃうのもありかな？と。

進退ステータス表
※これは、マスタデータじゃなくてもいい気がしますが、とりあえず。

ステータスID	ステータス名称
1	入学
2	進級
3	卒業
4	留年
5	編入
6	休学
7	復学
8	退学

進退履歴表
※学部は、学校ごとに登録されているテイで、定義しました。
※説明列は、データがどう管理できるかのイメージが付くよう、説明文として追加しただけですｗ

学生ID	進退ステータスID	学部ID	年月日	説明
1	1	1	2013/04/01	1さん：入学
1	2	1	2014/04/01	1さん：2年に進級
1	5	2	2015/04/01	1さん：学部編入
1	2	2	2015/04/01	1さん：3年に進級
1	4	2	2016/04/01	1さん：留年
1	2	2	2017/04/01	1さん：4年に進級
1	3	2	2018/03/31	1さん：卒業
2	1	1	2013/04/01	2さん：入学
2	2	1	2014/04/01	2さん：2年に進級
2	4	2	2015/04/01	2さん：留年
2	8	2	2015/09/15	2さん：退学

これだと、学生表には、「学部」列が不要となります。

ただ、手動で登録・管理するのは、かなり手間な内容なのと、
入学からデータがないとうまく管理ができないですが・・^^;

Answer 3

学部はそれぞれの大学で新設、統合、廃止があり得るので、大学ID, 学部ID をPRIMARY KEYで持っておいた方が良いのでは？また、[2019年新設]といったコメントを入れる備考も付けた方が便利でしょう。
大学は廃止されたからといって即DELETEってできないことが多いので、大学は募集停止年、廃止年の情報も欲しいです。

Answer 4

yubaさんのご指摘のとおり学部は微妙ですね。
学校ごとにちょっとずつマイナーな名称がつくので
学部名で正規化するのは難しいと思います

学部系統分類くらいでカテゴライズはできそうなので
学部名自体はフリーで記入する方が運用しやすいでしょう

集計を前提とするうと都道府県と市区町村は分けたほうがよいかもしれません。

Answer 5

「学生・大学・学部表」と「大学・学部表」の内容が重複しています。
大学と学部が確実な親子関係にあって、条件には記載していませんが学部名が変更になるケースを想定すると、同名の学部であってもレコードは別になるべきかと思います。

学部表

学部ID	大学ID	学部名
1	1	文学部
2	2	文学部

こうなると、学生は学部IDだけ持てばいいことになります。

学生表

学生ID	学部ID	氏名	電話番号	住所	登録年月日
1	1	...	...	...	...

こうすることで「学生・大学・学部表」と「大学・学部表」は不要になります。
これができる条件は「学生はただ一つの大学のただ一つの学部に所属する」ということです。

Answer 6

「学部」を一つにまとめて管理はよろしくないですね。
たとえば東京大学医学部を指すことができる単一のIDというものが存在しないことになってしまいます。
またもっと現実的な話、ある大学で学部の改称があったときにデータの付け替えが大変なことになります。

あとは、正規化の考え方で間違っている点は特に指摘できないです。