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

今回、フラットに管理されているデータに、カテゴリ機能を追加し、さらにカテゴリの中で任意に並び替えられるようにしたのですが、なんかテーブル設計がイマイチな気がしたので後学のために再考しています。備忘録。


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

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

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