Webアプリ作成におけるDB設計について

Question

初心者で、出会った友人のプロフィールを作成していくWebアプリを作ろうと考えている者です。

項目	内容
名前	A子
年齢	25
誕生日	2000/09/09
どこで出会ったか	SNS
いいところ	顔

以上のような形で出会った、個人個人のプロフィールを書き記し、保存していくということをしたいです。アプリ上のUIだと上記の表のようにしたいと考えています。そして、上記の表でいう「項目」部分を、個人の裁量によって自由に増やしていきたいと考えているのですが、DB設計で止まっています。

方法として考えついたのが、

①自分の友人についてまとめたテーブルがあり、そこにデータを入れる

|ID|名前|誕生日|年齢|どこで出会ったか|いいところ|悪いところ|
|:--|:--|:--|:--|:--|:--|
|1|田中太郎|1990/02/02|22|SNS|イケメン|臭い|
|2|山田花子|1963/01/11|33|酒場|可愛い|臭い|
|3|伊藤カイジ|1996/11/22|44|草むら|勝負運がある|指がない|
|4|山崎俊朗|1990/02/03|55|マッチングアプリ|男前|女々しい|

上記のやり方は一番最初に考えた案です。

しかし、カラムを自由に追加することができない（100個とか1000個とか）と考えている。検索も遅そう。初心者目線で見てもなんとなく効率が悪そう(もっと効率のいいデータ設計があるはず)

また、アプリ所有者それぞれに新しいテーブルを作らなければならないのはなんとなく変だと感じました。そこで考えたのが次のような設計方法です。

②ユーザーID,友人ID、項目、内容をカラムとしたテーブルを作成する。

ユーザーID	友人ID	項目	内容
1	1	名前	田中太郎
2	12	名前	田中次郎
1	5	趣味	スキー
3	10	誕生日	1990/02/02

上記のやり方は全てのユーザーの全ての友人が1つのテーブルに集まるという形になっています。

しかし、これだと項目を自由に増やすことはできますが、全てのユーザーの全ての友人が1つのテーブルに混ぜこぜになるので検索がしづらそうです。テーブルを役割ごとに分けることがいいとは分かっているのですが、どう分ければいいかが分かりません。

Toxi法なるDB設計がやりたいことに近いのではないか？と考えていますが、こんがらがってしまい、それなら質問をした方が早いと考え、質問をさせていただきました。

よろしくお願い致します。

追記

HouraijiICさんのご指摘から以下のような形に正規化することができました。

before

ユーザーID	友人ID	項目	内容
1	1	名前	A子
1	1	性別	女
1	2	名前	B太
1	2	性別	男
2	3	名前	C子
2	3	性別	女
2	4	名前	D太
2	4	性別	男

これを正規化することで、

after

usersテーブル

ユーザーID	友人ID
1	1
1	2
2	3
2	4

user_friendsテーブル

友人ID	項目	内容
1	名前	A子
1	性別	女
2	名前	B太
2	性別	男
3	名前	C子
3	性別	女
4	名前	D太
4	性別	男

これ以上細分化はできないと考えているのですが、考慮する点等はございますでしょうか？

追記 19/11/13

第3テーブルの議論についての補足の表を提示しました。

user_friendsテーブル(第2テーブル)

友人ID	項目	内容
1	1	A子
1	2	女
2	1	B太
2	2	男
3	1	C子
3	2	女
4	1	D太
4	2	男

項目テーブル(第3テーブル)
|名前|性別|趣味|特技|部活|備考|...
|:--|:--|:--|:--|:--|:--|
|1|2|3|4|5|6|...

追記 19/11/14

![]

考えているうちに思考がこんがらがってしまったので、とりあえずER図にまとめました。記載していただいたER図と少し違う箇所があります。

Answer 1

＜＜＜正規化前＞＞＞
ID    名前         誕生日         年齢    どこで出会ったか   いいところ  悪いところ
1     田中太郎 	 1990/02/02    22     SNS             イケメン   臭い

＜＜＜正規化後＞＞＞

＞考慮する点等はございますでしょうか？usersテーブル

ユーザーID    友人ID
1            2

＜＜＜ここは＞＞＞
user_friendsテーブル
友人ID     項目     内容
1         名前     A子

↓

友人ID     名前    性別   誕生日        年齢    どこで出会ったか いいところ  悪いところ
2         B太     男     1990/02/02   22     SNS           イケメン   臭い

の、第二正規化で、実現可能では無いでしょうか。

＞検索の速さをどうするかというのが個人的には気になっています。インデックスを追加するだけでいいでしょうか？

dbエンジンはなんでしょうか？

この程度であれば、インデックスしなくても、inner join で、大丈ぶだと思うのですが。。。

遅い様であれば、インデックしてください。

追記：2019.11.13
要件定義から、とりあえず、概念的には、↓の様に考えました。
これで、
＜項目テーブル＞＜ユーザ項目テーブル＞
への「insert, select」をモデル部分に実装しておけば、
「その度にカラムが増えるのは面倒」
を回避できるはずです。

参考になりますでしょうか？

補足：
ここまでの案件ですと「ER図」を起こした方が、良いと思います。
↓はER図は起こさず、頭の中だけで、テーブル定義しました。
ちゃんとER図を起こすと、正解のテーブルが定義出来ると思います。

＜ユーザテーブル＞
ユーザID  項目ID
1        1
2        2
3        1
key：ユーザID

＜友人テーブル＞
ユーザID  友人ID
1        2
2        1
3        1
3        2
key：ユーザID, 友人ID

＜項目テーブル＞
項目ID  項目連番  項目名
1      1        名前
1      2        年齢
1      3        職業
2      1        名前
2      2        彼氏の有無
2      3        既婚か未婚か
key：項目ID, 項目連番

