はじめに
私は1年以上稼働しているシステムを運用しているエンジニアです。
今回は私がDB設計で失敗し外部キー制約について学んだことを記事にします。
少しでも皆さんの参考になれば幸いです。
こんな人に読んで欲しい
- DB設計をしている
- DB設計を勉強中
- 外部キー制約について学びたい
- 運用中のテーブルに変更をかけたい
目次
- 皆さんにお伝えしたいこと
- 概要
- 失敗事例
- 間違った設計
- バグ調査
- 正しい設計
- 失敗から学んだこと
- まとめ
皆さんにお伝えしたいこと
まず初めに、エンジニアの皆さんにお伝えしたいことは漏れなくテストしよう！！！です。
いきなりなんだ？？と思うかもしれませんが、結局本記事で最も伝えたいことはこれなので先にお伝えしました。
それでは概要をお伝えしていきます。
概要
システム概要
登録した利用者情報を管理するシステムです。
利用者(エンドユーザー)はフォームから各情報を入力し、登録します。
管理者(クライアント)は管理画面から利用者情報を変更することができます。
- 運用期間：1年4ヶ月
- 利用者数：数万人
- 開発言語：
- フロントエンド：Vue.js
- バックエンド：Ruby on Rails
- DBMS：MySQL
当時、既存システムは以下設計で運用していました。
※紹介にあたり設計を簡略化しています。
Customerテーブル
| 項目名 | 概要 | 必須/任意 |
|---|---|---|
| name | 名前 | 必須 |
| age | 年齢 | 必須 |
| phone_number | 電話番号 | 必須 |
Customerテーブルのマイグレーションファイル
class CreateCustomers < ActiveRecord::Migration[7.0]
def change
create_table :customers do |t|
t.string :name, null: false
t.integer :age, null: false
t.string :phone_number, null: false
t.timestamps
end
end
end
create_table "customers", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
t.string "name", null: false
t.integer "age", null: false
t.string "phone_number", null: false
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
経緯
利用者情報にお気に入り店舗情報を必須項目として追加したいという要望があり、
お気に入り情報マスタテーブルを作成し、利用者テーブルと外部キーで紐づけることにしました。
失敗事例
私はお気に入り情報マスタテーブルを設計し、新規利用者については問題なくレコード登録されていることをテストで確認しました。
しかし、管理画面から既存利用者情報を更新できないというバグを発見しました。
間違った設計
バグ発見時の設計について紹介します。
どこが間違っているのか考えながら見てみてください。
Customerテーブル
| 外部キー | 項目名 | 概要 | 必須/任意 |
|---|---|---|---|
| name | 名前 | 必須 | |
| age | 年齢 | 必須 | |
| phone_number | 電話番号 | 必須 | |
| FK | favorite_store_id | お気に入り店舗ID | 必須 |
FavoriteStoreマスタテーブル
| 項目名 | 概要 | 必須/任意 |
|---|---|---|
| name | 店舗名 | 必須 |
FavoriteStoreマスタテーブルのマイグレーションファイル
class CreateFavoriteStores < ActiveRecord::Migration[7.0]
def change
create_table :favorite_stores do |t|
t.string :name, null: false
t.timestamps
end
end
end
CustomerテーブルへFavoriteStoreマスタテーブルの外部キーを設定するマイグレーションファイル
class AddReferencesToCustomer < ActiveRecord::Migration[7.0]
def change
add_column :customers, :favorite_store_id, :integer, null: false
end
end
create_table "customers", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
t.string "name", null: false
t.integer "age", null: false
t.string "phone_number", null: false
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.integer "favorite_store_id", null: false
end
create_table "favorite_stores", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
t.string "name", null: false
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
CustomerのModelファイル
class Customer < ApplicationRecord
belongs_to :favorite_store
end
FavoriteStoreのModelファイル
class FavoriteStore < ApplicationRecord
has_one :customer
end
バグ調査
既存のCustomerレコードが実際に更新できないのかどうかをコンソール上で確認したところ以下エラーメッセージが出力されました。
raise_validation_error: Favorite storeを入力してください (ActiveRecord::RecordInvalid)
また、既存のレコードを見てみるとこのようなデータになっていました。
#<Customer:0x0000ffff9b536560
id: 1,
name: "test taro",
age: 25,
phone_number: "080-1111-2222",
created_at: Thu, 18 Apr 2024 20:29:40.644320000 JST +09:00,
updated_at: Thu, 18 Apr 2024 20:29:40.644320000 JST +09:00,
favorite_store_id: 0>
原因
Customerテーブルへfavorite_store_idを必須項目として後から追加したことで、Railsの仕様で既存レコードにはid=0が設定されていました。
レコード更新を行った際にid=0のFavoriteStoreレコードを紐づけることができず(idは1から設定される)エラーが発生しました。
また、この設計ではそもそも外部キー制約が設定されていません。
正しい設計
本来あるべき正しい設計を示します。
Customerテーブルへ外部キーを設定するマイグレーションファイルを以下のようにする必要があります。
add_referenceを設定することで以下メリットがあります。
- {項目名}_idを自動設定してくれる
- インデックスを自動設定してくれる
class AddReferencesToCustomer < ActiveRecord::Migration[7.0]
def change
add_reference :customers, :favorite_store, foreign_key: true
end
end
CustomerのModelファイル
optional: trueを設定することで、既存レコードがnilであることを許容し、更新をかけることが可能となります。
※しかし、新規レコードの登録についてもnilを許容してしまうのでバリデーションをかけるなどして対応しましょう。
class Customer < ApplicationRecord
belongs_to :favorite_store, optional: true
end
また、既存のレコードは失敗設計時のデータと比較しfavorite_store_idが0からnilになります。
#<Customer:0x0000ffff9b536560
id: 1,
name: "test taro",
age: 25,
phone_number: "080-1111-2222",
created_at: Thu, 18 Apr 2024 20:29:40.644320000 JST +09:00,
updated_at: Thu, 18 Apr 2024 20:29:40.644320000 JST +09:00,
favorite_store_id: nil>
失敗から学んだこと
改めて今回の失敗を通して最も大切だと思ったことは漏れなくテストしよう！！！です。
結局、DB設計が間違っていたとしても既存データが問題なく更新できることがしっかりテストされていればバグに気づくことができますし、そこで正しい設計を理解することができます。
テストケースの作り方については失敗しないテストケースの作り方と、効率よくテストを進める方法が参考になるかもしれません。併せて読んでみてください。
まとめ
今回はシステムや経緯について省略しましたが、実際は数十万のデータや個人情報を扱っているため、これがリリースした後に発覚すれば大きな障害となってしまう可能性があります。
皆さんが失敗する前に何か参考になれば幸いです。
正しい設計について誤りやより良い方法があれば、ぜひコメントお願いします。
ここまで読んでくださりありがとうございました！