データベース設計とは？テーブルの分け方と関連付けを具体例で解説｜確認問題の回答例
データベース設計とは？テーブルの分け方と関連付けを具体例で解説の確認問題の回答例です。各テーブルの1行が何を表すか、どのID同士を対応させるかに注目して確認しましょう。
問題1：現在の利用者情報を1か所にまとめる
学習記録の各行に利用者名も保存しています。利用者1001の名前を変更したところ、記録101だけ新しい名前になり、記録102は古い名前のままでした。どの情報を別のテーブルに分け、何で関連付けるとよいですか。
利用者の現在の情報をusersへ分け、id1001の行に名前を1か所だけ保存します。学習記録はstudy_logsへ入れ、user_id1001でusers.idを参照します。名前を変えるときはusersの1行を変更します。登録当時の名前を残す要件なら別の設計が必要ですが、今回は現在の名前を表示する条件です。
問題2：記録IDと所有者IDを区別する
田中さん（利用者ID1001）が学習記録を3件登録します。study_logs.idとstudy_logs.user_idには、それぞれどのような値を入れますか。例を挙げてください。
| 列 | 値の例 |
|---|---|
| study_logs.id | 101、102、104のように、study_logs内で重複しない別々のID。 |
| study_logs.user_id | 3行とも1001。3件すべて同じ田中さんの記録だから。 |
記録103は図では佐藤さんの記録として既に使われています。空いているIDを使い、登録時の採番はDBに任せます。user_idを重複禁止にすると1人1件に制限されるため、この関係には合いません。
問題3：関係の定義とアクセス制御を分ける
列名をuser_idにすれば、存在しない利用者の記録も、他人による閲覧も防げると考えました。それぞれ、何が別に必要ですか。
存在しない利用者を拒否するには、study_logs.user_idがusers.idを参照する外部キー制約を定義し、有効にします。SQLiteでは接続ごとの有効化も確認します。所有者なしを許さないためNOT NULLも指定します。列名だけでは制約にはなりません。
他人の閲覧を防ぐには、アプリ側でログイン中の利用者を確認し、その利用者IDで取得対象を絞ります。外部キーは、現在の操作者に閲覧権限があるかまでは判断しません。登録時の所有者も、送信された任意の利用者IDではなくログイン情報から決めます。
問題4：型と必須指定だけで済ませない
学習内容にNOT NULL、学習時間にINTEGERを指定しました。空文字の学習内容や、0分の学習時間を必ず拒否できるでしょうか。このSQLiteの設計例で必要な確認を答えてください。
その指定だけでは足りません。NOT NULLが拒否するのはNULLであり、空文字や空白だけの値は別に確認します。学習内容は1〜200文字か、空白だけでないかを確認します。時間は整数かつ1〜480かを確認します。SQLiteの通常のテーブルではINTEGERという型名だけで厳密な整数限定にもならないため、DBのCHECK制約へ型と範囲の条件を定義します。
画面入力はアプリ側で確認して理由を伝え、DBにも必要なNOT NULL・CHECK制約を設定します。実際に空文字・0・481などを拒否できるか、定義と動作の両方を確かめます。
問題5：保存する値と表示文を分ける
画面には「メモはありません」と表示したいので、未入力時にその文章をDBへ保存する案が出ました。この記事の設計では、何を保存し、いつ文章を作りますか。
未入力ならNULLへそろえて保存し、一覧の表示時に「メモはありません」という文章を作ります。画面向けの案内文をメモ本文として保存しません。この方針なら案内文を変えるだけで、過去の記録を書き換えずに表示を変更できます。NULLと空文字を混在させないように、登録・取得・表示の扱いをそろえます。