DB設計

設計時に注意すべき点

このテーブルからレコードを削除したらどんな問題がでるか？

判断の悩みどころ

NOT NULLをつけるべきか？

できるだけつけたほうがいいと思う。

参照整合性を使うべきか？

参照整合性(外部キー)制約 使う？使わない？
http://www.mahoroba.ne.jp/~mw_ken/foreignkey.html

自然キー、代理キー

代理キーは使ったほうがいいと思う。
Rails ではそう決まってるし。

代理キーのメリット

変更に強い。

• ユニークだと思っていたものがユニークでなくなった場合
• 自然キーのカラム名を変更したい場合（代理キーを使っていれば、自然キーによる他テーブルの結合は少なくなっているので、その分楽）

代理キーが完全にムダになる場合

しかし、代理キーがあっても他のテーブルと結合するときは自然キーしか使わないような
設計にする場合もある。
例えば、次のようなテーブルがあるとする。

tb_user ユーザーテーブル
tb_userkind ユーザー種別（admin, user, guest等）
ユーザーテーブルはユーザー種別への外部キーを持っている。

そして、アプリケーションで新規ユーザー登録したときのデフォルトユーザー種別はuserであるとする。
つまり、
1. アプリケーションのSQLでuserを表す値を明示的に指定する
2. tb_user.ユーザー種別のデフォルト値をuserにしておく
のどちらかである。
そしてtb_userとtb_userkindを
A. 代理キーで結合する
B. 自然キーで結合する
選択肢は2×2で4通り。
1-A の場合：もしtb_userkindのuserのレコードを誤って削除してしまった場合、もう一度insertしようにも同じ番号は得られないので、次ユーザー登録したときおかしなことになってしまう。
2-A の場合：誤って削除した場合、デフォルト値を変更すればなんとかなる。
1-B の場合：もう一度insertすれば復旧できる。
2-B の場合：もう一度insertすれば復旧できる。

つまり、Bにしておけば1,2どちらの方法を採用してもよいので、AよりBの方がよい設計だといえる。

しかしまあ、代理キーはあってもムダなだけで実害は小さいので、全てのテーブルにつけておいてもいいかもしれない。

プログラミングするとき

insert, update は失敗する可能性を考える

ここでは単なる入力値検証漏れ以外のことを考える。

select は失敗する可能性を考えなくていい。

delete が失敗する可能性があるのは、

1. 他のテーブルから参照整合性制約が張ってあるとき
2. 該当レコードが存在しないとき

2. の場合は無視していい。
1. の場合は画面にメッセージ表示。
「この○○は××であるため削除できません」

insert, updateが失敗する可能性があるのは

• 一意制約違反
  画面にメッセージを表示する
  「この○○は既に登録されています」
• 参照整合性違反
  これは原因がわかりにくいがありうる。
  画面にメッセージを表示する
  「他のユーザーによって関連情報が更新されている可能性があります。○○画面よりやり直してください」など

updateが失敗する可能性があるのは

PEAR::DB + mysql のエラーコード

insert 一意制約違反 -5 already exists（ただしポータビリティが有効になっている場合は -3）
delete 対象なし エラーなし affectedRows == 0
update 対象なし エラーなし affectedRows == 0
insert 参照整合性 -3 constraint violation