動的に属性を拡張できるテーブルの設計メモ

データベースを使って、運用中に動的に属性を拡張できるような仕組みを作りたい場合の設計メモです。

拡張属性に対応する「キー」と「値」のテーブルを追加で用意して、
拡張元のテーブルと関連づける

感じで実現します。

テーブル設計

kittenテーブルの属性を拡張できるようにしたいとして、テーブルは次のような感じになります。

名前	説明
kitten	拡張元のテーブル。あらかじめ決まっている属性をカラムとして持つ。
ex_key	拡張属性のキーを格納するテーブル。
ex_value	拡張属性の値を格納するテーブル。外部参照キーとして、拡張属性IDとkittenのIDを持ち、「kitten+拡張属性+値」を関連づける。

MySQLで作る場合のSQL文は以下の通りです。

create table kitten (
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL DEFAULT '',
  PRIMARY KEY(id)
);

create table ex_key (
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL DEFAULT '',
  PRIMARY KEY(id)
);

create table ex_value (
  kitten_id BIGINT NOT NULL,
  key_id BIGINT NOT NULL,
  value VARCHAR(20) NOT NULL DEFAULT '',
  FOREIGN KEY (kitten_id) REFERENCES kitten(id) ON DELETE CASCADE,
  FOREIGN KEY (key_id) REFERENCES ex_key(id) ON DELETE CASCADE,
  PRIMARY KEY(kitten_id,key_id)
);

拡張属性の取り出し

指定された拡張属性をkittenの属性と共に取り出すには、
JOINするか、

select 
  id, 
  name,
  ex1.value as ex1_value,
  ex2.value as ex2_value
from kitten 
  left join ex_value as ex1 on ex1.kitten_id = kitten.id and ex1.key_id = <1つめの拡張属性キーのID>
  left join ex_value as ex2 on ex2.kitten_id = kitten.id and ex2.key_id = <2つめの拡張属性キーのID>;

サブクエリを使ってもできます。

select 
  id, 
  name,
  (select value from ex_value where kitten_id=id and key_id=<1つめの拡張属性キーのID> ) as ex1_value,
  (select value from ex_value where kitten_id=id and key_id=<2つめの拡張属性キーのID> ) as ex2_value
from kitten;

データベースに次のようなデータが入っている場合、

mysql> select * from kitten;
+----+-------+
| id | name  |
+----+-------+
|  1 | mii   |
|  2 | shiro |
|  3 | tora  |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from ex_key;
+----+------+
| id | name |
+----+------+
|  1 | type |
|  2 | age  |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from ex_value;
+-----------+--------+---------+
| kitten_id | key_id | value   |
+-----------+--------+---------+
|         1 |      1 | mike    |
|         2 |      1 | persian |
|         1 |      2 | 1       |
|         2 |      2 | 2       |
|         3 |      2 | 1       |
+-----------+--------+---------+
5 rows in set (0.00 sec)

実行結果は以下のようになります。

mysql> select 
    ->   id, 
    ->   name,
    ->   ex1.value as ex1_value,
    ->   ex2.value as ex2_value
    -> from kitten 
    ->   left join ex_value as ex1 on ex1.kitten_id = kitten.id and ex1.key_id = 1
    ->   left join ex_value as ex2 on ex2.kitten_id = kitten.id and ex2.key_id = 2;
+----+-------+-----------+-----------+
| id | name  | ex1_value | ex2_value |
+----+-------+-----------+-----------+
|  1 | mii   | mike      | 1         |
|  2 | shiro | persian   | 2         |
|  3 | tora  | NULL      | 1         |
+----+-------+-----------+-----------+
3 rows in set (0.04 sec)

mysql> select 
    ->   id, 
    ->   name,
    ->   (select value from ex_value where kitten_id=id and key_id=1 ) as ex1_value,
    ->   (select value from ex_value where kitten_id=id and key_id=2 ) as ex2_value
    -> from kitten;
+----+-------+-----------+-----------+
| id | name  | ex1_value | ex2_value |
+----+-------+-----------+-----------+
|  1 | mii   | mike      | 1         |
|  2 | shiro | persian   | 2         |
|  3 | tora  | NULL      | 1         |
+----+-------+-----------+-----------+
3 rows in set (0.00 sec)

拡張属性での検索とソート

↑で取り出したデータに対して、検索とソートを行なえばOK。

select * from (
  select 
    id, 
    name,
    ex1.value as ex1_value,
    ex2.value as ex2_value
  from kitten 
    left join ex_value as ex1 on ex1.kitten_id = kitten.id and ex1.key_id = <1つめの拡張属性キーのID>
    left join ex_value as ex2 on ex2.kitten_id = kitten.id and ex2.key_id = <2つめの拡張属性キーのID>
) as X where ex1_value is not null order by ex2_value;

実行結果です。

mysql> select * from (
    ->   select 
    ->     id, 
    ->     name,
    ->     ex1.value as ex1_value,
    ->     ex2.value as ex2_value
    ->   from kitten 
    ->     left join ex_value as ex1 on ex1.kitten_id = kitten.id and ex1.key_id = 1
    ->     left join ex_value as ex2 on ex2.kitten_id = kitten.id and ex2.key_id = 2
    -> ) as X where ex1_value is not null order by ex2_value;
+----+-------+-----------+-----------+
| id | name  | ex1_value | ex2_value |
+----+-------+-----------+-----------+
|  1 | mii   | mike      | 1         |
|  2 | shiro | persian   | 2         |
+----+-------+-----------+-----------+
2 rows in set (0.00 sec)