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

質問

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

テーブル：英単語
フィールド：単語、意味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を取り出して、英単語テーブルに入れるにはどうしたらいいでしょうか。

回答No.4

yambejp · ベストアンサー

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

まず元になるデータを羅列します
（元テーブル）
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`;

お礼

わざわざSQLまで書いて頂いて、感謝してもし足りないです。お金払いたいくらいです。ありがとうございました。

分類はゆるいタグのようなものを想定していますので、品詞IDは意味テーブルに置こうかと考えております。下のように変えてみました。

（正規化後）
英単語テーブル
英単語ID,英単語
1,eye
2,hand

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

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

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

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

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(`意味`),`品詞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(200));
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 `分類テーブル`(`分類`) VALUES(@c);
INSERT IGNORE INTO `意味テーブル`(`意味`,`分類ID`,`難易度`,`品詞ID`) SELECT @b,`分類ID`,@e,`品詞ID` FROM `分類テーブル JOIN `品詞テーブル` ON ` WHERE `分類`=@c AND `品詞`=@d ;
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`;

回答No.3

yambejp

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

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

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

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

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

回答No.2

hardgeek

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

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

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

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

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

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

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

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

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

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

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

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

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

お礼

ご回答誠にありがとうございました。

確かに第一正規化が出来てないと思っていました。
第一正規化して繰り返しグループを避けることはわかっていたのですが、
分類や意味など、単語に付随して複数の項目が交錯するときのDBのイメージがつかめずにいましたが
質問にお応えいただいたおかげで、だいぶんわかりやすくなりました。
意味ごとに分類と品詞が決まるので

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

の形がいいのかな、という気がしました。例文も、意味より、単語につけて全ての例文を羅列、のほうがいいかな、と思ったので。

今回はほぼ全てバラバラにして正規化しようと思います。ありがとうございました。

回答No.1

yambejp

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

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

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

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

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

補足

早速ご教授ありがとうございます！
意味カラムを一つにするとなると、、いろいろ考えた挙句、こうしてみましたがどうでしょうか。

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

↓正規化↓
■■■■■■■■■■■
(1)
テーブル：紐付け
フィールド：英単語ID、意味ID、分類ID、品詞ID、例文ID、難易度ID、ID（オートインクリメント、主キー）

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

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

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

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

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

■■■■■■■■■
例えばこういうデータを入れます。

英単語：　　意味　　：　　分類　　：品詞　：　難易度
eye　 : 　(1)目玉　　：　(1)物体(2)体：名詞　：　2
　　　：　(2)視力　　：　(1)能力　　：名詞　：　2
hand　 :　(1)手　　　：　(1)物体(2)体：名詞　：　3
　　　　　(2)拍手　　：　(1)行為　　：名詞　：　4

↓こういうデータが格納されるのかな、と思います。↓
◆◆◆◆◆◆◆◆◆◆◆◆◆
【テーブル：紐付け】
ID:0
英単語：0
意味ID：0
分類ID：0
品詞ID：2
難易度:2

ID:1
英単語：0
意味ID：0
分類ID：4
品詞ID：2
難易度:2

ID:2
英単語：0
意味ID：1
分類ID：1
品詞ID：2
難易度：3

ID:3
英単語：1
意味ID：2
分類ID：2
品詞ID：2
難易度：3

ID:4
英単語：1
意味ID：2
分類ID：4
品詞ID：2
難易度：3

ID:5
英単語：1
意味ID：3
分類ID：2
品詞ID：2
難易度：4

【テーブル：英単語】
英単語ID:0
英単語：eye

英単語ID:1
英単語：hand
【テーブル：意味】
意味ID：0
意味：目、眼球、目玉

意味ID：1
意味：視力・視覚

意味ID：2
意味：手

意味ID：3
意味：拍手

【テーブル：分類】
分類ID：0
分類：体

分類ID:1
分類：能力

分類ID：2
分類：行為

分類ID：3
分類：物体

【品詞】
品詞ID：0
品詞：形容詞

品詞ID：1
品詞：動詞

品詞ID：2
品詞：名詞

品詞ID：3
品詞：副詞

◆◆◆◆◆◆◆◆◆◆