要は中間テーブルのお話
mysqlにはjsonを取り扱える型式がある。
json形式だから何でも取り扱えるのがメリットだが、そのまんま裏返しにデメリットもある。
つまり「なんでも取り扱えてしまうがゆえに濫用しようと思えばいくらでもできる」という点。
2年目当時の私は、気候スイッチを暑いか寒いかの2択にしか操作できない『れいわちゃん』と同じように
柔軟な思考回路も経験則も大して持ち合わせていなかったので、
中間テーブルなんて単語が書かれた本は一切脳の書庫にはなかった。
（ ＾ω＾）「おっ、json形式とかいうやつ、カラムで配列みたいなん使えてクッソ便利やんけ！！」
そんな当時の私に怒りの鉄槌を振り下ろすのが今回。
mysqlでjson形式を使うこと自体を否定するわけではない点に留意頂ければ
json形式のカラムとは
まずは軽くおさらいだが、jsonの型を宣言したカラムにはその名の通りjson形式のデータを格納できる。
定義の時はinteger()、varchar()やtimestampがjsonになるだけ。
▼よく見るjson
{
"name": "山田太郎",
"birthday": "1989-11-15",
"place": "東京"
}
これをDBに突っ込むときはこんなイメージ。
| id | json_data | regist_date |
|---|---|---|
| 1 | {"name":"山田太郎","birthday":"1989-11-15","place":"東京"} | 2022-12-16 |
| 2 | {"name":"山田次郎","birthday":"1991-12-15","place":"東京"} | 2022-12-16 |
| 3 | {"name":"山田三郎","birthday":"1993-01-15","place":"埼玉", "gender":"男"} | 2022-12-16 |
json形式ということしかこのjson_dataには定義されていないので、めちゃくちゃ自由度は高い。
とはいえこの格納方法はさすがにひどいので再考すべしだが。
このjson形式では普通の一次元配列も格納することができる。
例えばplace_historyに、各山田さんの歴代居住地を格納してみる。
| id | name | place_history | regist_date |
|---|---|---|---|
| 1 | 山田太郎 | ['東京都内', '大宮', '日光', '板橋'] | 2022-12-16 |
| 2 | 山田次郎 | ['千代田区'] | 2022-12-16 |
| 3 | 山田三郎 | ['東京某所', '日光', '川越'] | 2022-12-16 |
これくらいならどこかで使えることもあるだろう。
なおmysqlにはjsonを取り扱うための様々な関数が用意されている。
https://dev.mysql.com/doc/refman/8.0/ja/json-function-reference.html
うまく使えばなかなか心強いが、安易に手を出すと可読性や保守性がｶﾞﾀｯと落ちる。
json形式を使用した失敗
先ほどは各文字列が違い、かつ地名という無数にあるデータが対象であり、
マスタデータに持つのが難しかったためjsonでも比較的問題ないと思われる対応方法だったが、
当時の私が用いたのは一次元配列で数値データを持つというもの。
おまけにその数値データはほかのテーブルのidに一致するという紛れもない害悪パターンだ。
当時の私の過ち
例えば、社内の部を格納しておくテーブルがある。
社員データを格納しておくテーブルも別途存在するとする。
▼所属マスタ(beload)
| id | name |
|---|---|
| 1 | 営業部 |
| 2 | 技術部 |
| 3 | 経営部 |
▼社員テーブル(employee)
| id | name |
|---|---|
| 1 | 社員花子 |
| 2 | 社員太郎 |
| 3 | 社員徹 |
この時、社員データに新たに「どの所属に属するか」を示すデータを追加したい。
しかし社員は複数の所属に属する可能性がある。つまり多対多の関係性である。
1つの所属だけでいいのならば、所属カラムを社員テーブルに追加して外部キー連携すればいい、
それくらいなら当時の私でも理解できていたが、複数となると、、、
所属２カラム、所属３カラムを追加するわけにもいくまい。所属９９９カラムが必要になったらどうするんだ。
当時の私はそれはそれは頭を悩ませたのであった。
そこで取った作戦がjsonを使うというもの。
▼社員テーブル・改
| id | name | belong | regist_date |
|---|---|---|---|
| 1 | 社員花子 | [1, 2] | 2022-12-16 |
| 2 | 社員太郎 | [1] | 2022-12-16 |
| 3 | 社員徹 | [2, 3] | 2022-12-16 |
（ ＾ω＾）「これなら社員1人あたりに複数の所属情報を持たせられるぜ」
何がいけないのか
上記の方法は、言ってみれば「とりあえず動くだけの方法」と言えると思う。
当時の私に、今の私がこの方法はダメだと鉄槌を加えるのに理由を考えるならば
・後からプロジェクトに入った人が見たとき、DB上の定義としてはこのカラムは「jsonであること」しかないので、belongに格納されている数値がなんなのかパッと見で分からない
・外部キー制約を使っていないため、マスタにないデータが投入され思わぬエラーを引き起こす可能性がある
・FWによっては外部キーを貼ることでマスタデータにアクセスできるのにそれができないのが勿体ない
とかいろいろそれらしい理由も考えられると思うが、個人的に最大の問題点は
このbelongカラムの値を取り出したときはただの文字列に過ぎないという点。
つまり[2, 3]という文字列が入手できる。いらない。
json関数を使えばある程度マシだが、mysqlのバージョンやFWに依存するのであまり好ましくない。
※SQLを直書きすれば基本いけるが、結局統一された綺麗なコードにはなりづらい点でやはりよくない。
だから当時の私の拙いソースを見返してみると、驚くことに
# dataにはjson形式の形をした文字列が保持されている
data_json = data[1:-1].split(', ')
dataの先頭と最後の文字（[と]）を排除したうえでsplitでカンマごとに区切り配列を生成する・・・なんてことを行っている。
配列にすることでdata_json[0]とかでようやくデータに容易にアクセスできるようになる。
初見の人が見たら間違いなく小1時間は「何のためにこれをやっているのか」と首をかしげる箇所である。
コードレビューの重要性よな。
json形式を使わない方法に変換する
この害悪手法をDBらしい設計に作り替えてみると、おそらく中間テーブルを作成するという手法にたどり着くと思う。
▼中間テーブル
| id | employee_id | beload_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 3 | 2 |
| 5 | 3 | 3 |
employee_idが社員ID、beload_idが所属ID。
こうすれば、例えば社員ID3の人が属している所属は何かなーと
select * from table where employee_id = 3;
と検索すれば4と5のデータが抽出され、あ、2番と3番の所属に属しているのねということがわかる。
社員テーブルにカラムも増えない、際限なく増やす必要性もない。
そしてjson関数などを使用せず、それ以外のデータの抽出方法と同様の方法になるためコードも統一され綺麗になる。
少なくとも先ほどのように「[2, 3]という文字列が抽出されるのでそれを配列に加工する」というコードを書かなければならない状況は回避できるはずだ。
とはいえ・・・
個人的には、正直中間テーブルという手法もなんかもっといい方法ないかなぁと思う次第である。
「社員には複数の所属が紐づくのよ」という多-多の関係性のテーブルが1つのアプリ内でいくつも発生するとその分だけ中間テーブルが必要になる。
ER図に起こすとリレーションがごっちゃごちゃになるのが好きじゃない（知らん