データベースのテーブルを設計する-SpringBoot入門②

SpringBoot(Todo)：サムネイル画像２ Todoアプリ(SpringBoot)
2025.01.31

前回で環境構築が完了したので、今回はTodoアプリに必要な SQL テーブルの設計をします。

アプリ構想

把握しておいた方がテーブル設計時に分かりやすいので先に考えます。

今回は、３つのページで構成されるアプリケーションにしていきます。

  1. ログインページ
  2. マイページ
  3. 管理者ページ

ログインページ

初期ページです。

メールアドレスとパスワードを用いたログイン機能を実装し、マイページへ遷移できるようにします。

マイページ

ログイン後に遷移するページです。

ここでユーザーが Todo を操作できる機能を実装します。

管理者ページ

管理者しかアクセスできないページです。

任意のユーザー情報やTodoリストを確認できるように実装します。

テーブル設計

設計図を示します。

テーブル名の左側にある数字はテーブルをロードする順番を示していますが、次回のSQLファイル作成で説明するので一旦無視していいです。

2_userPK`user_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`authority_id` int unsigned NOT NULL `name` varchar(16) NOT NULLUK`email` varchar(32) NOT NULL`password` varchar(32) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP3_todoPK`todo_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`user_id` bigint unsigned NOT NULLUK`title` varchar(32) NOT NULL`is_check` tinyint(1) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP1_authorityPK`authority_id` int unsigned NOT NULL AUTO_INCREMENTUK`name` varchar(16) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP1_cateoryPK`category_id` int unsigned NOT NULL AUTO_INCREMENTUK`name` varchar(16) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP4_todo_categoryPK`todo_category_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`todo_id` bigint unsigned NOT NULLFK`category_id` int unsigned NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

テーブル一覧

５つのテーブルを用意します。

  1. user
  2. authority
  3. todo
  4. category
  5. todo_category

共通カラムとして、データの作成日と更新日を示す、

  • created_at
  • updated_at

を定義しており、データが作成(更新)されたら即座に作成(更新)時刻が記録されるタイムスタンプ形式にしています。

また、それぞれの主キー(PK)には自動連番機能を設定し、DB側で勝手にID番号を付与するようにします。

ただ、直感的に数えやすくするために、ID には unsigned を付与して正の値となるようにしています。

user

Todoアプリを利用しているユーザーの下記情報を管理します。

  • ユーザー名
  • メールアドレス
  • パスワード
  • アクセス権限ID

メールアドレスとパスワードは、ユーザーのログイン処理を実装する際に使用するつもりです。

ユーザーIDとユーザー名はマイページに表示したりします。

他の項目も無い方が珍しいと思うので、違和感はあまりないと思います。

ただ、このテーブルだけだとアクセス権限はIDしか取得できないため、保持している authority_id に対応するデータを authority テーブルから参照できるようにしています。

authority

アクセス権限を管理します。

管理者サイトや会員限定サイトを実装する場合、この情報があるとフィルタリングできるため便利です。

name カラムには、「管理者」「会員」「ゲスト」など、それっぽいのを登録しようと思いますが、名前の重複があると紛らわしいのでユニーク制約(UK)を適用します。

ユニーク制約を適用したカラムは、同じ値を複数登録することができません。

user テーブルに権限を直接設定しない理由

SQLに慣れてない場合、user テーブルに直接「管理者」などの権限名を設定すればいいのではと考えてしまうと思います。

(自分もそうだったので…)

この方法だと、

「authority テーブルを作成して user テーブルから参照する」

という面倒な工程が必要ないので楽にみえます。

ただ、あるとき「管理者」から「マネージャー」に表記を変更したくなったとしましょう。

その場合、user テーブルで「管理者」と設定されている全データを「マネージャー」に変更する必要があり、途方もない時間を要することになります。

一方、事前に authority テーブルに分割してしまえば、一つのデータを変更するだけで済みます。

これが、わざわざ複数テーブルに分割している理由です。

todo

下記の Todo 情報を管理します。

  • やること内容
  • 完了したか示すチェック

どのユーザーが書いた Todo なのか識別する必要があるため、 user_id カラムを設定し、外部キー制約(FK)を適用することで、user テーブルの user_id カラムを参照可能としています。

外部キー制約を適用したカラムは、参照先のテーブルに存在する値しか登録できなくなります。

今回の場合、todo テーブルの user_id カラムは、 user テーブルの user_id カラムに存在する値しか登録できません。

このような仕様なので、操作ミスによってユーザーが存在しない Todo を生成してしまった等の問題を防ぐことができます。

また、同じ内容が複数あると紛らわしいので、やること内容を示す title カラムにはユニーク制約(UK)を適用します。

ただ、単純に title カラムだけに付与すると、他のユーザーが同じ Todo を既に書いていた場合も重複エラーとなります。

つまり、他のユーザーと同様の Todo だと一切登録できなくなってしまうため、同一ユーザーが同一の Todo を複数個登録できない仕様にするために、(user_id, title)でユニーク制約を適用します。

Todo を完了したか示す is_check カラムは真偽値(True,False)を設定します。

MySQL では、真偽値のカラムは tinyint(1) と定義します。

category

各 Todo に設定可能なカテゴリーを管理します。

カテゴリー名を示す name カラムにはユニークキー(UK)を適用しています。

今回は一つの Todo に対して複数のカテゴリーを付与できる仕様にするため、todo テーブルから直接 category テーブルを参照せず、中間テーブルとして todo_category テーブルを作成していますが、ここら辺は ER 関係の説明で補足します。

todo_category

Todo とカテゴリーのペアを管理する中間テーブルです。

todo_id と category_id に外部キー制約(FK)を適用しています。

このテーブルを参照することで、特定のTodoに付与されているカテゴリーは何かが分かるようになります。

ERについて

次は各テーブル間の関係を説明します。

2_userPK`user_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`authority_id` int unsigned NOT NULL `name` varchar(16) NOT NULLUK`email` varchar(32) NOT NULL`password` varchar(32) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP3_todoPK`todo_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`user_id` bigint unsigned NOT NULLUK`title` varchar(32) NOT NULL`is_check` tinyint(1) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP1_authorityPK`authority_id` int unsigned NOT NULL AUTO_INCREMENTUK`name` varchar(16) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP1_cateoryPK`category_id` int unsigned NOT NULL AUTO_INCREMENTUK`name` varchar(16) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP4_todo_categoryPK`todo_category_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`todo_id` bigint unsigned NOT NULLFK`category_id` int unsigned NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

