データベースと正規化とINSERT文について

Question

ただいま以下の一つのテーブルを正規化しようとしています。

テーブル：英単語
フィールド：単語、意味1、意味2、意味3、品詞、分類1、分類2、分類3、例文、難易度、ID（主キー・オートインクリメント）

これを以下のように分けました。（分類とは単語の分類です。例えば year なら意味は 年 で分類は 時間 とか 単位 とか属性を入れたいのです。）

＊＊＊＊＊＊
テーブル：英単語
フィールド：単語、意味1、意味2、意味3、分類ID、分類ID、分類ID、例文ID、難易度、ID（主キー・オートインクリメント）

テーブル：分類
フィールド：分類名、ID（オートインクリメント　主キー）

テーブル：品詞
フィールド：品詞名、ID（オートインクリメント　主キー）

テーブル：例文
フィールド：例文、ID（オートインクリメント　主キー）

＊＊＊＊＊＊

(1)これで正規化できているでしょうか？
もしできていないなら、どうすればよいでしょうか？

(2)また、仮にこのままデータベースを作るとすると、INSERTの時に英単語テーブルの分類IDに、分類テーブルのIDをひもづけるにはどうしたら良いのでしょうか？
INSERT INTO 分類 (分類名) VALUES("時間")
のあと、オートインクリメントされたIDを取り出して、英単語テーブルに入れるにはどうしたらいいでしょうか。

yambejp · Accepted Answer

せっかくなので軽く書いてみましょう

まず元になるデータを羅列します
（元テーブル）
ID,英単語ID,英単語,意味ID,意味,分類ID,分類,品詞ID,品詞,難易度
1,1,eye,1,目玉,1,物体,1,名詞,2
2,1,eye,1,目玉,2,体,1,名詞,2
3,1,eye,2,視力,3,能力,1,名詞,2
4,2,hand,3,手,1,物体,1,名詞,3
5,2,hand,3,手,2,体,1,名詞,3
6,2,hand,4,拍手,4,行為,1,名詞,4

これを正規化します
（正規化後）
英単語テーブル
英単語ID,英単語
1,eye
2,hand

意味テーブル
意味ID,意味
1,目玉
2,視力
3,手
4,拍手

英単語_意味テーブル
英単語ID,意味ID
1,1
1,2
2,3
2,4

分類テーブル
分類ID,分類,品詞ID
1,物体,1
2,体,1
3,能力,1
4,行為,1

意味-分類テーブル
意味ID,分類ID 
1,1
1,2
2,3
3,1
3,2
4,4

品詞テーブル
品詞ID,品詞
1,名詞

これをSQLで書くと
CREATE TABLE `英単語テーブル`(`英単語ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`英単語` VARCHAR(50),UNIQUE KEY(`英単語`));
CREATE TABLE `意味テーブル`(`意味ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`意味` VARCHAR(200),`分類ID` INT,`難易度` INT,UNIQUE KEY(`意味`));
CREATE TABLE `英単語_意味テーブル`(`英単語_意味ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`英単語ID` INT,`意味ID` INT,UNIQUE KEY(`英単語ID`,`意味ID`));
CREATE TABLE `分類テーブル`(`分類ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`分類` VARCHAR(200),`品詞ID` INT,UNIQUE KEY(`分類`));
CREATE TABLE `意味_分類テーブル`(`意味_分類ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`意味ID` INT,`分類ID` INT,UNIQUE KEY(`意味ID`,`分類ID`));
CREATE TABLE `品詞テーブル`(`品詞ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`品詞` VARCHAR(20),UNIQUE KEY(`品詞`));

//データの追加

SET @a='eye',@b='目玉',@c='物体',@d='名詞',@e=2;
INSERT IGNORE INTO `品詞テーブル`(`品詞`) VALUES(@d);
INSERT IGNORE INTO `分類テーブル`(`分類`,`品詞ID`) SELECT @c,`品詞ID` FROM `品詞テーブル` WHERE `品詞`=@d;
INSERT IGNORE INTO `意味テーブル`(`意味`,`分類ID`,`難易度`)  SELECT @b,`分類ID`,@e FROM `分類テーブル` WHERE `分類`=@c;
INSERT IGNORE INTO `意味_分類テーブル`(`意味ID`,`分類ID`) VALUES((SELECT `意味ID` FROM `意味テーブル` WHERE `意味`=@b),(SELECT `分類ID` FROM `分類テーブル` WHERE `分類`=@c));
INSERT IGNORE INTO `英単語テーブル`(`英単語`) VALUES(@a);
INSERT IGNORE INTO `英単語_意味テーブル`(`英単語ID`,`意味ID`) VALUES((SELECT `英単語ID` FROM `英単語テーブル` WHERE `英単語`=@a),(SELECT `意味ID` FROM `意味テーブル` WHERE `意味`=@b));

