misc.: RDBの設計について

■まとめ

COMMENTはSQLには乗らない情報をSQLで取得可能な形で残せるので、もっと活用すべき

JSONのようなパース可能な構文で呼び出し側にデータを渡すことができて自動化しやすい

C言語の構造体とRDBの設計は等しいという感性を持って、DB設計をプログラミングフレンドリーにすべき

RDBはメモリの永続化、IDはメモリアドレス、テーブルは構造体だとイメージするとDBをいじる時に気が楽

■前置き

お仕事でバックエンドのソースをいじったりすると、ほぼ必ず次のような設計が出てきます。

CREATE TABLE t_user (
user_id BIGINT ZEROFILL NOT NULL UNIQUE,
address BIGINT ZEROFILL NOT NULL, # address_id, 住所情報
roles BIGINT ZEROFILL NOT NULL, # role_list_id,
hoge TEXT,
huga TEXT
);
CREATE TABLE t_address (
address_id BIGINT ZEROFILL NOT NULL UNIQUE,
postal_code INT(13),
detail TEXT
);
CREATE TABLE t_role_list (
role_list_id BIGINT ZEROFILL NOT NULL UNIQUE,
role BIGINT ZEROFILL NOT NULL UNIQUE # role_id
);
CREATE TABLE t_role (
role_id BIGINT ZEROFILL NOT NULL UNIQUE,
role_name TEXT,
foo TEXT,
bar TEXT
);

まあ、いつものRDBだよね、という感じなんですが、
「もう一工夫あっても良いんじゃないの？」とか思いませんか？

■ 疑念

なんでIDに逐一テーブル名を含めてしまうの

情報的に冗長じゃん

なんでリンクしてるテーブルの情報が#のコメントに入ってんのこれ

SQLで取得できる情報にコメントの情報入ってたら機械的に処理できるんじゃないのこれ

■ こういう風にしてみた

CREATE TABLE t_user (
id BIGINT ZEROFILL NOT NULL UNIQUE,
address_id BIGINT ZEROFILL NOT NULL
COMMENT '{"relation_to":"t_address"}',
role_list_id BIGINT ZEOFILL NOT NULL
COMMENT '{"relation_to":"t_role_list"}',
hoge TEXT,
huga TEXT
);

CREATE TABLE t_address (
id BIGINT ZEROFILL NOT NULL UNIQUE,
postal_code INT(13),
detail TEXT
);

CREATE TABLE t_role_list (
id BIGINT ZEROFILL NOT NULL UNIQUE,
item_id BIGINT ZEROFILL NOT NULL UNIQUE
COMMENT '{"relation_to":"t_role"}',
PRIMARY KEY(id, item_id)
) COMMENT '{"type":"list"}';

CREATE TABLE t_role (
id BIGINT ZEROFILL NOT NULL UNIQUE,
role_name TEXT,
foo TEXT,
bar TEXT
);

■考え方

「カラムについての説明をパース可能な形でCOMMENTに記入する」
各テーブルにユニークなIDを用意し、カラム名はidに統一する

リストの場合、リストのidとリンク先のitem_idで複合キーにする

これだけです。

DBに関する情報をSQLで取得できる場所に記述してしまえば、
「1更新に対してあっちこっちで編集が必要」みたいな状況を改善できるのでは？
と考えました。

Nodeで作ってみたら別にsequelize使わなくてもそれっぽいの作れた(関連テーブル再帰してCREATEとか)ので、とりあえずここで書いてみた感じです。
例ではCOMMENTにJSONを使っていますが、ExcelフレンドリーなCSVの方がうれしいようならそっちでも大丈夫だと思います。

■ ポイント

IDはidで統一する

これで、テーブルの種類によらず機械的に処理できるようになります。
その上でさらに、次のルールを使います。

リストテーブルは、リストのIDを表す「id」とリストに入った項目を表す「item_id」で統一する

要するにテーブルは構造体、リストはリスト単位のIDと項目のID(ポインタ)を保持する構造体というような作り方をしています。
リストについては、順番を考慮して項目のアドレス・インデックス・隣接するインデックスのアドレスなんかを持つのが通常ですが、そこらへんはSELECT文でどうにかする前提で無視できます。
(細かく気になる人はJavaのList実装とか、LISPとかそういうのを見てみてください。同じitem_idが複数回出てくるのを許容したい場合はインデックス付けた方が良いように思いますし)

