親子関係にあるとき、親が削除されたときに子をどうするか決めておきます。

RESTRICT　子があるとき削除できない

CASCADE　子もすべて削除される

SET NULL　子の参照をNULLにする

論理削除　削除フラグを立て子はそのままにする

データを削除する際、実際物理的にはデータベースから削除せずに見かけ上消す論理削除がしばしば用いられます。この利点は、誤削除しても復活させる手立てを残しておくことや、削除されたものを後で参照できることなどがあります。削除というよりは無効化という方が適切な場合があります。

例えば、案件テーブルに担当者を入れる項目があったとしてます。論理削除であれば、担当者が退職した場合でも、あとで誰が担当者であったかを分かるようになります。一方物理削除であれば、その案件の担当者が誰だったか分からなくなります。

ただ、便利な反面、アプリケーション作成上注意する点がいくつもあります。

○　一意制約と削除フラグ

一意制約のあるテーブルで削除フラグを用いると厄介なことになります。

削除フラグが立っていないものに対して、一意性を保証する必要がある場合、その実現には、

１．挿入・更新前に対象列が重複していないかチェック

ただ、その際、他の同じ挿入・更新を行なうプロセスに対してコミットするまでの間排他制御を行なう必要があります。トリガーで行なう方法も考えられますがアプリ側で制御します。

２．ユニーク列を作り、対象列と同じ値を入れる

削除フラグを立てるときにユニーク列をNULLにします。ただしこれはそのためだけに列を一つ増やすので長い列の場合薦められません。また作成・更新のSQL文に常にユニーク列の分同じ値を入れる必要があります。トリガーが使えるなら自動的に挿入されるのが望ましいです。

３．一度使用した値が削除された場合、再利用禁止

再利用するには復活するか物理削除してからにします。

４．削除時に、対象列に_DEL番号を付加

番号はシーケンスから取得します。重複している場合は次の番号を使用します。シーケンスがないMySQLでは、シーケンスオブジェクトから値を取得するようにします。

５．削除テーブルを用意する

これはログのようなもので、参照先のIDのほか、名称も記録しておきます。ただし、参照しているテーブル側で結合ができないので特別な処理が必要になります。

６．削除フラグを1以上の数にして増やしていく

そして、削除フラグをユニークキーに加えます。

○　登録と表示・更新・削除で表示を変える

先の案件テーブルの例の場合、新規登録画面では論理削除された担当者は選択できないようにし、表示、更新、削除ではその担当者が表示されるようにします。多くの場合、登録画面と更新画面を一緒にしているケースが多いので、この部分で条件分岐が必要になります。

○　物理削除のインターフェース

論理削除の場合、物理削除を画面からするか、定期的にバッチを発行して削除するかによって工数が変わってきます。また物理削除したものを復活できるようにするとなると、さらに工数がかさみます（一意値の重複問題とかいろいろ考慮することがあります）。

○　履歴テーブル

論理削除でも、最終的には全削除する場合には、削除しない履歴テーブルなどには、関連を持たせないようにします。履歴テーブルには当時のIDと名前を関連なしに入れておきます。

実際、論理削除を用いることは、一意制約や参照制約を意味のないものにしてしまいます。親エンティティが論理削除されていても外部キーとしては存在しているので登録できてしまいます。そうならないためにはアプリケーション側でチェックする必要があります。残念ながら、DBで制約を使えば、整合性のあるデータしか格納されないようになるというふうにはなりません。

ユーザIDは、ログインIDと内部IDにわけるようにします。ログインIDが変更されることがなく、ユーザの削除は物理削除である場合は、一緒にしても構いません。IDを変更する可能性があると、参照しているすべてのテーブルの値を変更する必要が出てきます。

複合キーを使うか否かというのは議論の分かれるところですが、できればキーは単一の方が何かと扱いやすくていいと思います。関連テーブル以外では、複数の主キーは避けた方がいいです。

論理設計の段階では、親に紐づくすべてのテーブルの主キーに親のIDが入っていますが、物理設計では、親IDは主キーではなくなります。ただインデックスと参照制約を設定します。論理上の設計と、物理上の設計の乖離する点です。

代理キーの利点としては、

・主キーの生成が単純で、通常数字のインクリメントでよいこと。

・１カラムで済み、アクセスしやすくなること。

が挙げられます。複合キーの場合、当該レコードを指定するために複数のパラメーターが必要になります。

また、できればキーは無意味な自動的にインクリメントされる数値の方が望ましいです。またIDを連番にして、順番に意味を持たせると、挿入、削除で意味が崩れることになり、また途中で登録に失敗した場合空き番がでることになりますので、IDには意味を持たせない方がいいです。

同時にデータの更新を行なう場合、以下のような問題があります。こういう問題は、一人でテストしている間は発覚せず、複数の人でテストして始めて発覚することが多いものです。しかし事前に組み込んでおく必要があります。

・一意値同時更新＆登録
・一意値同時登録
・同時更新
・参照先削除
・同時削除
・同時削除・編集
・ログイン中ユーザ削除

○　一意値同時登録

一意である必要がある値を登録する場合、DBでは当然ユニーク制約を張ると同時に、事前にあるかどうかselectする場合と、チェック制約に引っ掛けてExceptionを拾う場合とがあります。事前にチェックする場合は、厳密には、チェック後更新するまで他のプロセスをロックする必要があります。というのは同時に二つのスレッドが同時にSelectを発行した場合、両方ともチェックをすり抜けてしまうからです。

