今年の1~3月は多忙につきゾンビとして日の当たらぬ日々を送っていた野地である。ホントお久しぶりである。今回は前回の続きで残りの3つのテーブルを作成するところまでを紹介したいと思う。前回と今回をまとめて理解できれば実際のアプリケーションでも一通りのデータベース設計が行えるだろう。memo_category_relationテーブルを作成する。新たに登場する型や、リレーショナルデータベースの最も得意とするところである複数のテーブルの関係と、逆に苦手とされてる木構造データの扱い方を紹介しながらの作成になるので、じっくりと理解しながらテーブルを作成してみて欲しい。前回はuserテーブルを作成したので、今回はmemoテーブルの作成から始めよう。memoテーブルのカラムは以下になる。一意性を保つための数値。オートインクリメントを設定する。メモが削除されたかのboolean値。actはそれぞれuserテーブルで指定したものと同じ役割なので説明は割愛する。actについては前回説明した複合インデックスをuser_idと一緒に張るのでuserという名前でインデックス設定を追加しておこう。user_idについてはリレーションという概念に関わってくるのでリレーションとはにて説明しよう。titleは長文を設定しないのでVARCHAR型の長さ255としている。タイトルに25文字も必要ないと判断したら長さは100でも50でもいいかもしれない。メモのタイトル次にcontentだが、ここで初めて設定するデータ型であるTEXT型を設定する。メモ本文TEXT型はVARCHAR型と同じく文字列を格納するデータ型だが、明確に違うのは挿入可能なデータの大きさであり、TEXT型はVARCHAR型より多くの文字列を格納することができる。TEXT型にも上限は存在し、TINYTEXT255文字TEXT65,535文字MEDIUMTEXT16,777,215文字LONGTEXT4,294,967,295文字と、派生型によって上限文字数が異なる。ブログ記事一つ分くらいならTEXT型で十分事足りるはずだが、小説や論文データなどを格納する場合は章などで区切ることを検討した方が良いだろう。VARCHAR型とは異なり、上限値の長さはそのまま文字数ではなく、マルチバイト文字(日本語等)を使用するとそれだけ短くなる点にも注意。このようにTEXT型は長文を格納するデータ型であるため、長さの設定も行わなくてよい。全ての、文字を格納するカラムはVARCHAR型ではなくTEXT型で設定すればいいのかと言うとそうでもない。この連載では触れないが、TEXT型にはINDEXが効かないという弱点があり、検索効率がVARCHAR型に比べ極端に悪いという特徴がある。大雑把に言えばデータは大量に入るが、検索スピードが遅くなるのだ。ある場合、両者の内部処理は一緒なので性能に違いはないらしい。混乱を避けるためにも明示的な使い分けは必須だろう。次にreg_timeだが、こちらはDATETIMEというデータ型を設定する。登録日時JavaScriptやPHP等のプログラミング言語ではなかなかお目にかかれないが、SQLの世界では日付と時間型という型が独立して存在する。時間というデータはプログラミング言語では文字列であったり数値であったりするが、データベース上では時間による絞り込みや時間差の算出等をよく使うためであろう。HH:ii:ss」という形で日時情報が入る。日本語に直すと「西暦4桁+半角ハイフン+月2桁+半角ハイフン+日2桁+半角スペース+時2桁+半角コロン+分2桁+半角コロン+秒2桁」といった文字列を格納していると解釈して構わない。DATETIME型には日時情報が全て入るため秒レベルまでのある一点を指し示すことができるが、日レベルまでで構わない場合はDATE型、逆に日付は不要の場合はTIME型を使用できるので覚えておくとよいだろう。ちなみに現代の事象を扱う場合は全く注意する必要はないが、「YYYY」部分、つまり西暦部分においてMySQLが公式にサポートしているのは「1000」から「9999」までなので、奈良時代以前やSFレベルの未来を扱うときは注意した方がよい。今回初めて設定するデフォルト値の「CURRENT_TIMESTAMP」だが、これはデータを挿入するときにreg_time型が未設定だったときにDBが把握している現在時刻をそのまま入れてくれる設定となる。場合はアプリケーション側で明示的な日時を指定するべきだが、保険的な意味でも設定しておくといいかもしれない。リレーションとは世の中には色々なデータベースが存在するが、MySQLはリレーショナルデータベースというデータベースに分類される。リレーショナルとは日本語に訳すと「関連する」という意味だが、リレーショナルデータベースの場合はテーブル同士の関連を指す。今回の例ではmemoテーブルも前回作成したuserテーブルに関連するテーブルだ。memoテーブルのuser_idカラムはどのユーザーがそのメモを作成したかを表すカラムである。user_idカラムにはINT型を指定するが、このカラムにはuserテーブルのidと同じ情報を格納する。1番を持つユーザーの作成したメモの一覧を検索することができるし、逆にあるメモを作成したユーザーのname、つまり名前情報等を取得することが可能になるわけだ。ところで、そもそもなぜデータを複数のテーブルに分割するのだろうか。これぞリレーショナルデータベースの真髄と言っても過言ではないのだが、それは「ユーザーというデータ」と「メモというデータ」の関係が1対1ではなく1対nだからである。1対nという言い回しは、より分かりやすく言えば一つに対して複数が結びついている状態のことである。3のメモの作者というデータの作者だったとしよう。これを素直に表にするのであれば以下のようになる。id」列と「作者の名前」列が被っているのが分かるだろう。人間にとっては見やすい表ではあるが、システムとして考えたとき「作者の名前」列データは冗長なデータである。id」が1ならば「作者の名前」は必ず「Aさん」になるからだ。「Aさん」がメモを追加していく度に同じデータである「作者の名前」データが溜まっていくのは有限のハードディスクやSSDを使っている限りデータ容量の無駄である。そもそもメモを一つも持っていない「Cさん」が存在していた場合、この表から「Cさん」のデータを読み取ることは不可能である。そこで過去の人々が考えたのがテーブルの分割、すなわちリレーショナルデータベースモデルである。id」だけになるし、Cさんがメモを一つも持っていなくてもこのデータベースを使用しているシステムにCさんというユーザーが存在することは明らかだ。後になって「Aさん」が「A`さん」に改名した際も、わざわざメモデータを格納しているテーブルの情報を上書きしなくても「すごいメモ」や「あやしいメモ」の持ち主名が「A`さん」になるというメリットもある。このように表データを、人間が見るのではなく、コンピューターシステムによって取り回しやすいように変形することを「データベースの正規化」という。この記事では正規化について詳しく説明しないが、この正規化を正しく行うことでデータの矛盾や余分を設計段階で防ぐことができる。定義はもっと厳格なのだが、現時点ではとりあえず「1対1ではないデータはテーブルを分ける」ことを意識できれば十分だろう。話を戻して、user_idカラムの設定は以下となる。user_idデータ型と長さはuserテーブルのidと同じデータを格納するため条件も揃えよう。前述したactカラムとの複合インデックスも張っておくと「あるユーザーの削除されていないメモ」を検索するときの処理が高速になるだろう。加えて時間指定をするのであればreg_time、タイトル検索をするならばtitle、といった具合に複合インデックスへの合成物を混ぜていくとより詳細な検索への高速化が見込める。一回の検索で使えるインデックスは一つだけという制約があるため、どんなインデックスをどれだけ用意するかは作成するサービスの検索種類・頻度によって調整すべきだ。以上でmemoテーブルは完成である。木構造の問題と解決モデルの紹介メモができたら次にカテゴリーを扱うテーブルを作成しよう。categoryテーブルの各カラムは以下の4つになる。一意性を保つための数値。オートインクリメントを設定する。