phpMyAdminで始める初めてのデータベース設計　前半

今回のゴール

この連載では例としてユーザー登録ができて、登録されたユーザーはメモを登録できるようなサービスのテーブル設計を行う。

第一回目となる今回の記事ではXAMPP(またはMAMP)環境のインストールとphpMyAdminによるテーブル作成の手順を説明を経て、テーブルを一つ作成するところまでを行う。

ブラウザ上からSQLを操作することでPHPやRuby等のコードを書かずにMySQLを体験できるようになっているので、バックエンドなんぞ1mmも分からない、なんて人も安心して読み進めて欲しい。

XAMPP or MAMP環境によるサーバーの用意

まずはなんにせよサーバー環境が必要である。レンタルサーバーを既に持っている or 新たに登録するのも良いが、手軽に自分だけがアクセスできる開発環境を手に入れたいならXAMPPをインストールするといいだろう。Docker?　僕のWindowsでは動きませんでした……

インストールは公式サイトをブラウザから開き、対応するOSのモノをダウンロードしよう。

後はダウンロードしたファイルを開けば勝手にインストールが始まる。途中何かの選択が要求される場合は全てデフォルトで構わない。

なお、最新版のXAMPPではデータベースが「MySQL」ではなく「MariaDB」が採用されているが、今回説明する範囲では特に違いを気にせずMySQLとして使えるハズだ。

インストールが済んだらXAMPPを起動しよう。

Windowsの場合はWindwsキーを押してから「xampp」と打てば「XAMPP Control Panel」というアプリケーションが開くハズなので、それを起動すると各Serviceの起動画面となる。

phpMyAdminにアクセスするためにはApacheとMySQLの起動が必要なので、両者の横にある「Start」ボタンを押して起動させる。

起動に成功したら「Module」の名前が緑色になるので、今度はブラウザを開き、アドレスバーに「http://localhost/phpmyadmin/」と入力すればphpMyAdminを開ける。

Macの場合はアプリケーションからXAMPP.appを開けばコントロールパネルが開けるが、Winodwsと違いVM上で起動するためやや操作方法が異なる。

まずは「General」タブで「Start」をクリックし、「Service」タブで「Apache」と「MySQL」をStartさせよう。

このまま「General」タブに表示されているIPアドレスでphpMyAdminにアクセスできそうな予感がするが、実はセキュリティの問題でこのままではブラウザカラアクセスすることができない。

なので、さらに「Network」タブを開き、「localhost:8080 -> 80(Over SSH)」をEnable状態にしてからブラウザのアドレスバーに「http://localhost:8080/phpmyadmin/」と入力しよう。

また、Mac環境ではXAMPPではなくMAMPを使用するのもオススメだ。こちらはVM上でサーバーを起動させずに済むので、いくらか手順が簡略化される。

公式サイトでMAMP(MAMP PROではないので注意)からインストーラーをダウンロードし、インストーラーの指示に従ってMAMPをインストールしよう。途中で出てくる選択肢は全てデフォルトでOKだ。

インストールが終わったらアプリケーションの中にあるMAMP→MAMP.appを起動し、開いた画面の一番左にある起動アイコンをクリックすると勝手にブラウザが立ち上がる。

開いたページの上部メニューにある「TOOLS」にマウスオーバーすると「PHPMYADMIN」という項目が出てくるのでそれをクリックするとphpMyAdminが開かれるだろう。

データベースの準備

どんなデータベース構造を持つべきかはそれこそ作成するモノによるのだが、データベースを使用するアプリケーションに最低限共通するのは必ず専用のテーブルを持ち、それにアクセスできるMySQLのアカウントが存在することだ。

なので、いわゆるDDL(Data Definition Language)を発行し、データベース&テーブル&それにアクセスできるアカウントをMySQLに作成していく必要があるのだが、いきなりSQL直打ちは難易度が高いのでこの連載では編集画面でテーブル作成を行う。

ブラウザでphpMyAdminが開けたら、上部タブにある「User accounts」をクリックするとページ下部に「Add user account」が表示されるので、そこからユーザーを作成しよう。

「ログイン情報」に存在する「User Name」に任意のユーザー名、「パスワード」と「Re-type」に同じパスワードを入力し、「Database for user account」に存在する「同名のデータベースを作成してすべての特権を与える。」にチェックを入れて、一番下にある「実行」をクリックするとデータベースとそれにアクセスできるユーザーが一気に作成される。

今回は「test_user_memo」という名前のデータベース&アカウントでデータベースを作成しておこう。

データベースの作成画面

サイドメニューに作成したユーザー名と同名のデータベースが作成されているハズなので、それをクリックして表示される「構造」画面の下部に表示されている「テーブルを作成」にて任意のテーブルを作成しよう。

カラム数というのはそのテーブルに存在するカラムの数なのだが、最近のphpMyAdminはカラムの編集画面であっても後からカラムを追加できるので適当な数を入力しておけば良いだろう。

また、この記事では「カラム」という言い方で統一するが、開発者によっては「カラム」のことを「フィールド」と呼んだりもする。

視覚的には列である対して、行にあたる1データのことを「レコード」と呼ぶので覚えておこう。

さて、今回のゴールでも書いたが、この記事では例としてユーザー登録ができて、登録されたユーザーはメモを登録できるようなサービスのテーブル設計を行う。

ユーザーは自信の名前とメールアドレス、パスワード情報を持ち、メモにはタイトルと本文情報、制作日時情報が存在するとしよう。さらに、ユーザーは管理者が用意したカテゴリーをメモに適用することができるとしよう。

様々な設計が考えられるが、今回の例で必要なテーブルはおそらく以下の4つだ。