○　一意値同時更新＆登録

一意の値を変更できる場合、新規に登録、あるいは他のプロセスが同様に更新する場合に、重複する場合があります。同時登録と同じ処理が必要になります。

○　同時更新

これについては、以下の３通りの対処があります。

１．悲観的ロック

　ユーザが編集を開始したものについてはロックをかけ、ユーザが編集を終了するまで、他のユーザには更新させません。

２．楽観的ロック

　編集開始時にはロックをかけず、DB更新時にロックをかけます。もし自分が編集を開始した後に他のユーザが更新をかけている場合は、ロールバックします。

３．いずれも行なわない

　この場合、あとで更新されたものが有効になります。

Webアプリケーションで悲観的ロックは望ましくありません。するとしたら、タイムアウト時間を設けて、一定時間を過ぎたらロックを解放することです。

また、楽観的ロックを行なう場合、他のユーザが更新した場合、ロールバックするのではなく、上書きするか否かをユーザに確認するのもいいでしょう。

○　参照先削除

編集している間に、参照先が削除されていた場合、参照制約エラーが出るので、それに対する対応をする必要があります。ただし、参照先が論理削除されていた場合、参照制約エラーが出ないので、この問題に対応するかどうか検討する必要があります。

○　同時更新・削除

更新後に削除の場合は問題ありません。削除後更新した場合、エラーメッセージを表示するか、あるいは表面上、更新後削除されたことにするかです。

○　同時削除

同様に問題はありません。親切にすでに削除されている旨メッセージを出してもよいでしょう。

○　トランザクションの一貫性

参照した値を元に更新を行う場合、参照した値に一貫性がない場合、不整合が起きます。例えば最初にテーブルAから値を参照し、ついでテーブルBから値を参照し、その和をテーブルCに書き込む場合、もし他ユーザがテーブルBを先に更新してしまった場合、トランザクションを開始した時点での値ではないため、和は不正なものになります。この場合トランザクションの分離レベルをSerializableに設定する必要があります。

しかし、そもそもトランザクションの分離レベルをSerializableにする必要があるような設計は正規化が取れていず、望ましくありません。参照だけの場合であれば、読み取り一貫性が保証されるだけのトランザクションを開始すればいいです。パフォーマンス上の問題で集計結果をテーブルに保存しておいて毎度集計する必要がないようにしたい場合は、マテリアライズドビューを使うか、READ ONLYでトランザクションを開始し、結果をアプリケーション側で保持しておいて、一旦トランザクションを終了させてから、再度集計テーブルに書き込むようにすればいいでしょう。

○　ユーザ削除後のユーザ操作

１．セッションを無効にし、次の操作をさせない（強制ログアウト）ようにします。

２．削除された旨をユーザが次の操作をしたときに表示するようにします。この場合、１よりもプログラムが複雑になります。

ログアウトするまで有効にする方法もありますが、すでに無効になっているユーザで、DBの更新をするのはよくありません。

○　共有オブジェクトについても

共有オブジェクトについてもデータベースと同様に同時更新や読み取り一貫性に配慮する必要があります。

○　DB以外のトランザクション

DBのトランザクションはDBが役割を果たしてくれますが、ファイルや他のプロセス、システムにまたがるトランザクションは難しいものです。この辺も十分に検討が必要です。

複数のユーザが同時に操作を行なう場面では、同じリソースに同時にアクセスするときがあり、そのときにどう対処するのかをしっかり検討しておく必要があります。

ロックはパフォーマンスを劣化させるので、ロック取得時間を短くするか、ロックせずに異常時に例外処理をするなどの検討もします。またアプリケーション側でロックする場合、同じ更新を行なう全プロセスがそこを経由するようにする必要があります。

同じIDで複数の端末から同時にログインした場合のことを考えます。対応としては、許可する、禁止する、後のものを有効にし前のものを強制ログアウトするという方法があります。禁止すると、片方がログインしたまま、フリーズすると、タイムアウトするまで扱えないことになります。不正利用されているケースを考えるのでなければ、セッション情報は別々になるので、データの不整合はそれ自体では通常は起きないので、許可しても普通は問題ないでしょう。

一つのブラウザから、もう一つウインドウを開くとセッションを共有してしまうため、セッション情報が書き換わって誤動作を起こす可能性があります。この対処は結構難しいです。そういう使い方をさせないようにメニューバーを隠してしまうというのも一つの手です。

データの整合性を保つためには、Validationが欠かせません。データベースでは型やレングス、制約があるため、その範囲では整合性を保つことができますが、それ以外に値に制約がある場合、アプリケーション側でも確認する必要があります。通常はデータベースの制約に引っかかる前に、アプリケーション側でValidationチェックを行い、ユーザにエラーを返します。

１．入力前

　ユーザにそもそも間違った値を入力させないようにします。

　選択肢でNULL値をなくす、表示しない、ボタンを無効にする、ReadOnlyにする、テキストボックスのサイズを設定するなど。

２．入力時（Javascrip＆サーバーサイド）

　入力値のチェック（必須、レングス、フォーマット）

３．DB更新時のチェック（直前のアプリケーション、トリガー、DB制約）

　一意制約・参照制約等のチェック。基本的に前でチェックしているので、ここでエラーになるケースは異常な事態になる。ただここでしかチェックできないものは、ここで例外を正しく拾ってユーザに通知する。

Javascriptを無効にしているケースもあるので、サーバー側でのValidationはJavascriptと重複していても必要です。