ほぼタイトルの通りですが、メインテーブルのマスターレコードに対し、複数の属性レコードで項目が可変長の属性テーブルがあり、これを、横持ちのデータとして取り扱う必要がありました。最初はPivotを使おうかと思いましたが、項目が可変長なので、うまくいきません。そこで、属性種別ごとにviewを用いてマスターレコード単位でに横持ちにし、さらにマスターレコードidを頼りに、複数のviewをinnerjoinで複数結合させたところ、ほぼ目的通りに動作したので、備忘録代わりに挙げてみることにしました。
属性テーブルは一例として以下の様になっています。
もっとも横持ちの様に扱えるかどうかが問題だったのですが、上述の様にすることで、おおよその仕様要求はみたせたので、よしとしています。 今回は以上です。それでは。
属性テーブルは一例として以下の様になっています。
select * from art_prop where art_id=1; +-------------+------------------+--------------+---------------+------------------+----------------+-----------+-----------+--------+ | art_prop_id | prop_cat | prop_name | prop_dsc1 | prop_dsc2 | prop_dsc3 | prop_dsc4 | prop_dsc5 | art_id | +-------------+------------------+--------------+---------------+------------------+----------------+-----------+-----------+--------+ | 1 | Basic spec | vendor | ASRock | | | | | 1 | | 2 | Basic spec | form factor | Micro ATX | | | | | 1 | | 3 | Basic spec | cpu socket | Socket AM4 | | | | | 1 | | 4 | Basic spec | chip set | AMD | A520 | | | | 1 | | 5 | Basic spec | memory type | DIMM | DDR4 | | | | 1 | | 6 | Basic spec | memory slots | | | | | 2 | 1 | | 7 | Basic spec | memory max | 64 | GB | | | | 1 | | 8 | Basic spec | dimension | 230mm x 201mm | | | | | 1 | | 9 | Expansion Slot | pcie | 3.0 | x16 | | | 1 | 1 | | 10 | Expansion Slot | pcie | 3.0 | x1 | | | 1 | 1 | | 11 | Serial bus | USB | 2.0 | back panel | Type A | | 2 | 1 | | 12 | Serial bus | USB | 2.0 | header | | | 2 | 1 | | 13 | Serial bus | USB | 3.2 Gen1 | back panel | Type A | | 4 | 1 | | 14 | Serial bus | USB | 3.2 Gen1 | header | | | 1 | 1 | | 15 | On board graphic | D-Sub | analog | 1920x1200@60Hz | | | 1 | 1 | | 16 | On board graphic | HDMI | HDMI 2.1 | 4096x2160@60Hz | | | 1 | 1 | | 17 | On board graphic | DVI-D | digital | 1920x1200@60Hz | | | 1 | 1 | | 18 | On board audio | audio chip | ALC887 | | | | 1 | 1 | | 19 | Storage | sata | sata3 | | | | 4 | 1 | | 20 | Storage | M.2 | M Key | sata3/pcie3.0 x4 | 2242/2260/2280 | | 1 | 1 | | 21 | Network | wired lan | 1000BASE-T | RTL8111H | | | 1 | 1 | | 22 | Security device | TPM | header | | | | 1 | 1 | | 23 | Security device | TPM | fTPM | | | | 1 | 1 | | 24 | Serial Port | PS/2 | back panel | | | | 1 | 1 | | 25 | Serial Port | COM | header | | | | 1 | 1 | +-------------+------------------+--------------+---------------+------------------+----------------+-----------+-----------+--------+フィールド名でいうとart_idがメインテーブルのレコードidです。可変項目にしたのは、属性がいろいろとかわり、横持ちだとメンテが面倒だとおもったからですが、DB設計が悪いといわれればそれまでなんですが...基、さて、これをたとえば以下の様にしたいとします。
+-----------------+----------------+----------+--------+------------+-------------+-------------+-------------+ | name | chipset vendor | chipset | vendor | cpu socket | form factor | memory form | memory type | +-----------------+----------------+----------+--------+------------+-------------+-------------+-------------+ | A520M-HDV | AMD | A520 | ASRock | Socket AM4 | Micro ATX | DIMM | DDR4 | | A320M-HDV R4.0 | AMD | A320 | ASRock | Socket AM4 | Micro ATX | DIMM | DDR4 | | A520M Pro4 | AMD | A520 | ASRock | Socket AM4 | Micro ATX | DIMM | DDR4 | | B450M-HDV R4.0 | AMD | B450 | ASRock | Socket AM4 | Micro ATX | DIMM | DDR4 | | H310CM-HDV/M.2 | Intel | H310 | ASRock | LGA1151 | Micro ATX | DIMM | DDR4 | | PRO Z690-A WIFI | Intel | Z690 | MSI | LGA1700 | ATX | DIMM | DDR5 | +-----------------+----------------+----------+--------+------------+-------------+-------------+-------------+答えからいうと、この表は以下のようなSQLで作成しました。
SELECT v1.art_name AS "name", v1.`chipset_vendor` AS "chipset vendor", v1.`chipset` AS "chipset", v2.`vendor` AS "vendor", v3.`cpu_socket` AS "cpu socket", v4.`form_factor` AS "form factor", v5.`memory_form` AS "memory form", v5.`memory_type` AS "memory type" FROM v1 INNER JOIN v2 ON (v1.art_id = v2.art_id) INNER JOIN v3 ON (v1.art_id = v3.art_id) INNER JOIN v4 ON (v1.art_id = v4.art_id) INNER JOIN v5 ON (v1.art_id = v5.art_id)vXが、viewでそれぞれの属性ごとに作成したもですが、それぞれのviewは以下の様に作成しました。
drop view if exists v1; create view v1 as SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as chipset_vendor, `art_prop`.`prop_dsc2` as chipset FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) WHERE prop_name="chipset"; drop view if exists v2; create view v2 as SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as vendor FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) WHERE prop_name="vendor"; drop view if exists v3; create view v3 as SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as cpu_socket FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) WHERE prop_name="cpu socket"; drop view if exists v4; create view v4 as SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as form_factor FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) WHERE prop_name="form factor"; drop view if exists v5; create view v5 as SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as memory_form, `art_prop`.`prop_dsc2` as memory_type FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) WHERE prop_name="memory type"; drop view if exists v6; create view v6 as SELECT `art`.`art_id`, `art`.`art_name`, `art_prop`.`prop_dsc1` as max, `art_prop`.`prop_dsc2` as unit FROM `art` INNER JOIN `art_prop` ON (`art_prop`.`art_id` = `art`.`art_id`) WHERE prop_name="memory max";みていただければわかると思いますが、今回はマザーボードのデータベースを作成しています。なぜこのような縦持ちのへんてこな属性レコードにしたのかは聞かないでください(笑) 基、理由はたとえば、USB4やThunder Bolt4、DDR6などがこれから出てくるし、これから先どんな規格のものが出てくるかわからないためです。また、拡張スロットにしても、PCIEだけでなく、マザーボードによってはPCIバスがあるものや、M.KeyではないM.2があり、さらにUSB3.2にしてもGen1なのかGen2なのか、TYPE AなのかTYPE Cなのか、また、StorageのNVMe SSDにしても属性がバラバラなため、横持ちは無理だと思ったからでもあります。もちろんマスターレコードの数が比較的にすくないというのもありますが。。。
もっとも横持ちの様に扱えるかどうかが問題だったのですが、上述の様にすることで、おおよその仕様要求はみたせたので、よしとしています。 今回は以上です。それでは。
コメント
コメントを投稿