個人開発物のデータベース構造の変化とそうした理由

なんの記事?

LTで登壇した内容をqiitaに書き起こしました｡
私は､メモやブックマークにタグを付けて登録するwebツールアプリを作って運用(?)しています｡
そのwebツールアプリのデータベースの構造をどうしてその仕様にしたのか､どう変化したのか､なぜ変化させたのかを書きます｡

当時の大まかな流れ

1. 今現在の構造を見てもらう
2. 一通り動きを見てもらう(どういう風にデータベースを使うのかイメージしてもらうため)
3. 最初期の構造を見てもらう
4. 今と最初期の構造を見比べての質疑応答など

前置き

メモとブックマークのデータベースが別々で存在するが､ほぼほぼ同じ仕様､同じ変化をしたので､今回は､ブックマークのデータベースの説明は省略する｡

他説明に関係なさそうな部分は省略する｡

各データベースの説明など

データベース名	目的
users	ユーザーの情報を記録
book_marks	ブックマークに関する情報を記録
tags	タグに関する情報を記録
book_mark_tags	ブックマークに紐づけられたタグを記録

環境

道具などの名前	バージョン
laravel	9
php	8
MariaDB	10

今現在のデータベース構造

users

名前	何を保存するか
id	ユーザーのid
name	ユーザー名
email	ユーザーのメアド
email_verified_at	ぶっちゃけわからない
password	パスワード
remember_token	おそらくしばらくログインした状態を保つ時に使う
ceated_at	作成日
updated_at	更新日

laravel breezeで自動生成されたテーブルをそのまま使っている

book_marks

名前	何を保存するか	補足などのその他コメント
id	ブックマークのid
user_id	ブックマークを保存したユーザーのid	usersテーブルのidと外部結合して､カスケード削除も付けた インデックスを貼った
count	ブックマークを見た回数
title	ブックマークのタイトル
url	ブックマークのurl
deleted_at	論理削除した日付
created_at	作成日
updated_at	更新日

tags

名前	何を保存するか	補足などのその他コメント
id	タグのid
user_id	タグを作ったユーザーのid	usersテーブルのidと外部結合して､カスケード削除も付けた インデックスを貼った
name	タグ名
count	タグを使った回数
deleted_at	論理削除した日付
created_at	作成日
updated_at	更新日

book_mark_tags

名前	何を保存するか	補足などのその他コメント
book_mark_id	ブックマークのid	book_marksテーブルのidと外部結合して､カスケード削除も付けた
tag_id	タグのid	tagsテーブルのidと外部結合して､カスケード削除も付けた
created_at	作成日
updated_at	更新日

どんな感じに動くのか

前置きとして以下のようなデータがすでにデータベースにあるとする

tags (説明に不要な部分は省略)

id	user_id	name	count	deleted_at
1	1	php	0	null
2	1	laravel	0	null
3	1	データベース	0	null

保存されるデータ

ユーザーid:1のユーザーが以下のようなデータを保存する場合

タイトル:エンジニアになれないへっぽこな私が実装したタグをつける機能とタグの検索機能(n番煎じ)
url     :https://qiita.com/hideya670/items/268a9643488e4036706a
紐づけたタグ:[php,laravel,データベース]

データベースは以下のように変化する

book_marks

id	user_id	count	title	url	deleted_at	created_at	updated_at
1	1	0	エンジニアになれないへっぽこな私が実装したタグをつける機能とタグの検索機能(n番煎じ)	https://qiita.com/hideya670/items/268a9643488e4036706a	null	2023-03-07 11:11:11	2023-03-07 11:11:11

book_mark_tags

book_mark_id	tag_id	created_at	updated_at
1	1	2023-03-07 11:11:11	2023-03-07 11:11:11
1	2	2023-03-07 11:11:11	2023-03-07 11:11:11
1	3	2023-03-07 11:11:11	2023-03-07 11:11:11

最初期のデータベース構造

一部テーブルは今と形が大きくことなりました｡

users

名前
id
name
email
email_verified_at
password
remember_token
ceated_at
updated_at

特にいまと変化無し

book_marks

名前	補足などのその他コメント
id
user_id	usersテーブルのidと外部結合している インデックスを貼った
title
body
deleted_at
created_at
updated_at