(user,authority)の関係

user 視点だと、権限(authority)は1つだけ保持しているので、user→authority の関係は１です。

authority 視点だと、どの権限も０人以上のユーザーが所持しているので、authority→user の関係は０～多です。

従って、user(０～多):authority(１)の関係になります。

(todo,user)の関係

todo 視点だと、該当の todo_id(PK) を保持する Todo を登録したユーザーは一人だけなので、todo→user の関係は１です。

user 視点だと、該当の user_id(PK) で登録した Todo は０個以上存在するので、user→todo の関係は０～多です

従って、todo(０～多):user(１)の関係になります。

(todo,todo_category)と(category,todo_category)の関係

中間テーブルがあるので少しややこしいです。

そもそも、本来なら線を繋げるべきテーブルは(todo,category)です。

そのため、todo テーブルに category_id を追加し、両テーブルを直接繋いだ以下の関係の方が一見よさそうに思えます。

3_todoPK`todo_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`user_id` bigint unsigned NOT NULLFK`category_id` bigint unsigned NOT NULLUK`title` varchar(32) NOT NULL`is_check` tinyint(1) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP1_cateoryPK`category_id` int unsigned NOT NULL AUTO_INCREMENTUK`name` varchar(16) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

この場合、todo 視点だと、該当の 「todo_id(PK) を保持する Todo」 に登録できるカテゴリーは１種類以上なので、todo→category の関係は１～多です。

category 視点だと、該当の category_id(PK)が登録されている Todo は０種類以上なので、category→todo の関係は０～多です。

すなわち、多：多の関係ということです。

ここに大きな問題があるのです。

なぜなら、SQLテーブルの各カラムは、１つの値しか設定できないからです。

以下の例で考えてみます。

3_todoPK`todo_id` 1FK`user_id` 1FK`category_id` 1UK`title` varchar(32) ヘルクレスのマット交換をする。`is_check` tinyint(1) 0`created_at` datetime 2025-01-31 07:04:10`updated_at` datetime 2025-01-31 07:04:101_cateoryその１PK`category_id` 1UK`name` varchar(16) カブトムシ`created_at` datetime 2025-01-31 07:04:10`updated_at` datetime 2025-01-31 07:04:101_cateoryその２PK`category_id` 2UK`name` varchar(16) マット交換`created_at` datetime 2025-01-31 07:04:10`updated_at` datetime 2025-01-31 07:04:10
１と２を両方設定したいけど、
片方の値しか設定できない
１と２を両方設定したいけど、片方の値しか設定できないText is not SVG – cannot display

Todoの内容は「ヘルクレスオオカブトのマット交換をする」とします。

カテゴリーは「カブトムシ」と「マット交換」の2種類が存在すると仮定します。

ここから、Todo に設定すべきカテゴリーを考えます。

まず、クワガタとかカナブンじゃなく、カブトムシであることが分かるように、Todo テーブルのcategory_id には「カブトムシ」を示す１を設定します。

次に、何の操作をしたのか判別できるように、「マット交換」というカテゴリーも付与したいところです。

なので、「マット交換」を示す２も category_id に設定しようとします。

ですが、SQL テーブルの各カラムには一つの値しか設定できないので、１と２の両方を同時に設定することは不可能です。

SQLにはリストで格納するという概念も存在しません。

この問題を解決するために、中間テーブルの todo_category を使用します。

3_todoPK`todo_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`user_id` bigint unsigned NOT NULLUK`title` varchar(32) NOT NULL`is_check` tinyint(1) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP1_cateoryPK`category_id` int unsigned NOT NULL AUTO_INCREMENTUK`name` varchar(16) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP4_todo_categoryPK`todo_category_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`todo_id` bigint unsigned NOT NULLFK`category_id` int unsigned NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

このように、多：多のテーブルの間に新たなテーブルを挟んで無理やり１：多の関係にすることで、以下のように１つの todo テーブルに対して複数のカテゴリーをマッピングすることができます。

1_cateoryその１PK`category_id` 1UK`name` varchar(16) カブトムシ`created_at` datetime 2025-01-31 07:04:10`updated_at` datetime 2025-01-31 07:04:101_cateoryその２PK`category_id` 2UK`name` varchar(16) マット交換`created_at` datetime 2025-01-31 07:04:10`updated_at` datetime 2025-01-31 07:04:104_todo_categoryその１PK`todo_category_id` 1FK`todo_id` 1FK`category_id` 1`created_at` datetime 2025-01-31 07:04:10`updated_at` datetime 2025-01-31 07:04:103_todoPK`todo_id` 1FK`user_id` 1UK`title` varchar(32) ヘルクレスのマット交換をする。`is_check` tinyint(1) 0`created_at` datetime 2025-01-31 07:04:10`updated_at` datetime 2025-01-31 07:04:104_todo_categoryその２PK`todo_category_id` 2FK`todo_id` 1FK`category_id` 2`created_at` datetime 2025-01-31 07:04:10`updated_at` datetime 2025-01-31 07:04:10

この構成であれば、対象の todo を取得する際に、 todo_id が同値のデータを todo_category 経由で参照することで、カテゴリーも同時に取得することができます。

忘れられがちですが、反対に１つの todo テーブルに対してカテゴリーを１つも付与しない場合にも対応することができます。

todo_category テーブルに登録しなければいいだけなので。

まとめ

ER図を用いて、SQL テーブル生成時の設計図を作成しました。

2_userPK`user_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`authority_id` int unsigned NOT NULL `name` varchar(16) NOT NULLUK`email` varchar(32) NOT NULL`password` varchar(32) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP3_todoPK`todo_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`user_id` bigint unsigned NOT NULLUK`title` varchar(32) NOT NULL`is_check` tinyint(1) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP1_authorityPK`authority_id` int unsigned NOT NULL AUTO_INCREMENTUK`name` varchar(16) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP1_cateoryPK`category_id` int unsigned NOT NULL AUTO_INCREMENTUK`name` varchar(16) NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP4_todo_categoryPK`todo_category_id` bigint unsigned NOT NULL AUTO_INCREMENTFK`todo_id` bigint unsigned NOT NULLFK`category_id` int unsigned NOT NULL`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

次回はこのER図のとおりとなるように MySQL 上にテーブルを生成します。

MySQLでテーブルを生成する-SpringBoot入門③
前回は SQL テーブルの構成を ER 図を用いて定義しました。今回は MySQL 上にテーブルを生成します。DB起動の準備単に SQL ファイルを直置きしても無視されるだけなので、SQL ファイルを...
or-expert.com
2025.03.03
タイトルとURLをコピーしました