以下のような商品情報と商品パラメーターを格納したテーブルがあります。
[Itemsテーブル]
・item_code - 001,002...
・item_name - 麦茶,iPhone...
・item_price - 150,30000...
・item_group - A,B...
[paramAテーブル]
・item_code - 001
・volume(内容量) - 2000
・cal(カロリー)- 50
...
[paramBテーブル]
・item_code - 002
・weight(重さ) - 150
・height(高さ) - 100
...
このように商品属性(item_group)によって保存したいデータの種類、カラム数が全く異なるため、商品属性によってパラメータテーブルを分ける設計をしました。
Itemsテーブルは10万件、商品属性は20-30種類程です。
まず、このようなテーブル設計は適当でしょうか?
次に、商品名からパラメータを検索したいとします。この時、どのグループに属する商品が検索されるか分からないものとします。
SQLの書き方として select * from Items join (paramA union paramB union ... paramZ) where Items.item_name = ...
くらいしか思いつかなかったのですが、非常に遅いかつスマートでないSQLだと思います。
この場合の高速かつスマートなSQL文はどのようになるのでしょうか。
以上2点よろしくお願いします。
まず[Itemsテーブル]のそれぞれの項目に カンマ区切りで データを入れるのは
よくないです。
あと、商品名からパラメータを検索したい場合は、[Itemsテーブル]だけ検索すればよいかと思いますが・・・。
カンマで区切って1つのフィールドに保存し、APP側で分解しようと考えましたが、パラメータから商品を検索することも多々ありますので、この方法は使えないかなと思いました。
unionを使う必要はありません。
下記のようにして外部結合できます。
left joinを明示的に使ってもいいです。
select items.item_code, items.item_name, items.item_price, paramA.volume, paramA.cal, paramA.weight, paramA.height from items, paramA, paramB where paramA.item_code = items.item_code and paramB.item_code = items.item_code ;
select に * は使わないほうが早くなります。
また、paramのテーブル数が多いようだったら、1つのテーブルにまとめる(要素がないものはnull値にしておく)というのも方法です。そのほうが検索は早くなります。
item_code,weight,height,volume,cal, ...など、paramA,paramBテーブルを結合したテーブルparamを作成する。
商品属性AのものはBに相当する成分をすべてNULL、逆にBならAに相当する成分をNULLにする。
というような統合テーブルを作成したうえで検索をかける方が、管理も楽になるし検索も早いと思います。
>まず、このようなテーブル設計は適当でしょうか?
同じようなテーブルがparamZまであるのなら、お世辞にも効率がいいとは言えないと思います。
数十万件程度のデータでメモリに余裕があるなら、統合することをお勧めします。
以下のテーブル構成はいかがでしょうか。
[テーブル一覧]
・商品
・商品属性
[商品テーブルのカラム]
・商品ID ※PK
・商品名
・商品種別 (0:本、1:CD、2:DVD) ※FK
・価格
・数量
・属性1
・属性2
・属性3
...
・属性30
[商品属性テーブルのカラム]
・商品種別 ※PK
・属性名1
・属性名2
・属性名3
...
・属性名30
商品名や価格のような共通属性は、商品テーブルで管理します。
各商品の任意(固有)な属性は、属性の定義(意味)を商品属性テーブルで管理します。
例えば、商品種別=0:本 の場合は、
・商品種別 = 0
・属性名1 = ISBN
・属性名2 = 著者
・属性名3 = 書籍タイトル
のような形です。
また、商品種別=1:CD であれば、
・商品種別 = 1
・属性名1 = 曲名
・属性名2 = 作曲者
・属性名3 = 作詞者
のような形です。
商品テーブルには、商品種別毎の値のみ格納します。
属性1~30の定義(意味)は、商品属性テーブルで管理します。
※値は商品テーブルの属性カラム、意味は商品属性テーブルの属性名カラムです。
商品名の検索は、商品テーブルを使って検索します。
商品毎の任意属性の検索は、商品属性テーブルを参照し、
検索する属性カラムを特定して検索します。
ISBNであれば、属性1 = "hoge" AND 商品種別 = 0
作曲者であれば、属性2 = "hoge" AND 商品種別 = 1
のような形です。
画面表示する際は、
商品種別を元にカラム名を商品属性テーブルから取得する形になります。
また、商品属性テーブルのデータは、システム起動時に一度だけ読んで、
メモリに保持っておいても良いと思います。
以上、宜しくお願いします。
ぼくなら、こんなふうに作るな。
■Items テーブル
item_code | item_name | item_price | attr_group |
---|---|---|---|
001 | 麦茶 | 150 | A |
002 | iPhone | 30000 | B |
■Groups テーブル
attr_group | attr_code |
---|---|
A | volume |
A | cal |
B | weight |
B | height |
■Attrs テーブル
item_code | attr_code | value |
---|---|---|
001 | volume | 2000 |
001 | cal | 50 |
002 | weight | 150 |
002 | height | 100 |
で、例えば、名前 = 'iPhone' で属性を探すのはこんな感じ。
select Items.item_name, Items.item_price, Attrs.attr_code, Attrs.value from Items, Attrs, Groups where Items.item_name = 'iPhone' and Items.attr_group = Groups.attr_group and Attrs.item_code = Items.item_code and Attrs.attr_code = Groups.attr_code
結果は、こう。
item_name | item_price | attr_code | value |
---|---|---|---|
iPhone | 30000 | weight | 150 |
iPhone | 30000 | height | 100 |
欲しい形とちょっと違うと思うけど、整形はアプリケーションで頑張る(というほどではない)。
属性から探す場合。
例えば、高さ < 200 で名前を探すなら、こんな感じ。
select item.item_name, item.item_price, attr.attr_code, attr.value from item, attr where attr.attr_code = 'height' and attr.value < 200 and item.item_code = attr.item_code
本当なら、コードは数値にしておきたいところ。幾分、速くなることが期待できる。
■Items テーブル
item_code | item_name | item_price | attr_group |
---|---|---|---|
1 | 麦茶 | 150 | 1001 |
2 | iPhone | 30000 | 1002 |
■GroupDef テーブル
attr_group | group_name |
---|---|
1001 | A |
1002 | B |
■Groups テーブル
attr_group | attr_code |
---|---|
1001 | volume |
1001 | cal |
1002 | weight |
1002 | height |
■AttrDef テーブル
attr_code | attr_name | attr_unit |
---|---|---|
2001 | volume | ml |
2002 | cal | cal |
2003 | weight | g |
2004 | height | cm |
■Attrs テーブル
item_code | attr_code | value |
---|---|---|
1 | 2001 | 2000 |
1 | 2002 | 50 |
2 | 2003 | 150 |
2 | 2004 | 100 |
SQL は、こうなる。
select Items.item_name, Items.item_price, AttrDef.attr_name, Attrs.value from Items, Attrs, Groups, AttrDef where Items.item_name = 'iPhone' and Items.attr_group = Groups.attr_group and Attrs.item_code = Items.item_code and Attrs.attr_code = Groups.attr_code and Attrs.attr_code = AttrDef.attr_code
属性の名称は SQL で引き当てなくても、アプリケーションで AttrDef テーブルを抱えておいて、そちらで引き当てるというのでも良い。
で、パラメーターテーブルを検索して どうするんでしょうか?
2013/06/04 06:47:50パラメーターテーブルによって取得できる内容は 違ってきます。
ということは、パラメーターテーブルによって 処理する内容も 変わってくるはずだと思いますが・・・。
また パラメータテーブルって 1レコードだけなんでしょうか?
全部 UNIONするんでしたら、ひとつのテーブルにまとめてしまったほうが よほど効率的かと思われます。
2013/06/04 09:57:02