大昔に、
『「オブジェクト指向って構造体に関数ポインタのっけたのと何が違うの？」とおっさんに突っ込まれた』
というポストを2chかどこかで読んだのが心に引っかかっていたのですが、
RDBは昔っからある仕組みなので、そこをあえて逆行して、昔のスジにそって考えれば、少なくともC言語くらいの感じで処理できる(と思われる)!
という感じでしょうか。

で、最後に

カラムと関連しているテーブルの情報をパース可能なデータにして、COMMENTに残してSQLで取得できるようにする

というルールを乗っけることで、自動化を可能にします。
ノリは「jsDocみたいな構文規則付きコメントの仕組みあれば全部自動でできんじゃねーの？」みたいな感じです。

■ どういうことなの？

□ 全テーブルにユニークなid付けるなどのIDのルール

RDBはメモリの永続化だったんだよ!
な、なんだってー!?

というのはおいといて、
仕事で「メモリを確保して(INSERT)、利用して(UPDATE)、解放する(DELETE)感じかな」と思った時、プログラミングしてて気が楽だったのが起点です。
自分の中でDBとプログラミングが接続できた感じがあったので…

それならば！
そういう前提で設計すれば、NoSQLとか言わなくても良いんじゃないの？(RDBでも難読化前にすでに難読な不思議な実装にはならないんじゃないの？)
Mongoとか全部にIDついてんだからテーブル全部にユニークなID義務づけるのも無理じゃないでしょ？

という感じです。

□ COMMENTにJSON書くルール

単にSQL→Sequerizeのコンバータがあればバッチ回して終わる部分もあるんですが、
リストなどでリレーションで繋がってるテーブルの情報なんかはSQLに乗らないため、COMMENTでの追記が必要でした。
普通の('#'の)コメント書いてあったりすると、
「この内容そのままパース可能な構文でCOMMENTに載せたら自動化できんのに、なんで俺このソース読んでるの？」
みたいな気分になるというか…
いっそSQLの構文制定してる所でそういう構文作ってくれれば良いんですが

□ で、それやってどうなるの？

テーブルのCOMMENTをパースすれば機械的にすべてのテーブルのクラスを動的に生成可能になります。

簡単には、全テーブルの継承元となる親クラスとしてidとコメントのrelational_toを参照するようなクラスを作れば、テーブルの関連付けはできます。
手作りが嫌なら、sequelizeを導入して、sequelizeに渡すオプションをCOMMENTに書くようにすると良いと思います。
(自分はsequelizeを使ってると「俺にSQLを書かせてくれ!思い通りにいかねぇんだ!」みたいな気分になるので諸々自作しました…)

あと、デバッグのALTERが大量発生する予感がするので、運用上の仕組みづくりに工夫が必要かもしれません。

■ 問題点

実運用はしてないです ←
SQLでサクッと全テーブルの情報が取れちゃうので、設計ごと全部秘密にしたいような(ソーシャル含めて)ハッキング怖いようなシステムだと辛いかもしれないな、とは思います。
そういう場合は、COMMENTはあくまで開発時のORM生成用の補助データとして扱って、本番環境ではCOMMENT部分を削除するみたいな工夫で乗り越えられると考えてます。

あと、「このテーブルとあのテーブルとそのテーブルを内から外からJOIN JOINしてSELECTしたのをUPDATE」みたいなのを自動生成するのはこれだけだとしんどいです。
(これじゃなくてもしんどいと思うけど)

■その他

NoSQLでも良いんですが、多テーブルで共有するデータが出てくるあたりで「正規化がー」とか「JOINがー」みたいなRDBと同じような問題が同じように出てくる気がするんですよね…

あと、「このツール(A5とか)でこういう事したらパパッとORMできちゃうよー！」って知識をお持ちでしたら、ぜひQiitaなり何なりでその知見の共有お願いいたします…

misc.

2018年7月14日土曜日

RDBの設計について