• ユーザー情報を保存する「user」
• メモ情報を保存する「memo」
• カテゴリー情報を保存する「category」
• メモ情報とカテゴリー情報の関連を保存する「memo_category_relation」

まずこの連載第一回目はuserテーブルを作成してみよう。テーブル名にuser、カラム数を5として「実行」を押すと、カラム情報入力欄が5行分表示された編集画面が現れる。

1行につき様々な入力欄が表示されるが、操作するのは「名前」「データ型」「長さ/値」「デフォルト値」「NULL」「インデックス」「AI」「コメント」の8つだ。

これら8つの項目を以下の5カラムに対して設定していく。

INT型とAI(オートインクリメント)、Primaryキー

まずはidカラムから作成しく。

その前に、このカラムが必要な理由である一意性について説明しよう。

データベースの基本として、各レコードは一意性を保つほうが良いというルールがある。

一意性とは何か。それは各カラムの情報で対象のレコードが一つに絞り込める性質のことである。

例えば、学校の生徒には出席番号とクラス、学年、入学時西暦の情報を持っているが、西暦何年時の何年何組の何番、と3つの情報を組み合わせれば対象となる生徒はただ一人に定まる。これが一意性を持っている状態である。

これにより、両親の再婚等の理由である生徒の名前が変わったときに、書類上のデータも合わせて変更するときは「何年何組の何番を○○という名前に変更する」という操作ができるのである。

もちろん現実の世界ではそんな周りくどい言い回しではなく、「もともと○○君だったのを□□君にする」といった思考で名前を変更するだろう。

しかし、名前は同姓同名の可能性がある。何百万というデータを処理するデータベースの定義に基づくなら、名前というカラムは確実に一意性を保つ情報にならないのである。

話をidカラムに戻そう。

先の例では、学生を一人に絞り込むのに4種類のカラム情報が必要だった。

データベースの用語で言い換えると、複合主キーによって一意性が保たれている状態である。

一意性を満たしている、という観点からは問題ないのが、生徒の情報を変更するときに毎回4種類の情報を用意し参照するのは面倒である。

代わりに、絶対被らない連番をふっておけば、その数字だけで個人が特定できる。これぞidカラムの役割である。

phpMyAdminの入力欄のうち、一番上の行を以下のように入力しよう。

データ型の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と入力しておこう。全角の１を入力しないように注意。

インデックスの設定だが、INDEXを選択した場合、既になにかしらのカラムでINDEXが設定されていると出現するモーダルウィンドウで「単一カラムのインデックスを作成」と「複雑なインデックスを作成」の両者が選択できるようになる。

今回は「複雑なインデックスを作成」を選択し、現れた「合成物」でmailを選択しよう。

これでmailカラムとactカラムを二つ纏めた複合インデックスが設定されることになる。

なぜこれを設定するのかというと、実際のサービス運用時、具体的に言えば新規ユーザー登録時に同じメールアドレスが登録されていないか調べる際に「有効なユーザーの中からメールアドレスが○○○であるユーザーを探す」という絞り込みが発生することが予想されるからだ。

インデックスを作成すると検索が早くなるというのは前述したが、実はMySQLには、データの検索を行うときに使用されるインデックスは一つだけという制約がある。

なので、mailカラムとactカラムそれぞれにインデックスを張るより二つを一緒にしたインデックスを一個作るほうが検索の高速化が見込めるというわけである。

ログイン認証の際には更にpasswordカラムも混ぜたインデックスが必要になるのではないか、という想像もできるが、有効(act=1)なユーザーに限ればあるメールアドレスとmailカラムが一致するデータは1個、もしくは0個なので、そのデータのpasswordが持つ値をアプリケーション側で比較するという手を使えば合理的と考えることもできる。これはケースバイケースなので実際のサービス要件によって考えてみて欲しい。

さて、ここまで入力し終えたら右下の「SQLのプレビュー」をクリックしてみよう。

そうすると以下のようなSQLが表示される。

  CREATE TABLE `test_user_memo`.`user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'AI' , 
    `name` VARCHAR(255) NOT NULL COMMENT 'ユーザー名' , 
    `mail` VARCHAR(255) NOT NULL COMMENT 'メールアドレス' , 
    `password` VARCHAR(64) NOT NULL COMMENT 'パスワード(sha256)' , 
    `act` BOOLEAN NOT NULL DEFAULT TRUE COMMENT '有効/無効' , 
    PRIMARY KEY (`id`), INDEX `login` (`mail`, `act`)
  ) ENGINE = InnoDB CHARSET=utf8mb4 COLLATE utf8mb4_general_ci COMMENT = 'ユーザーデータ';

今まで様々な情報を入力したが、その目的はこのSQL文を作成し、それをMySQLに実行させることでテーブルを作成することである。

今後開発を進める中で、アプリケーション側で動的にテーブルを作成したり、コマンドラインから直接データベースを操作する場合はこのようなSQLを直打ちすることで全く同じようにテーブルを作成できるので、慣れてきたらこの構文を勉強すると様々な場面で役に立つだろう。

「SQLのプレビュー」の横にある「保存する」をクリックすればこのSQLが実行され、晴れてテーブルが作成される。

まとめ

テーブルを一つ作るところまで進めてみたが、意外とあっけないと感じただろうか。それとも想像以上に複雑だったと感じただろうか。

当然だがデータベース設計はサービスの要件によってかなり変わるもので、最適な構造を設計するにはかなりの知識・経験を要する技術である。

自分もまだまだ修行中ではあるが、なかなか思い付きで勉強し辛い分野なので、この連載が読者の第一歩になってくれれば幸いである。

次回はリレーショナルデータベースの真骨頂であるテーブル結合のためのテーブル設計やTEXT型、木構造や中間テーブルについて説明しつつ残りの3テーブルを作成していくのでお楽しみに。