SHA256による変換を想定するため必ず64文字となる。ユーザーが有効なのか無効なのかのboolean値。INT型とAI(オートインクリメント)、Primaryキーまずはidカラムから作成しく。その前に、このカラムが必要な理由である一意性について説明しよう。データベースの基本として、各レコードは一意性を保つほうが良いというルールがある。それは各カラムの情報で対象のレコードが一つに絞り込める性質のことである。情報を持っているが、西暦何年時の何年何組の何番、と3つの情報を組み合わせれば対象となる生徒はただ一人に定まる。これが一意性を持っている状態である。これにより、両親の再婚等の理由である生徒の名前が変わったときに、書類上のデータも合わせて変更するときは「何年何組の何番を○○という名前に変更する」という操作ができるのである。現実の世界ではそんな周りくどい言い回しではなく、「もともと○○君だったのを□□君にする」といった思考で名前を変更するだろう。名前は同姓同名の可能性がある。何百万というデータを処理するデータベースの定義に基づくなら、名前というカラムは確実に一意性を保つ情報にならないのである。話をidカラムに戻そう。先の例では、学生を一人に絞り込むのに4種類のカラム情報が必要だった。データベースの用語で言い換えると、複合主キーによって一意性が保たれている状態である。一意性を満たしている、という観点からは問題ないのが、生徒の情報を変更するときに毎回4種類の情報を用意し参照するのは面倒である。代わりに、絶対被らない連番をふっておけば、その数字だけで個人が特定できる。これぞidカラムの役割である。phpMyAdminの入力欄のうち、一番上の行を以下のように入力しよう。Primary(ここより前に次のAIのチェックを入れると勝手に設定される。AIデータ型のINTとは整数値を扱うデータのことで、マイナス値、0、整数値を入れることができる。次の長さはデータ型が数値型だった場合は桁数を表し、今回は11桁(=999億9999万9999まで数えられる。マイナス方面も考えると二倍になる)を指定した。デフォルト値はなし、NULLもなし、コメントはオートインクリメントの略である「AI」とした。AIとインデックスだが、AIはチェックを入れるとMySQL側が勝手に被らない連番をふってくれる機能なので、チェックを入れる。AIにチェックを入れると勝手に設定しようとしてくるが、インデックスにはPrimary属性を設定しておこう。インデックスは検索を早くする目的で使われるものだが、先ほど説明した一意性を担保する機能も兼ねているので、その意で使われるPrimaryキーを設定しておくのだ。VARCHAR型と文字列の扱い続いて、二行目にはnameカラムを設定していく。ユーザー名このカラムにはユーザー名を保存するのだが、INT型には数値しか入らないので、文字列を保存できるVARCHAR型を設定する。長さは数値型の時は桁数を表していたのに対し、文字列型は文字数を表すので、VARHCHARの最大値である255を設定しよう。255なのかというとVARCHAR型は文字数を保存するためのデータが一緒に保存されるのだが、256文字以上だとその部分のデータ量が2バイト以上になってしまうため、1バイトで済む最大文字数である255文字に設定するのである。VARCAHR型は必要であれば65,535文字まで保存できるが、ユーザー名は255文字以下で十分だろう。同じように、mailもカラムも設定する。メールアドレスインデックス以外はnameカラムとほぼ変わらない。インデックスは後でactカラムを作成するときに再説明するので今は出現したモーダルウィンドウの「インデックス名」にloginと入力して他はデフォルトのままインデックスを登録しよう。次にpasswordカラムだが、基本的にデータベースにはパスワード等の他人に知られるとマズい情報は平文(=そのまま読める文字列)で保存すべきではない。パスワードは暗号にして保存するのだが、パスワードはアプリケーション側(PHPやRuby等)でハッシュという種類の暗号に変換して保存することが多い。ハッシュとは一度暗号化したら元に戻せないが、同じ文章を暗号化したら必ず同じ値が得られるという特徴を持った暗号である。パスワードの平文を用いてログイン認証を行う場合、その平文をハッシュ化した文字列と、データベースに保存してある同じ方法でハッシュ化済の文字列が一致するかでパスワードが一致しているか確認できるというわけだ。すれば万が一データが流出しても、攻撃者には元となるパスワード文字列が分からないのである程度の保険にはなるのである。今回passwordカラムではコメントに書いてある通り、sha256というアルゴリズムを使用する想定でカラムを作成する。このsha256というアルゴリズムを通すと、元の文字列がどんな長さ・内容であれ変換後の文字列は64文字となる性質があるため長さは64文字となるわけだ。このタイミングで最後のカラムを作成する前に画面の下部にある「Collection」を設定してみよう。今まで無視してきた「照合順序」という欄だが、これは使用する文字コードのことであり、ここを設定すると個別に文字コードを指定できるのだが、未指定の場合は「Collection」で設定された文字コードを使用するので「Collection」での一括指定で事足りることが多い。セレクトボックスを開いてみると大量の文字コードが並んでいるが、日本語サービスを作ろうと思ったらとりあえず下のほうにあるutf8_general_ciを選択すれば大抵問題ないので、今回もこれを設定しよう。BOOLEAN型、論理削除と物理削除、複合インデックス最後にactカラムを作成しよう。このカラムはユーザーが通常のデータか、もしくは削除されたデータなのかを識別するためのカラムである。大体のプログラミング言語では数値と真偽値を明確に区別していることが多いが、SQLにおいてBOOLEAN型は数値の一種であり、この型に入れるべき値は0(false)か1(true)のみである。入れるべき、という言い回しになってしまうのは、実際には-127から127の範囲の数字は入ってしまうからである。この型のカラムを操作するときはアプリケーション側でちゃんと値を制限しておくことが望ましい。SQLにはレコードの削除(DELETE)という機能が存在し、もちろんこのuserテーブルに対しても削除は行える。その機能を使わずに削除フラグを用意するのか。データベース的な言い回しでは本当にデータを削除する事を物理削除、削除フラグを立てて削除したと見せかけることを論理削除という。このuserテーブルでは論理削除で削除を実行するわけだが、この方法を採用するメリットはいくつかある。削除されたユーザーのデータが後から復元できるという点である。サービスによってユーザーのデータを削除するというのが退会を指すのか文字通りの削除を指すのかは分かれるが、削除されたユーザーのデータを残しておけば後から統計を取るときやユーザーを復活することができるのだ。後で作成するmemoはuserテーブルを参照することになるので、参照先のデータが存在しないことによるエラーを潜在的に防ぐことができる。userテーブルが巨大になったとき顕著になるのだが、物理削除はインデックスを計算し直すことになるので論理削除に対して処理が重いという特徴がある。以上を踏まえて、actカラムの設定は以下のようになる。有効/無効ここに来て初めて設定することになるデフォルト値だが、これはデータを追加するときに明確に指定されなかったカラムに設定される値で、未指定だった場合BOOLEAN型は0が入る。アプリケーション側でしっかり1を挿入すれば問題無いが、ユーザーを追加する際にはデフォルトで有効にしておきたいので、ユーザー定義を選択し、新たに表れる入力欄に1と入力しておこう。全角の1を入力しないように注意。インデックスの設定だが、INDEXを選択した場合、既になにかしらのカラムでINDEXが設定されていると出現するモーダルウィンドウで「単一カラムのインデックスを作成」と「複雑なインデックスを作成」の両者が選択できるようになる。今回は「複雑なインデックスを作成」を選択し、現れた「合成物」でmailを選択しよう。これでmailカラムとactカラムを二つ纏めた複合インデックスが設定されることになる。これを設定するのかというと、実際のサービス運用時、具体的に言えば新規ユーザー登録時に同じメールアドレスが登録されていないか調べる際に「有効なユーザーの中からメールアドレスが○○○であるユーザーを探す」という絞り込みが発生することが予想されるからだ。インデックスを作成すると検索が早くなるというのは前述したが、実はMySQLには、データの検索を行うときに使用されるインデックスは一つだけという制約がある。mailカラムとactカラムそれぞれにインデックスを張るより二つを一緒にしたインデックスを一個作るほうが検索の高速化が見込めるというわけである。ログイン認証の際には更にpasswordカラムも混ぜたインデックスが必要になるのではないか、という想像もできるが、有効(act=1)なユーザーに限ればあるメールアドレスとmailカラムが一致するデータは1個、もしくは0個なので、そのデータのpasswordが持つ値をアプリケーション側で比較するという手を使えば合理的と考えることもできる。これはケースバイケースなので実際のサービス要件によって考えてみて欲しい。ここまで入力し終えたら右下の「SQLのプレビュー」をクリックしてみよう。すると以下のようなSQLが表示される。'ユーザーデータ';今まで様々な情報を入力したが、その目的はこのSQL文を作成し、それをMySQLに実行させることでテーブルを作成することである。今後開発を進める中で、アプリケーション側で動的にテーブルを作成したり、コマンドラインから直接データベースを操作する場合はこのようなSQLを直打ちすることで全く同じようにテーブルを作成できるので、慣れてきたらこの構文を勉強すると様々な場面で役に立つだろう。「SQLのプレビュー」の横にある「保存する」をクリックすればこのSQLが実行され、晴れてテーブルが作成される。まとめテーブルを一つ作るところまで進めてみたが、意外とあっけないと感じただろうか。それとも想像以上に複雑だったと感じただろうか。当然だがデータベース設計はサービスの要件によってかなり変わるもので、最適な構造を設計するにはかなりの知識・経験を要する技術である。自分もまだまだ修行中ではあるが、なかなか思い付きで勉強し辛い分野なので、この連載が読者の第一歩になってくれれば幸いである。次回はリレーショナルデータベースの真骨頂であるテーブル結合のためのテーブル設計やTEXT型、木構造や中間テーブルについて説明しつつ残りの3テーブルを作成していくのでお楽しみに。userテーブルで指定したものと同じ役割なので説明は割愛する。コメントをキャンセル入力エリアすべてが必須項目です。designerTwitterhttps://twitter.com/Facebookhttps://www.facebook.com/others小田原産Webエンジニア。フロント/バック半々。最近はCodeigniterでECサイトばっかり作っている。