＜ユーザ項目テーブル＞
ユーザID  項目連番  値
1        1      （名前）
1        2      （年齢）
1        3      （職業）
2        1      （名前）
2        2      （彼氏の有無）
2        3      （既婚か未婚か）
3        1      （名前）
3        2      （年齢）
3        3      （職業）
key：ユーザID,  項目連番

Comments

[zyno]

返信ありがとうございます！返信が早くてとても嬉しいです！

質問欄で①の形を棄却したのは、カラムに「名前」や「年齢」を入れる形式だと、例えばユーザーが「狙ってる人」「好きな食べ物」「彼女の有無」などを入れる際にその度に、テーブルにカラムを追加しなければならないからです。そして、DBに追加する友人一人一人によって必要な項目が変わってくる可能性があるため、その度にカラムが増えるのは面倒だと感じたためです。（友人Aの場合は職場の友達なので「名前」「年齢」「職業」を項目に入れたい。友人Bの場合は出会い系で出会ったので、「名前」「彼氏の有無」「既婚か未婚か」などを項目に入れたいなど）

そして、質問欄②はその項目を自由に記載できるため、いいなと感じました。しかしこの場合、全てのユーザーの全ての友人が1つのテーブルに混ぜこぜにしてしまいます。ユーザー1人が友達を平均30人登録し、ユーザー数が３０００人と仮定した時のレコード数は90000行であり、検索速度が落ちる可能性が怖かったのもあり、今回質問をteratailに投げました。

とりあえず検索速度に関してはいけそうだということが分かったので、質問欄afterのDB設計でやっていきたいと思います。ありがとうございます。

[HouraijiIC]

＞DBに追加する友人一人一人によって必要な項目が変わってくる可能性がある

であれば、私なら、必要な項目が変わってくる「（第３）テーブル」を起こしますね。で、そのテーブルは「全項目」を「フィールド定義」したものになります。（随時追加、、、alternate）」（＊１）

３テーブルのjoinとselectの仕方は、もう、分かりますよね。

分からなくても、検索すれば、情報は、たくさん得られます。

＜追記＞
（＊１）『「全項目」を「フィールド定義」』は、どうも、違和感を感じます。第２テーブルのIDと第３テーブルのID対フィールドの定義、、、何か良い方法があると思うのですが、、、分かったら、コメントします。

[zyno]

返信ありがとうございます！

第３テーブルというのは、例えばusersテーブル、user_friendsテーブルがそれぞれ第1テーブル,第2テーブルだとして、第2テーブルの項目カラムのフォールド内に入る値を数値などに置き換え、第3テーブル内でそれぞれ各項目の数値を定義するということでしょうか？

補足の表を上に提示しました。こういう考え方をしています。

[HouraijiIC]

先のコメントでの『「（第３）テーブル」を起こしますね。』は、すでに、回答内で
＜＜＜ここは＞＞＞
user_friendsテーブル
友人ID 項目 内容
1 名前 A子
↓
友人ID 名前 性別 。。。
2 B太 男 。。。
として提示していました。
これを「（第３）テーブル」と呼んでしまいました、故、違和感を感じた訳です。

要件定義から、起こしてみたテーブルを回答に追記しましたので、参考になるかどうか、見てみてください。

＞補足の表を上に提示しました。こういう考え方をしています。
この考え方だと、select出来ないのでは？と思います。
テーブル定義は、
「ID, 項目（ID）, フィールド1, フィールド2, 。。。」
とし、IDによる（SQL文やモデルロジックでの）リレーションを行います。

[zyno]

返信ありがとうございます！

頭の中がこんがらがってしまったので、HouraijiICさんに記載いただいたテーブルに若干の内容変更をしましたが、ER図を追記しました。このER図で何か変なこととかあったりしますでしょうか？

〜変更内容〜
HouraijiICさんの要件定義にある＜ユーザテーブル＞というのは、こちらでは登録ユーザーという認識です。登録ユーザー自身もプロフィールを作るのであれば必要になりますが、今回は実装しない予定です。その為、＜ユーザテーブル＞に存在する項目IDカラムは必要ないと考えられるので削除をしました。

[HouraijiIC]

friend_itemのnameがname_value（itemのnameフィールドに対応する値）であれば、これで大丈夫だと思います。

view, controller, model（一旦、ダミーデータ：コード埋め込み値でのcontrollerとの接続）をコーディングして、viewとcontrollerの基礎を固める。

次にmodelのダミーデータをdb接続に変更する。
色々なフレームワークがあるので、採用したフレームワークでとことんdb接続格闘する。
どうしても意図通りにならない場合は
・controllerとmodelの接続にもどる
・er図に戻ってdb設計を見直す
どちらを優先するかはwebアプリの実装者にしか決められない。
（なんともならない場合はmodelにて素のsql文を書いてcontrollerで頑張る。この場合は、なんとでもなるかわりにsqlインジェクション対応を漏れなく実装しなくてはならないので、お勧め出来ない。）

controller, model, dbが固まったらviewを整える。

といったところでしょうか。

参考になれば幸いです。

Answer 2

DB設計において「第三正規化」について、調べられましたか？
DB設計は
「第一正規化」
「第二正規化」Toxi法
「第三正規化」
があり、「第三正規化」までを理解すると、DB設計はかなり楽になります。

「Toxi法　第三正規化」で検索すると、少ないですが、解説Webがヒットします。

Comments

[zyno]

早速の回答ありがとうございます。第三正規化の記事を閲覧し、正規化ができました。質問欄に追記する形で正規化後のテーブルを記載しました。個人的にはこれ以上細分化はできないと考えているのですが、考慮する点等はございますでしょうか？検索の速さをどうするかというのが個人的には気になっています。インデックスを追加するだけでいいでしょうか？