PostgreSQLで便利な配列型を使ってテーブル構築 | その1 配列型とは?
最近PostgreSQLを使ったシステム構築をしているのですが、配列型がとてつもなく便利です。
「配列型って何？」という方（私も前までそうでした・・・）にも、オススメしたい配列型の話をちょこちょこと書きたいと思います。
1.配列型とは
配列型とは、PHPではおなじみですし他の言語でも大概ある変数型の1つです。
大きな特徴はひとつの変数内に複数の情報を格納できる事。
<?php
$_arr_test = array('1','2','3');
$_arr_test2 = array(
'11' => 'Jack',
'12' => 'Queen',
'13' => 'King'
);
?>
この配列型をPostgreSQLでも定義可能です。
定義する際は、CREATE TABLE時に以下のような型でカラムを追加すればOK。
CREATE TABLE ARR_TEST ( id integer NOT NULL, arr_test integer[], arr_test2 character varing(10)[] )
2.配列型の特徴
先ほども書きましたが、配列型の特徴はひとつのカラムに複数の情報が含められるという事です。
これは、データーベースの基本である「正規化」をする際に、あえて「非正規化」をするという事で成り立つ技です。
例えば、あるユーザー情報を管理するシステムがあるとします。こんなテーブル。
CREATE TABLE USER ( id integer NOT NULL, --ID lastmod timestamp NOT NULL, --最終更新日 password character varing(20) NOT NULL, --パスワード CONSTRAINT pk_user_id PRIMARY KEY (id) ); CREATE TABLE USER_REL ( rel_id integer NOT NULL, user_id1 integer NOT NULL, user_id2 integer NOT NULL );
このシステムはユーザーごとにパスワードと最終更新日を保持するUSERテーブルと、ユーザー同士が友達の場合にその友達情報を記憶するUSER_RELテーブルが存在します。
ユーザーが、友達申請をすると、USER_RELのuser_id1に自分のID、user_id2に友達のIDが記録されるような感じです。
このIDはユニークなので、こういう風な情報を持つことで友達一覧等を出す事が可能になります。
しかし、このテーブル構造には後々問題になりそうなところが1つ。
- 1対1のユーザー関係しか記録できないので、グループ機能などは作れない（user_id1とuser_id2しかないので）
- 友達が一杯登録されるとUSER_RELテーブルが爆発的に増える（JOIN,SELECT時にやたら重くなる）
このような問題を抱えてしまっていると、後々の機能拡張時にDBの再設計が入ってきます。（例えばテーブル分割など）
また、例えば以下のようなSQLをユーザー数が増えて使うと重くなります・・・。
--IDが100のお友達リストを出力するSQL SELECT user_id2 FROM USER_REL WHERE user_id1 = '100';
これを回避するためにも、「配列型」を用いる事で、もっと柔軟なシステム構成にしてしまいましょう！
上記のテーブルを以下のように作り変えます。
CREATE TABLE USER ( id integer NOT NULL, --ID lastmod timestamp NOT NULL, --最終更新日 password character varing(20) NOT NULL, --パスワード frend_id_arr integer[] NOT NULL, CONSTRAINT pk_user_id PRIMARY KEY (id) ); CREATE TABLE USER_REL ( rel_id integer NOT NULL, user_id_arr integer[] NOT NULL, user_id_type integer NOT NULL );
USERテーブルとRELテーブルに双方配列型を作りました。
USERテーブルのほうにある配列型は各ユーザーが登録した友達のユーザーIDリストを入れます。
USER_RELのほうにある配列型は友達関係にあるユーザーIDを入れます。また、typeはその関係性を種別するためのテーブルです。
こう変えることで、自分の友達IDリストを取得するのは以下のようなSQLでOKです。
--IDが100の友達IDを取得 SELECT frend_id_arr FROM user WHERE id = '100';
また、USER_RELテーブルは複数のIDを受け付けられるようになったので、複数人のつながりを記録する事が出来るようになりました！
もし、配列型を使わないと、このつながりの人数を制限して、カラムをその人数分作って・・・という作業が必要になりますが配列型を使えば「未定の数」に対応が可能です！
とりあえず、簡単に配列型に関する説明のさわりを書きましたが、まだまだ配列型の魅力はたくさんあります。
次回、配列の問い合わせ方法や配列が早くできる理由をアップ予定。
