[RDB]レコードに任意の順序をもたせる方法を検討

下記のようなデータがある場合

• データの順序を保持する方法
• ID5と3の間に新しいレコードを追加する方法
  • 先頭に移動する方法
  • 最後尾に移動する方法
• ID4をID1の直後に移動する方法
  • 先頭に移動する方法
  • 最後尾に移動する方法

案1
各レコードにorder_numberをもたせる。カラムとして持っているので取得にorder句で簡単に取得できる。

みたいな感じ

• 追加のとき
  • 後ろのレコードを全てUpdateする必要がある
• 入れ替えるとき
  • 移動したレコード以降の全てのレコードをUpdateする必要がある
• order_numberにユニーク制約を付けることでデータ不整合が起きない
  • ただし、歯抜けなどは発生する可能性はある

案1.1
案1のorder_numberの間隔を予め広げておき、途中に挿入したときに他のデータを更新しなくて良くする。
下記に書いてあるようなやりかた。
https://teratail.com/questions/37795

データの更新量は減るかもしれないが、間隔が詰まったときに振り直したりするなどの考慮が必要になる。
案1の更新量がどうしても許容できない場合を除き、余計な実装が増えるので微妙だと感じた。

案2
各レコードに前のレコードID(もしくは後ろのレコードID)を持たせる。

順序の更新は前後を直せばよいだけになるが、SQLで順序を考慮して取得する方法が思いつかない。

また、誰からの参照されない(before_idがない)孤立したレコードができても制約等では気づけない。

案3
順序を保持するレコードを別途作成する。
例えば、sorted_idsに並び順のIDをカンマ区切りで保持

• 取得時はorder句でfieldを使えば順序どおりに取得できる。
• 更新時も1レコード更新すればOK
  • ただ、カンマ区切りの文字列にしているためDBのユニーク制約や外部キー制約が使えないので、データ不整合が起こりうる
    • データ不整合ツライ。。。やるとしたら不整合データになっても利用継続できて、次の更新時や定期的に自動復旧されるような仕組みを考える必要がある。

レコード数が大量になる場合、案1や3はツラくなる。
1は更新対象が多すぎ。3は保持するレコード長が長くなる。

案2で取得がスムーズにできたら一番良い気がする。

逆にレコード数が100以下とかであればどの方法でもよさそう。
案2も全件取得してアプリで並び替えたりできる。

連結リスト
https://ja.wikipedia.org/wiki/連結リスト

特に件数が膨大とかがなければ案1がシンプルで良い気がしてきた。
多少効率が良くても凝ったやり方をすると後々メンテが面倒になりそう。