カスケードは付けてなかった

tags

名前	補足などのその他コメント
id
user_id	usersテーブルのidと外部結合している インデックスを貼った
name
deleted_at
created_at
updated_at

カスケードは付けてなかった

book_mark_tags

名前	補足などのその他コメント
id
book_mark_id
tag_id
deleted_at
created_at
updated_at

質疑応答

book_marks,tagsのuser_idにインデックスを貼った理由は

• 検索速度を早くしたいから｡
• user_idは検索やデータの絞り込みなどでwhere句などに書くことが多いから｡

ブックマークにタグを紐づけるのに中間テーブルを使ったのはどうして?

調べたところ中間テーブルを使う方法がよくある設計のようなので真似した

論理削除を採用した理由は?

最初期での考え

どこかで論理削除の方がデータベースへの負担が軽いと聞いたから
-> 後でわかったことだがそれを裏付ける証拠は何一つとしてなかった｡

今の考え

• バグでデータを物理削除してしまわないように｡
• 論理削除ならバグで削除処理を実行しても復旧ができるから｡
  例:送信先のurlなどがまちがえたなど
  今現在は半年ほどの猶予期間を設けている

book_mark_tagsに主キー,論理削除を採用してた理由

• ぶっちゃけ特になし｡
• 未熟ゆえ取り敢えず他のテーブルと同じ形で良いかなと考えていた

book_mark_tagsに主キーを削除した理由

• 主キーを使って検索する必要がないから｡
• 一意のデータを探す必要がないから｡

book_mark_tagsを物理削除に変更した理由

• バグの原因になったから
• とある機能をつける時に邪魔だから
• そもそも論理削除したデータを復活させる予定がないから
• もしもバグでデータが消えたとしても､あまり支障が少ないから､取り返しがつくから

カスケードを追加した理由は?

• 初期段階ではカスケードのことを知らなかった｡
• 以前はsql文とシェルスクリプトとcronをつかって論理削除されたブックマークやタグをまとめて削除していたが､もっと楽な方法はないかと探していたら見つけて以前の方法より楽だと考えたから

book_marks,tagsにcountを追加した理由は?

• 閲覧数などを集計するため
  • 閲覧数を使ってあまり見てないメモや,ブックマークに,ユーザーが､｢見てないから消そう｣､｢見てないから見よう｣などの行動を起こすきっかけにしてほしいため
• タグもほぼ同じ理由
• 計算する方法もあるがそれでは､検索するたびに計算する必要があり､それではサーバーへ負荷がかかる､それに伴い時間がかかってしまうから

book_markの場合は実装した日から数えることにした
既存のタグは以下のようなマイグレーションを使って数えてデータベースにデータを保存した

        Schema::table('tags', function (Blueprint $table) {
            // 個数行を追加
            $table->unsignedBigInteger('count')->default(0)->after('user_id');
        });

        // 今までのタグの個数を集計する

        // article,bookmarkで使われているタグのid
        $artilceTags = DB::table('article_tags')
        ->select('tag_id')
        ->whereNotNull('tag_id');

        $bookMarkTags = DB::table('book_mark_tags')
        ->select('tag_id')
        ->whereNotNull('tag_id');

        // 合体
        $unioned = $bookMarkTags->unionAll($artilceTags)->toSql();

        // タグの数を数える
        $counted = DB::table(DB::raw('('.$unioned.') AS unioned'))
        ->select('unioned.tag_id',DB::raw('count(*) as count'))
        ->groupBy('unioned.tag_id');

        $tags = $counted->get();

        // データ移植
        foreach($tags as $tag){
            DB::table('tags')
            ->where('id','=',$tag->tag_id)
            ->update(['count' => $tag->count]);
        }

今後の予定

book_mark_tagsのcreate_atとupdate_atは両者とも使う用事がないので消す

book_mark_tagsにuser_idを追加､インデックスも追加
大量のデータのからデータをさがすのに一度user_idで絞るとデータベースが探しやすいのではと予想｡

他

発表後
誤削除したらログから復旧させると良いよ
データベースはできるだけ物理削除のほうが良いよ
とアドバイスを頂きました｡
今すぐに実行はできませんが､ゆくゆくにはこのアドバイス通りにログから復旧できるようにしたいです｡