大部分の項目は共通ではあるが、レコードによって微妙に異なる項目を保持したいというケースが存在します。
例えば、商品の他に期間限定商品が存在する場合などです。
大部分のレコードは商品ですが、一部の期間限定商品については販売期間の項目を持ちたいとします。
その場合、どのようなテーブル構造にすればよいかを考えていきます。
アンチパターン：汎用可変項目を作る
このようなものによって微妙に項目が異なるデータを表現する時にしばしば汎用可変項目が使われます。
拡張項目を別テーブルに切り出します。
属性カラムと値カラムを持たせ、汎用的に使用できるようにします。
(このような設計をEAV、エンティティ・アトリビュート・バリューなどと呼びます。)
これならどんな商品フォーマットが追加されても柔軟に対応できそうに見えます。
しかし、この設計には以下のような問題点があります。
汎用可変項目の問題点
必須制約を設定できない
期間限定商品であれば、販売期間の項目があることが期待されます。
しかし、この構造だと販売期間の項目が存在することがDB側の機能で保証することができません。
仮に商品テーブルに商品タイプのような列を追加しても同じことです。
DBの制約で、ある項目の値に応じて、他テーブルとの関連を強制するルールを設けることはできません。
データ型を設定できない
汎用項目であるがゆえに、データ型に制約を設けることができません。
属性カラムの値に応じて値カラムの制約を動的に変更することは、現状のDBMSではできません。
参照整合性を設定できない
データ型と同じで、属性カラムの値に応じて参照整合性を動的に変更することはできません。
行の再構築が必要
項目を別テーブルで複数行にまたがって持っているため、データを参照するときに行を再構成しないといけません。
SELECT d.id, a1.attr_val, a2.attr_val, a3.attr_val FROM data d LEFT OUTER JOIN attr a1 ON d.id = a1.id AND a1.attr_name = 'attr1' LEFT OUTER JOIN attr a2 ON d.id = a2.id AND a2.attr_name = 'attr2' LEFT OUTER JOIN attr a3 ON d.id = a3.id AND a3.attr_name = 'attr3' WHERE d.id = '123'
このように属性の数だけテーブルを結合しないといけません。
また、属性の数が増えた場合、クエリを変更しないといないなど、クエリの柔軟性が低くなります。
アンチパターンを用いても良い場合
この構造は柔軟にテーブルの構造を変えられるというメリットがありながらも、データの整合性を保てない、構造がわかりにくくなるといった大きなデメリットも持ちます。
さらにこのメリットは、後述するテーブル構造によって実現できる、かつ柔軟で動的な構造はRDBの領分ではないとう理由からあまり有用なものではありません。
柔軟な構造を実現したいのであればNoSQLなどの採用を検討するべきです。
解決策
解決策としては以下の三つが存在します。
シングルテーブル継承
シングルテーブル継承では、全てのデータを一つのテーブルで保持します。
欠点としては、どのデータがどの項目を有するのかがわかりにくい、NULL値と非NULL値が混在することです。
拡張クラス、項目数がそれほど多くない時は有効な手段になります。
具象テーブル継承
サブタイプごとにテーブルを分けます。
共通項目はテーブル間で重複します。
そのため、共通項目に変更があった場合は、全てのテーブルに変更を加えないといけない手間が発生します。
また、一見しただけではどれが共通項目なのかわからないという問題もあります。
すべての項目を跨いで検索をかけるような操作が少ない場合は有効な設計と言えます。
クラステーブル継承
基底クラスと拡張クラスを別テーブルに分割します。
RDBの構造に沿って分割しているため、1対1の構造が明確になります。
(拡張テーブルのid列に一意制約、外部参照制約を設定することで可能。)
基底タイプの項目を網羅的に取得することも簡単です。
基底テーブルのみを検索すれば実現できます。
拡張タイプを跨ぐ場合も、全てのテーブルをjoinすれば実現可能です。
存在しない項目はNULLになります。
SELECT * FROM data d LEFT OUTER JOIN sub_class1 s1 ON d.id = s1.id LEFT OUTER JOIN sub_class2 s2 ON d.id = s2.id;