並び順をカラムに持つテーブルの設計

DB

最近の話ですが、もともとフラットに管理されているデータに、次の要件を追加する対応をしました。

  • カテゴライズ
  • カテゴリの中で任意に並び替え可

で、この要件を実現するためにテーブル設計を変更したのですが、なんとなくイマイチな感じになってしまったので、後学のために再考しています。備忘録。

もともとフラットに管理されているデータがある。

f:id:knt_mr:20200718012208p:plain:w160

これにカテゴリの機能を追加する。カテゴリは1階層のみ (カテゴリの入れ子はない) で、データは1つのカテゴリに属する。

f:id:knt_mr:20200718012631p:plain

さらに、カテゴリの中で任意にデータを並び替えられるようにする。いくつか案を考えたのですが、並び順をカテゴリに対する属性と考えて、こんな感じにしてみました。

f:id:knt_mr:20200718012950p:plain

一応、これで実装は進めたものの、なんとなく違和感が...。関連テーブルと属性テーブルにそれぞれデータIDとカテゴリIDがあって役割が被ってるような気がする。属性テーブルの設計がイマイチっぽい。

というわけで、こんな感じにするのはどうだろう。

f:id:knt_mr:20200720172246p:plain

あるカテゴリのデータを並び順で取りたいときはこんな感じ。

SELECT * FROM Item as i
JOIN ItemCategoryRelation as rel ON rel.item_id = i.id
JOIN Category as c ON c.id = rel.category_id
JOIN ItemCategoryAttribute as attr ON attr.relation_id = rel.id AND attr.item_id = i.id
WHERE c.id = 12345
ORDER BY attr.order

うーん、特にクエリがシンプルになるわけでもないし、どっちもどっちな気がする...。現場からは以上です。