レコード変更時のデータの扱い方

Question

データ変更に伴うレコードの扱い方をどのようにしたらいいか判断がつかないので質問させていただきます。

テーブルがuser(以下U),schedule（以下S）,user_scuedule（以下US）と３つあり、USは中間テーブルので、持っているのはUS_id(主キー),U_id,S_idの３つです。UとSはそれぞれお互いのデータは持っていません。USテーブルの１つのレコードにはUS_id,U_id,S_idがそれぞれ1つあり、１対１対１になります。
１つのSに複数のUが参加する場合USテーブルのレコードは
US_1,U_1,S_1
US_2,U_2,S_1
US_3,U_3,S_1
︙
という感じなっています。

登録の際はフォームのPOSTにSとU_idのデータがあり、SのデータをSにINSERTして、発行されたS_idとPOSTのU_idをUSにINSERTしています。

このような場合、データ変更、削除の場合、USテーブルのデータはどのようにしたらいいでしょうか？
削除の場合
Sが削除された場合はUSの中から削除されたSのS_idと一致するUS_idを削除。
Uが削除された場合はUSの中から削除されたUのU_idと一致するUS_idを削除。

変更の場合
SやU自体が変更された場合はそのまま何もしない。
Sに参加するUに変更ある場合は、そのS_idを持つUS_idを削除して、変更後のデータをUSにINSERT（新たに別のUS_idが発行される）。

と今思いつく限り書きましたが、このような感じになりますでしょうか？アドバイスなどよろしくお願いいたしますm(_ _)m

Answer 1

中間テーブルへのデータ登録 にかなりの回答が載っています。それを放置して新しく質問するのは止めましょう。

削除の場合

Sが削除された場合はUSの中から削除されたSのS_idと一致するUS_idを削除。
Uが削除された場合はUSの中から削除されたUのU_idと一致するUS_idを削除。

yambejpさんの回答の「まとめ」のCREATE TABLEに親テーブルをDELETEしたら子テーブルも自動的にDELETEしてくる記述が載っています。CREATE TABLE, INSERT を実行してデータを用意し、親テーブルを1行DELETEして動作確認しましたか？

foreign key(sid) references schedule(sid) on delete cascade,

foreign key(uid) references user(uid) on delete cascade,

Answer 2

原則マスターデータを削除するということはないというのが回答。
ユーザーが抜けたりスケジュールが終わったりしても
usは中間テーブルでありログでもあるので、履歴は残しておいてかまわないし
逆に生きているレコードだけ残しておくとしてusをいじったとして
sやuに影響することはない

また変更については、そもそもがuidやsidが変更する運用はしてはいけない

ということで、やらないほうがいい前提を理解した上で
上記２点についてどうしてもやりたい場合、

• uやsのレコードが削除された場合usの該当レコードを削除したい

　→usのuidやsidのカラムに外部キーを設定する

• uでuid、sでsidが変更されたらusの該当idも変更する

　→uやsにtriggerを設定しておく

Answer 3

データ変更、削除の場合、USテーブルのデータはどのようにしたらいいでしょうか？

USテーブルは追加と削除でいいんじゃないでしょうか。

Sに参加するUに変更ある場合は、そのS_idを持つUS_idを削除して、変更後のデータをUSにINSERT（新たに別のUS_idが発行される）。

履歴を作るイメージだとしたら、そんなの必要ですか？

もし更新があるとしたら、USテーブルにSへの参加日時みたいなのを項目として持つなら、有りだとは思いますけど。

Answer 4

仕様次第かと。

そもそも S や U の削除も、物理削除がよいか？論理削除にすべきか？と言った話も出ると思います。