設計フェーズにおいて、どの設計から始めるかは、設計担当するエンジニアによってまちまちだと思う。私の場合、アプリケーション内で取り扱われるデータの体系が掴みやすくなるので、データベースを使うシステムの場合、データベースのテーブル設計から行うことが多い。個人的に、このデータベースモデルの設計がとても好きだということも大きいのだが、私の嗜好を抜きにしてもメリットが多い。まず、プログラム側で取り扱われるデータの「型」を暗黙的に定義していくことになるし、効率的な検索を行うためのSQLを想像しながらカラム構成を決め、シンプルなJOIN構造になるべくデータリレーション等を考えていると、頭の中におぼろげながら処理の完成形が浮かんで来る。いわば、自分の開発センスが研ぎ澄まされていくような感じがして、開発へのモチベーションを一段と高めてくれるのだ。
データベースが苦手だと思っている人も是非、一度このテーブル設計をやってみて欲しい。プログラミングやUIデザインといった製造フェーズとは一味変わった設計の面白みに気づけるのではないだろうか。
テーブル設計 – Design Tables
それでは早速、今回のアプリケーションに必要となるだろう各種テーブルを設計していく。まずは武器データを格納するテーブルからだ。
武器マスターテーブル: weapons.tbl
| カラム（物理名） | カラム（論理名） | タイプ | 属性 | 補足 |
|---|---|---|---|---|
| id | 武器ID | int | unsigned, auto_increment | |
| name | 武器名 | varchar | not null | |
| type | 武器種 | tinyint | unsigned, not null | 大剣=0/太刀=1/片手剣=2/双剣=3/ハンマー=4/狩猟笛=5/ランス=6/ガンランス=7/スラッシュアックス=8/チャージアックス=9/操虫棍=10/ライトボウガン=11/ヘビィボウガン=12/弓=13 |
| rarity | レア度 | tinyint | unsigned, not null | |
| rank | ランク | tinyint | unsigned, not null | 下位(High Rank)=1/上位(Low Rank)=2 |
| attack | 攻撃力 | int | unsigned, not null | |
| sharpness | 切れ味 | json | 当面は未使用 | |
| affinity | 会心率 | tinyint | not null, default 0 | |
| defense_bonus | 防御力 | int | not null, default 0 | |
| element1 | 属性1 | tinyint | unsigned, not null | 無属性=0/火属性=1/水属性=2/雷属性=3/氷属性=4/龍属性=5/毒属性=6/麻痺属性=7/睡眠属性=8/爆破属性=9 |
| element2 | 属性2 | tinyint | unsigned, not null | 無属性=0/火属性=1/水属性=2/雷属性=3/氷属性=4/龍属性=5/毒属性=6/麻痺属性=7/睡眠属性=8/爆破属性=9 |
| elem1_value | 属性値1 | int | unsigned, not null, default 0 | |
| elem2_value | 属性値2 | int | unsigned, not null, default 0 | |
| slot1 | スロット1 | tinyint | unsigned, not null, default 0 | スロットなし=0/スロット[1]=1/スロット[2]=2/スロット[3]=3/スロット[4]=4 |
| slot2 | スロット2 | tinyint | unsigned, not null, default 0 | スロットなし=0/スロット[1]=1/スロット[2]=2/スロット[3]=3/スロット[4]=4 |
| slot3 | スロット3 | tinyint | unsigned, not null, default 0 | スロットなし=0/スロット[1]=1/スロット[2]=2/スロット[3]=3/スロット[4]=4 |
| forging_materials | 生産素材 | json | 当面は未使用 | |
| upgrade_materials | 強化素材 | json | 当面は未使用 | |
| forge_funds | 生産費用 | int | unsigned, not null | |
| upgrade_funds | 強化費用 | int | unsigned, not null | |
| tree | 派生名 | varchar | not null | |
| rampage_skills | 百竜スキル | json | not null | 当面は未使用 |
「切れ味」カラムをJSON型にしているのは、現状ではそれぞれの切れ味ゲージの長さがわからないので、後々判明したら、
{ red: 20, orange: 10, yellow: 30, green: 20, blue: 15, white: 5, parple: 0, blank: 0 }
──みたいにデータ化しようかと思ったからだ。「生産素材」や「強化素材」についても、必要があれば 素材名:個数 のJSONデータで格納できるようにしておき、素材自体は素材マスターテーブルを追加して管理できるような建付けにしてある。
まぁ、今回のスキル検索には切れ味や素材のデータは不要なので、カラム枠だけ確保しておけばいいだろう。
同様に、武器の派生を管理する必要があれば、武器派生テーブルを追加して、そこで武器ID同士の連結を管理すれば良さそうだ。それも今回の機能では不要なので、テーブル自体は用意しない。
防具マスターテーブル: armors.tbl
次に、防具データ管理用のテーブルだ。
| カラム（物理名） | カラム（論理名） | タイプ | 属性 | 補足 |
|---|---|---|---|---|
| id | 防具ID | int | unsigned, auto_increment | |
| name | 防具名 | varchar | not null | ユニーク値なのでindex候補 |
| series | シリーズ名 | varchar | not null | |
| part | 部位 | tinyint | unsigned, not null | 頭=0/胴=1/腕=2/腰=3/脚=4 |
| rarity | レア度 | tinyint | unsigned, not null | |
| rank | ランク | tinyint | unsigned, not null | 下位=1/上位=2 |
| defense | 防御力 | int | unsigned, not null | Lv1時の防御力 |
| level | レベル | tinyint | unsigned, default 1 | |
| max_level | 最大レベル | tinyint | unsigned, not null | |
| fire_resistance | 火耐性 | tinyint | not null | |
| water_resistance | 水耐性 | tinyint | not null | |
| thunder_resistance | 雷耐性 | tinyint | not null | |
| ice_resistance | 氷耐性 | tinyint | not null | |
| dragon_resistance | 龍耐性 | tinyint | not null | |
| slot1 | スロット1 | tinyint | unsigned, not null, default 0 | スロットなし=0/スロット[1]=1/スロット[2]=2/スロット[3]=3/スロット[4]=4 |
| slot2 | スロット2 | tinyint | unsigned, not null, default 0 | スロットなし=0/スロット[1]=1/スロット[2]=2/スロット[3]=3/スロット[4]=4 |
| slot3 | スロット3 | tinyint | unsigned, not null, default 0 | スロットなし=0/スロット[1]=1/スロット[2]=2/スロット[3]=3/スロット[4]=4 |
| skills | スキル | json | {スキルID:スキルLv,…} | |
| forging_materials | 生産素材 | json | 当面は未使用 | |
| forge_funds | 生産費用 | int | unsigned, not null |
悩んだ末にJSON型にしたのが、スキルのカラムだ。個々の防具にバンドルされているスキルの数は、今のところ上限が4つまでなのだが、もし5つ以上スキルを持つ防具が出たときに、スロットカラムのように skill1, skill2, skill3 とカラムを分けてしまうと、カラム追加が必要になって来てしまう。テーブルスキーマの変更はシステム全体へのインパクトがけっこう大きいので、最初から可変データに対応できるようにしておいた方が無難だろうと考えた結果である。JSON型はSQL上での取り扱いに難があるので、あまり多用したくなかったのだが、ここは仕方ないと割り切った。
護石マスターテーブル: talismans.tbl
次は護石管理用テーブルだ。
| カラム（物理名） | カラム（論理名） | タイプ | 属性 | 補足 |
|---|---|---|---|---|
| id | 護石ID | bigint | unsigned, auto_increment | |
| name | 護石名 | varchar | ||
| rarity | レア度 | tinyint | unsigned, not null | |
| slot1 | スロット1 | tinyint | unsigned, not null, default 0 | スロットなし=0/スロット[1]=1/スロット[2]=2/スロット[3]=3/スロット[4]=4 |
| slot2 | スロット2 | tinyint | unsigned, not null, default 0 | スロットなし=0/スロット[1]=1/スロット[2]=2/スロット[3]=3/スロット[4]=4 |
| slot3 | スロット3 | tinyint | unsigned, not null, default 0 | スロットなし=0/スロット[1]=1/スロット[2]=2/スロット[3]=3/スロット[4]=4 |
| skills | スキル | json | {スキルID:スキルLv,…} |
今回のモンハンライズでは護石は自分で生産することになったため、護石に付与されるスキルやスロットはユーザー毎に千差万別となる。このテーブルには都度ユーザーの護石を登録できるようにして、それを再利用することを想定している。
装飾品マスターテーブル: decorations.tbl
次に、装飾品管理用のテーブルだ。
| カラム（物理名） | カラム（論理名） | タイプ | 属性 | 補足 |
|---|---|---|---|---|
| id | 装飾品ID | bigint | unsigned, auto_increment | |
| name | 装飾品名 | varchar | not null | ユニーク値なのでindex候補 |
| rarity | レア度 | tinyint | unsigned, not null | |
| slot | スロット | tinyint | unsigned, not null, default 1 | スロット[1]=1/スロット[2]=2/スロット[3]=3/スロット[4]=4 |
| skills | スキル | json | not null | {スキルID:スキルLv,…} |
| forging_materials | 生産素材 | json | 当面は未使用 | |
| forge_funds | 生産費用 | int | unsigned, not null |
装飾品とは加工屋で生産できる「○○珠」と呼ばれる装備に着脱できるスキルの素のことだ。基本的に装飾品1個で1スキルの1レベルのみ発動されるが、前作モンハンワールドでは複合珠と呼ばれる一度に2レベル以上を発動できたり、複数スキルを同時に発動できる装飾品が追加されていたので、今作のライズでも将来的にそのような装飾品が登場することを見越して、複合スキル構成にも対応できるように「スキル」カラムはJSON型にしてある。
スキルマスターテーブル: skills.tbl
最後に、スキル管理用のテーブルだ。
| カラム（物理名） | カラム（論理名） | タイプ | 属性 | 補足 |
|---|---|---|---|---|
| id | スキルID | int | unsigned, auto_increment | |
| name | スキル名 | varchar | not null | ユニーク値なのでindex候補 |
| description | スキル概要 | text | not null | |
| max_lv | 最大レベル | tinyint | unsigned, not null, default 1 | |
| status | レベル別ステータス | json | {スキルLv:内容,…} |
とりあえず、これらのテーブルがあれば、スキルの検索ができるだろう。まぁ、この時点での設計は完全である必要はない。実際に処理やUIを開発していく過程で、不足しているカラムやテーブルが見つかったら、追加すれば良いだけだからだ。また、検索性能に影響を及ぼすインデックスについても、実際のSQLを作成する際にパフォーマンス・チューニングをしながら適宜追加・削除して行く方向で問題ない。
リリース前であれば、ぶっちゃけゼロから全テーブルを作り替えてしまっても構わない（開発コストが溢れない限り、そして納期に間に合いさえすれば、だが……）。ただし、一度リリースしてしまうと、そんな緩いことはできなくなる。リリース後にテーブル構造を変更するのはデータ破損や消失をはじめとする、システム障害のリスクが高くなるためだ。特に業務用アプリケーションの場合、サービスを一時停止してのメンテナンス対応を入れる等、ステークホルダーを巻き込んで大事になることもしばしばある。
今回は趣味アプリなので、その辺はまったく気にしないで気楽に作れるのが強みだ。ただ、リスクが低いと云っても、後からテーブル構造を変更するのは影響範囲が大きいのでなるべく避けるのがベターである。もし、仕様があいまいなデータを取り扱うような場合は、今回の設計でも採用したようにデータの取り扱いが柔軟なJSON型カラムを利用したり、あらかじめ拡張用のカラムを準備しておく等の対策を講じておくと、安心である。
データベースとテーブルの作成
さて次に、今回のアプリケーション用にMySQLデータベースに専用のデータベースを作る。JSON型のカラムを取り扱うので、MySQLは5.7以降のバージョンが必須だ。
それでは、早速MySQLにログインして、データベースを作成してみる。
mysql> CREATE DATABASE IF NOT EXISTS mhr_simulator_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use mhr_simulator_db;
Database changed
データベースを作った後は use データベース名 で利用するデータベースにマウントする必要がある。あとは前章のスキーマを元にしてテーブル作成用のCREATE TABLE文を作っていく。
mysql> CREATE TABLE `weapons` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '武器ID',
`name` varchar(255) NOT NULL COMMENT '武器名',
`type` tinyint(4) unsigned NOT NULL COMMENT '武器種',
`tree` varchar(255) NOT NULL COMMENT '派生名',
`rarity` tinyint(4) unsigned NOT NULL COMMENT 'レア度',
`rank` tinyint(4) unsigned NOT NULL COMMENT 'ランク',
`attack` int(11) unsigned NOT NULL COMMENT '攻撃力',
`sharpness` json DEFAULT NULL COMMENT '切れ味',
`affinity` tinyint(4) NOT NULL COMMENT '会心率',
`defense_bonus` int(11) NOT NULL COMMENT '防御力',
`element1` tinyint(4) unsigned NOT NULL COMMENT '属性1',
`element2` tinyint(4) unsigned NOT NULL COMMENT '属性2',
`elem1_value` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '属性値1',
`elem2_value` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '属性値2',
`slot1` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT 'スロット1',
`slot2` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT 'スロット2',
`slot3` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT 'スロット3',
`rampage_skills` json DEFAULT NULL COMMENT '百竜スキル',
`forging_materials` json DEFAULT NULL COMMENT '生産素材',
`upgrade_materials` json DEFAULT NULL COMMENT '強化素材',
`forge_funds` int(11) unsigned NOT NULL COMMENT '生産費用',
`forge_with_money` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '購入費用',
`upgrade_funds` int(11) unsigned NOT NULL COMMENT '強化費用',
`rollbackable` bit(1) NOT NULL DEFAULT b'1' COMMENT 'ロールバック可否',
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='武器マスター'
;
Query OK, 0 rows affected (0.02 sec)
ここでは武器マスターテーブルのみCREATE TABLE文を紹介しているが、全てのテーブルのCREATE TABLE文が知りたい場合はこちらのGistページを参照して欲しい。
これで、アプリケーション用のデータベースとテーブルが出来ているハズなので、構成を確認してみよう。テーブル情報の確認には desc テーブル名 のコマンド（クエリ）を使う。
mysql> desc weapons;
+-------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | MUL | NULL | |
| type | tinyint(4) unsigned | NO | | NULL | |
| tree | varchar(255) | NO | | NULL | |
| rarity | tinyint(4) unsigned | NO | | NULL | |
| rank | tinyint(4) unsigned | NO | | NULL | |
| attack | int(11) unsigned | NO | | NULL | |
| sharpness | json | YES | | NULL | |
| affinity | tinyint(4) | NO | | NULL | |
| defense_bonus | int(11) | NO | | NULL | |
| element1 | tinyint(4) unsigned | NO | | NULL | |
| element2 | tinyint(4) unsigned | NO | | NULL | |
| elem1_value | int(11) unsigned | NO | | 0 | |
| elem2_value | int(11) unsigned | NO | | 0 | |
| slot1 | tinyint(4) unsigned | NO | | 0 | |
| slot2 | tinyint(4) unsigned | NO | | 0 | |
| slot3 | tinyint(4) unsigned | NO | | 0 | |
| rampage_skills | json | YES | | NULL | |
| forging_materials | json | YES | | NULL | |
| upgrade_materials | json | YES | | NULL | |
| forge_funds | int(11) unsigned | NO | | NULL | |
| forge_with_money | int(11) unsigned | NO | | 0 | |
| upgrade_funds | int(11) unsigned | NO | | NULL | |
| rollbackable | bit(1) | NO | | b'1' | |
+-------------------+---------------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)
意図したテーブル設計通りの構造になっているのでOKだ。
さて、ここからはSQLの基礎的な話になる。
この段階では各テーブルにデータが入っていないので、何かサンプルデータを登録してみる。テーブルにデータ（レコード）を登録する場合は、INSERT文を使う。
INSERT INTO weapons (
name, type, tree, rarity, rank, attack, sharpness, affinity, defense_bonus,
element1, element2, elem1_value, elem2_value,
slot1, slot2, slot3, rampage_skills,
forging_materials, upgrade_materials, forge_funds, forge_with_money, upgrade_funds, rollbackable
) VALUES (
'カムラノ鉄大剣Ⅰ', 0, 'カムラ派生', 1, 1, 50, null, 0, 0,
0, 0, 0, 0,
0, 0, 0, JSON_ARRAY('攻撃力強化Ⅰ','会心率強化Ⅱ','防御力強化Ⅰ'),
null, null, 300, 600, 0, false
),
(
'カムラノ鉄大剣Ⅱ', 0, 'カムラ派生', 1, 1, 60, null, 0, 0,
0, 0, 0, 0,
0, 0, 0, JSON_ARRAY('攻撃力強化Ⅰ','会心率強化Ⅱ','防御力強化Ⅰ'),
null, null, 0, 0, 200, true
)
;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
INSERT文で覚えておくべきなのは、サロゲートキーを持つ（プライマリキーが自動採番型である）テーブルにデータを追加する場合に、サロゲートキーであるカラム（上記武器マスターテーブルでのidカラム）を指定しないことで、データベース側で重複しない最適な番号を自動的に付与してくれるということだ。カラム自体に自動採番（auto_increment）属性を持っていないPostgreSQLのようなデータベースもあるが、そういうデータベースだったとしてもシーケンス属性を利用することで同じ動きを実現できる。
次に、登録したデータを参照してみる。今回のアプリケーションではこの参照クエリであるSELECT文が処理のキモになって来るので、特に重要である。
mysql> SELECT id,name,attack FROM weapons WHERE rollbackable = true;
+----+--------------------------+--------+
| id | name | attack |
+----+--------------------------+--------+
| 2 | カムラノ鉄大剣Ⅱ | 60 |
+----+--------------------------+--------+
1 row in set (0.00 sec)
なお、テーブル内のデータ件数が知りたい場合は、SELECT COUNT(id) FROM weapons;のようにプライマリキーをカウントするクセをつけよう。よく巷では COUNT(*) のようにカラム全体を取得するワイルドカードを利用したクエリが紹介されているのを見かけるが、内部的にメモリ効率が悪いので利用は控えるべきである。暗黙的にインデックス対象となっているプライマリキーをカウントするのが正しいのだ。基本的に、人力でコマンドラインからデータベースをメンテナンスする時以外に、アプリケーション側から発行するクエリ内にワイルドカード（*）を使用しないことがデータベースのパフォーマンスを維持するコツの一つであると私は信じている。
話が逸れたが、続いてデータを更新してみる。データ更新にはUPDATE文を使う。
mysql> UPDATE weapons SET rollbackable = false WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
これは、武器ID:2のデータの「ロールバック可否」を変更するクエリだ。更新されたかチェックするために、前述のSELECT文をもう一度実行してみると、今度は「カムラノ鉄大剣Ⅱ」が取得できなくなっているはずだ。
最後に、サンプルデータを削除しておく。テーブル内のデータを個別に削除する場合はDELETE文を使うが、DELETE文で削除した場合、そのテーブルのデータ番号であるidカラム（プライマリキー）の連番のインデックス（次にデータを追加した際に自動発行されるデータ番号）が初期化されない。今回はその連番インデックスも初期化しておきたいので、TRUNCATE文でテーブル自体を初期化する。
mysql> TRUNCATE TABLE weapons;
Query OK, 0 rows affected (0.01 sec)
これでデータベースの準備はとりあえず完了だ。
今回紹介したINSERT文以降のSQL文はMySQL以外のDBMSでもほぼ共通で使えるので、データベースの基礎知識として覚えておくことをオススメする。私見にはなるが、SQLが使えないプログラマーは実際の開発シーンでは頼りにならないことが多いので、CRUDと呼ばれる基礎的なクエリ（INSERT, SELECT, UPDATE, DELETE）だけでも使えるようにしておくと良いだろう。
データの投入について
作成したデータベースに「いつ」「どのように」「どんな」データを投入するかということも考えておかなければならない。今後、処理を開発するにあたっては何かしらのデータがデータベースに入っていないとテストが出来ないので、最低でも単体テスト用に断片的なデータが必要になって来る。さらに、結合テストの際にはある程度の量のデータが必要で、総合テスト（E2Eテスト：End To End Test）時にはほぼ本番運用と変わらないデータが欲しい。
つまり、一般的なデータの投入タイミングは、下記のようになる。
単体テスト（ユニットテスト）時
機能やモジュールを開発するために必要な、最小限度のデータ量のダミーデータ。
例えば、データを検索するモジュールならば、最低限検索条件にマッチするデータとマッチしないデータの両方がなければ正常動作を確認できないだろう。
この時点でのデータ投入は、開発している機能やモジュールの特性に合わせて都度々データを準備する方式でも構わない。また、この時点で投入されたデータは未完成の処理等によって破壊される可能性が高いので、継続的にデータの初期化・再投入ができるような手順を確立しておくことも重要だ。
結合テスト時
結合テストとは単体テストを通過したモジュールやコンポーネントが、他のモジュールやコンポーネントと連携して動作する際の動作を検証するためのテストである。この時点で必要になって来るデータ量は単体テスト時よりも増加する。
例えば、ページネーション付きデータ一覧を表示するコンポーネントを検証するためには、ページネーションが発生する閾値を超える量のデータがないといけない。
この時点でのデータ投入は、ある程度本番運用に近い形でのデータ精度とデータ量が求められる。また、単体テスト時と同様に、継続的なデータ初期化・再投入の必要性も高い。
総合テスト（E2Eテスト）時
この時点でのテストは、ほとんど本番運用と変わらない。エンドユーザー目線で、アプリケーション上のあらゆる操作を行い、動作やパフォーマンスに支障がないかを検証することになる。この時点で必要になるデータはできる限り本番用の実データに近い精度と量が求められる。
しかし、個人情報などを取り扱うシステム等の場合、実データをそのままテストに利用できないため、特定の個人情報（姓名や住所、メールアドレス等）をマスク（隠蔽）する等のデータ加工が必要になることもある。
このテストが完了した後は、データを初期化し、本番運用用の正式なデータを投入することになる。
結論的に、もし単体テスト時点から総合テスト並みのデータもしくは本番運用時用のデータが準備できるのならば、そのデータを投入してしまうのがベストである。しかし、数万件以上あるような大量なデータが対象だった場合などは、データの初期化・再投入作業のパフォーマンスが悪いことも多いので、その時点でどの程度のデータを投入するのがベストなのかは開発効率とのトレードオフで決めるのが良いだろう。
まぁ、今回開発するアプリケーションには特に配慮すべき特別な情報は含まれていないうえ、そこまでデータ量も多くないので、最初から本番運用時と同等のデータを準備してしまうのがベストだろう。さらには、管理側の機能として実装を想定しているデータを一括登録するための機能を一番最初に開発することで、以降のデータ投入運用が楽になるだろう。
ということで、今回はここまで。
なお、業務用システムとかの場合は、データベース設計後には設計のレビューを行って、ステークホルダーの了承を得てからデータベースの構築という流れになる。まぁ、リスクヘッジの儀式として必要なのは重々承知しているけれども、正直面倒なんだよねぇ……。その点、個人の趣味的開発にはそんなまどろっこしい工程は発生しないのだ（笑）。
ちゅーわけで、次回はアプリケーション本体の設計をサクッと終わらせる予定。