最初のset行を
SET @a='eye',@b='目玉',@c='体',@d='名詞',@e=2;
SET @a='eye',@b='視力',@c='能力',@d='名詞',@e=2;
SET @a='hand',@b='手',@c='物体',@d='名詞',@e=3;
SET @a='hand',@b='手',@c='体',@d='名詞',@e=3;
SET @a='hand',@b='拍手',@c='行為',@d='名詞',@e=4;
SET @a='cat',@b='猫',@c='動物',@d='名詞',@e=1;
SET @a='cat',@b='連結する',@c='動作',@d='動詞',@e=5;
のようにして順次INSERT を繰り返します。
場合によってはプロシージャやファンクションにしてもいいでしょう。

結果表示はこんなかんじ

SELECT T1.`英単語ID`,T1.`英単語`,T2.`意味ID`,T3.`意味`,T4.`分類ID`,T5.`分類`,T5.`品詞ID`,T6.`品詞`,T3.`難易度`
FROM `英単語テーブル` AS T1
INNER JOIN `英単語_意味テーブル` AS T2 ON T1.`英単語ID`=T2.`英単語ID`
INNER JOIN `意味テーブル` AS T3 ON T2.`意味ID`=T3.`意味ID`
INNER JOIN `意味_分類テーブル` AS T4 ON T3.`意味ID`=T4.`意味ID`
INNER JOIN `分類テーブル` AS T5 ON T4.`分類ID`=T5.`分類ID`
INNER JOIN `品詞テーブル` AS T6 ON T5.`品詞ID`=T6.`品詞ID`
ORDER BY `英単語ID`,`意味ID`,`分類ID`,`品詞ID`;

yambejp · Answer

だいたいイメージはよいかと思いますが

今回の例だと意味テーブルを作る必要はないかもしれませんね
紐づけテーブルで意味IDの代わりに直接意味を書けばいいので

逆に意味テーブルを作るのであれば、品詞や分類は意味テーブルの子供に
してしまってもいいかもしれません。

例でいれば、eyeに「目」と「物体」「体」と、「名詞」を持たせるのではなく
eyeは「目」で、「目」は「物体」「体」・「名詞」であるとした方がより
分類上はわかりやすくなります。
（検索処理は煩雑になるのであえて並列でもたせる手もありますが）

また意味ごとに分類が分化していくのであれば分類と品詞の相関関係を
どうするか決める必要がでてきます
分類がきまれば品詞が確定するなら正規化できるということ
たとえば「物体」は必ず「名詞」であるなら、目に「名詞」を指示しなくても
「物体」であることだけ指示すればすみます
そうでなく「物体」が「動詞」や「形容詞」になる場合は、そういうわけには
いかないのでそれぞれを指定する必要があります。

hardgeek · Answer

正規化は残念ながらできていません。意味1、意味2、意味3のように同じものを表すカラムが複数回登場するのは、繰り返しグループと呼ばれるものであり、第一正規形になっていない兆候です。

正規化するにはテーブルを次の3つに分割するのが良いでしょう。

・単語、品詞、例文、難易度
・単語、意味
・単語、分類

単語が主キーになれるので（ナチュラルキー）、この場合はあえてIDをつける必要はないと思います。

上記のような設計以外では、品詞が何であるかは意味によって異なるので、次のような設計のほうが良いかも知れません。

・単語、例文、難易度
・単語、意味、品詞
・単語、分類

意味ごとに分類が決まるというのであれば、

・単語、例文、難易度
・単語、意味、品詞、分類

という設計でもいいと思います。

意味ごとに例文や難易度が決まるというのであれば、

・単語、意味、品詞、分類、例文、難易度

という単一のテーブルで、(単語,意味)という複合キーを主キーにするという方法もあります。

3つほど例を挙げましたが、どれが良いかは状況次第で判断してください。最終的にはそれぞれのカラムが持つ意味が何であるかをじっくり考えた上で、テーブルを設計する必要があるでしょう。

yambejp · Answer

＞テーブル：英単語
＞フィールド：単語、意味1、意味2、意味3、分類ID、分類ID、分類ID、例文ID、難易度、ID（主キー・オートインクリメント）

英単語テーブルは、まず単語のIDをオートインクリメントにするかどうかは微妙
「単語」を登録するテーブルなのですから、場合によっては主キーは単語自身でいいかも

「意味」という似たような属性を３カラム用意するのは無駄
必ず３つの意味をもつわけでもなく、逆に４つ以上の意味をもつ可能性もあります
「単語-意味」テーブルをつくる必要があるかもしれません
分類もどうようです

例文について意味と組み合わせなくては意味がないのでは？
難易度も、意味によって難易度が変わってくるような気がしますが？
一般的な意味とは別に非常に特殊な使い方をする場合とか・・・

ということでもう少し簡単な正規化から手をつけた方がよいかもしれませんね

データベースと正規化とINSERT文について

せっかくなので軽く書いてみましょう

だいたいイメージはよいかと思いますが

正規化は残念ながらできていません。

＞テーブル：英単語

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング