RDBで1レコードに可変個数のデータが対応するときのUPDATE

1つのレコードに可変個数のデータを持たせるデータの構造なときです。

こんな感じの構造です。

json
1{
2  title: "a",
3  data: [
4    {name: "x"},
5    {name: "y"},
6    {name: "z"},
7  ]
8}

これを

table_main
 - id (autoincrement)
 - title

table_sub
 - id (autoincrement)
 - mid (fkey to table_main.id)
 - name

のようなテーブル構造で保存してるとします。

データを

json
1{
2  title: "b",
3  data: [
4    {name: "y"},
5    {name: "z2"},
6  ]
7}

のように変更したときに、 table_sub はどのように更新処理すべきでしょうか。

この場合だと、xがなくなって、yはそのまま、zがz2変更となっています。
こういう情報を更新する時に、SELECT で取ってきたデータと更新するデータとを比較して削除・追加・変更の3パターンに分けてそれぞれ処理するというのは大変です。
なので、普段は table_sub に対して mid で検索して更新対象の mid のデータを全件削除して、新しい登録データを新規に INSERT してます。

data の配列が数千とかあるわけでないなら速度面でも対して気になるものではないのですが、data のような可変個のデータを持つものが4つ、5つとあるような時で、title だけしか変更してないのに全部削除＋新規追加ってムダだなーと思いました。

それに、もし table_sub の id に別テーブルから外部キーが設定されていたら、全件削除してとはいかなくなりますし、なにか良い方法はあるのでしょうか。

行動規範の内容に同意します

回答3件

結論は出てるようですが…
将来的に? table_sub に更新日付を入れよう
なんて話になったら個別にちまちま更新かけるしか
なくなるので、そのへんちょっと心配かなあ。

投稿2016/07/30 15:42

takasima20

総合スコア7458

lazex

2016/07/31 08:14

更新日時でも変更してない行を消してしまったらだめですね。必要になれば編集する側で変更情報を保持させる作りにすることにします。

行動規範の内容に同意します

上記のテーブル構成にて、更新を効率よく行う方法として、josnで受け取る情報に
title: "a"は"b"に変わりました。name"x"は削除されました。name"z"は、"z2"に更新されました。
と言う情報をjsonで受け取とる方法が有ります。(jsonにはこだわりませんが)
上記受信jsonの場合は、title"a"のsubメンバー構成を全件削除して、titel"b"に更新してsubをインサートするしか私も考えが浮かびませんでした。

例えば、クライアントのプログラムで表示して更新、削除を行っているとする場合、クライアントでは、この差分情報の作成が可能と思います。

投稿2016/07/29 18:06