こちらは、ジーズアカデミー Advent Calendar 2022のカレンダー2、21日目の記事です。
はじめに
初めましての方、お久し振りの方、こんばんは。
福岡DEV7卒 西と申します。（Qiita 初投稿……）
今日は、在学中にやろっかなと言っていて結局やる余裕がなかった、データベース（テーブル）設計について、お話しします。
ジーズ入学までプログラミングにあまり親しむ機会のなかった方々、テーブル設計に苦しんでいないでしょうか。
慣れないとなかなかピンと来ないテーブル設計の思想ですが、慣れると一生ものです。
データベースを使わないシステムは、基本ないのですから。
※ 表示系（HTML/CSS など）は除きます。
説明は主に、リレーショナルデータベース（RDB）です。
初学者の方にイメージしていただきやすいよう、ざっくりした説明になりますので、「厳密には違う」とかいうコメントはご遠慮願います。クリスマスらしく、広いお心でお願いいたします。
さて。
授業では確か、Firebase から始めたと思います。LAB の方はどんな進み方かは存じませんが、同じであろうと思って進めます。
Firebase では、いくつかの項目（アイテム名、作成時間など）を作り、あたかも欄の印刷されたメモ用紙に中身を書き込んで貼っていったような、レコードの増やし方をしていったと思います。
このやり方は、NoSQL型データベースといい、直感的に使えるといえば使えますが、RDB に慣れた人から見ると、やや使いづらさを感じます。
授業が進み、PHPを習い始めた頃、SQL を教わったと思います。
「select * from ～」というアレですね。
SQL は、大文字小文字は判別しません。現場での決め事はあるかもしれませんが、統一してようが混ざってようが、動きます。こだわりの方もいらっしゃるのですが、気にしないで大丈夫です。その現場でとかその方と仕事をするときは気をつけましょう。
では、問題です。テーブル：Flower から、バラが売れた行を出してみましょう。
＜図１＞
SELECT count(*) FROM Flower WHERE 花ID = "A1";
３行ですね。「WHERE 花名 = "バラ"」じゃないところが、ちょっとしたポイントです。
RDB は縦横２次元の表で表します。
横１列で１行、縦は縦１行で１つの項目を表します。
この、横１列を「レコード（＝行）」、縦の項目を「カラム（＝列）」といいます。
図１でいうと、青い１行がカラムを表し、下の６行がレコードを表します。
設計の話
さて、この例題を作るとき、テーブルはどう作ったでしょうか。
やり方は２通りあり、どちらでも良くて、むしろ作りながら形にしていくものです。
その前に、テーブル構造の話をしましょう。
「ファクトテーブル」と呼ばれる『処理のベース』になるものがあり、その周りに「ディメンションテーブル」と呼ばれる『中身のベース』があり、それらを図にすると星のようだから「スタースキーマ」と呼ばれます。
「ファクトテーブル」は「トラン（テーブル）」、「ディメンションテーブル」は「マスタ（テーブル）」ということが多いと思うので、以下それでお話しします。
「トランの周りにマスタが」とお話ししましたが、もちろんトランとトランが繋がってたりマスタがなかったりしてＯＫです。
＜図２＞
トランとは、トランザクションの略で、プログラムでの処理の単位を指します。
プログラムで処理したい、１かたまりのことです。どんどん積み上がっていきます。
「何日に誰が何をいくつ買っていくらになったか」というような１レコードのことです。
図２でいうと、青いところですね。
マスタとは、一覧表、設定値のことです。マスタはあまり、更新するものではありません。
図２でいうと、緑のところですね。
先ほどの「やり方は２通り」というのは、トランから作るか、マスタから作るかということです。
まっさらから作るときは、トランからがいいでしょう。そこから、必要なマスタを洗い出して、マスタの整備をしていく感じでしょうか。
もしくは、ものが先にあるのだとしたらそれを整理してマスタを作り、組み合わせてトランを作るのもありです。
これは、プログラムで必要なデータを集めてテーブルを作るか、データからプログラムを作るかに似ています。
どちらにせよ、最初は粘土細工を行うように、少しずつ足していって形にします。
RDB は『積み木』、箱の山と思うと、SQL もいい感じにイメージできると思います。
箱を積み上げ、箱を選んで引っこ抜いて任意に並べてまた戻す。そんな感じです。
正規化の話
ＤＢを勉強すると、まず『正規化』の話を聞くと思います。
第１正規形、第２正規形、第３正規形、更新時異状（異常に非ず）。
これだけ抑えておけば大丈夫です。
図１ と図２ の違いを見てください。
図１ は第１正規形、図２ のトランは第２正規形、マスタは第３正規形です。（ちょっと自信ない）
試験でない限り、この辺の厳密な分け方は不要です。
トランに関して、正規化しすぎることはかえって処理に負担を掛けるので、第１正規形が出来ていればＯＫです。
よくあるのが、名称が入ってたり、合計があったりするケースです。
図１ でもそうですね。
名称はマスタに置くのがいいですが、テーブル結合が面倒ならそのままくっつけてもいいです。
但し、後で変更があった時に『更新時異状』を起こすので、出来るだけ名称はマスタだけに置きましょう。
合計は『導出項目』というものになるので、処理時に算出して出すようにするものですが、いちいち計算するのもマシンへの負担になりますので、これはよくくっついたままになってたりします。
また、税率が変わったり何かの要因で、処理時に算出し直すと当時の合計と変わってることがあります。
なので、合計はそのままくっついててもＯＫです。
単価も、EXCEL やアプリなどでは自動で入ったほうが便利ですが、トランはいわば記録のようなものなので、合計と同様にレコードに入っていたほうがいいこともあります。
導出項目に関して、「昔に比べてマシンスペックもネットワークも改善してるから考えなくていい」などという人がいますが、大規模業務を扱うつもりなら、気を使ったほうがいいと思います。
0.0001 秒の遅延を、10億スループットでするとしたら、どのくらいの時間になるか。
計算してみると、結構侮れないんじゃないでしょうか。
ではやってみましょう
以下の条件から、テーブル設計をしてみましょう。
＜条件＞
花屋の売り上げプログラムを作ることを依頼されたとしましょう。
「売れた日と、花の種類を色別に、出来れば買ったお客さんまで記録したいな」
初めは、図３ のようで構いません。
思いつくまま並べてみてください。
＜図３＞
これは、非正規形です。
花名～合計 が２回出てきているのが特徴で、これが「繰り返しがある」という状態です。
ここから、２回出てきているのを１レコードごとに分解して、枝番を付けたのが図１ です。
＜もう１度 図１＞
図３ から図１ にするところで、名称を ID にしていますね。
「『WHERE 花名 = "バラ"』じゃないところが、ちょっとしたポイント」とお伝えしましたが、名称で WHERE をするのが悪いわけではありません。
それはよくやるところです。
ただ、設計として、主キーなどのキー項目には、「数字やアルファベット」が適しているからです。
名称をキーにすることは、あまりありません。
データベースの世界で名称などの何らかの「文字列」は、人間が見やすいための補助的なものという感覚だからです。
上から順の通し番号でもいい、頭文字でもいい。
図３ から図１ 、そこから図２ が作れるか、やってみてください。
まずはメモを書いてみましょう。そのプログラムには、どんなテーブルが要るのか。
テーブルには、どんな項目が要るのか。
「売れた日と、花の種類を色別に、出来れば買ったお客さんまで記録したいな」なら、
売った日付、花の種類、花の色、お客さん…… あと、単価、数量、合計が要るかな……
といった風に。
何はともあれ、まずは項目（＝カラムにするもの）を書き出すこと。
マスタからでもトランからでもいい、組み合わせたり削ったり、整えるのは後でいいのです。
主キーの話
RDB には、【レコードはユニークでなければならない】という鉄則があります。
これは、絶対です。
同じ内容のものは、登録できません。
ユニークとは、一意であること。面白いという意味ではありません。つまり、各行はすべて、何らかの情報により見分けられなければならないのです。でなければ、弾かれる（＝データベースに登録を拒否される）のです。
主キーとは、レコードをユニークにする情報のことです。
〇〇ID などのコードの項目が主ですが、項目Ａ と項目Ｂ と項目Ｃで主キーとすることもできます。（複合キーといいます）
自動インデックスの「ID」が先頭に付くタイプのデータベースを使うことがあるでしょうが、その「ID」を主キーにするのはお勧めしません。
自動インデックスの「ID」でレコードはユニークになるけれど、お手軽機能と思ってください。
プログラム側がレコードを使うとき、「この行が欲しい」となったときに困ることがあります。「この行だけが欲しいんだけど、ID 以外に違うとこがないから、他の行も採れちゃう」といったことになります。
これは、設計時の意識で、防げる問題です。
かといって、プログラム中で自動インデックスの「ID」を指定するのは、お勧めしません。自動インデックスの「ID」の値は、不確かなものなのです。
更新元のレコードを消して、コピーして変更したレコードを挿入した場合、「ID」は違うものになるはずです。
※ UPDATE ではなく、DELETE-INSERT をするとそうなるはず。
似たものに「通し番号」があります。会員番号など、通し番号を自動採番で使いたいこともあるでしょう。それはレコードを一意に区別するキーとしてＯＫです。付与時のみ自動で、あとは一切更新しない情報であれば。
システムではなく、プログラムの領域にあるため、そういう設定にしてしまっていない限り、勝手に変わってしまうことはありません。
レコードを並べようとして、登録順に自動付与されるものをソート（並べ替え）したところで、登録順に並ぶだけです。
登録順に並んでくれればいいんだと思うかもしれませんが、時間が経つにつれてデータが積み上がっていく業務用データで、登録順など当てにするものではないのです。
たまたま、うまく並んでいるように見えるだけです。
そもそも、RDB の特徴として、【レコードの並びは保証しない】のです。
アテにしてはいけません。
要するに、レコードをユニークにする意識が、その設計に入っているかということです。
図２ の場合、「枝番」が入っています。「販売日＋顧客ID＋枝番」で、レコードがユニークとなっていることに注目してみてください。
プログラム中できちんと意味のある項目を、キーに選ぶことを、強くお勧めします。
表記ゆれの話
最後に、表記ゆれの話をしましょう。
「『WHERE 花名 = "バラ"』じゃないところが、ちょっとしたポイント」が、また戻ってきます。
表記ゆれとは、ざっくりいうと、１つの文字列に対して違う書き方をしてくることです。
キーに名称などの文字列を使わない理由に、「表記ゆれがあるから」というものがあります。
あと、「日本語はとても文字化けがしやすい言語」もあるんじゃないかと思っています。
この、表記ゆれがあるがゆえに、「『更新時異状』に気をつけろ」という話になります。
※ あと、単純にレコードが多いときはやってられっかになります。
余談ですが、ソートをしたときに、何で並ぶか。
これは、文字コード順に並んでるのです。
ということは、文字コードが違うと、並びが違うことがあります。
数字やアルファベットの場合、あまり違うことがないので、そちらを推奨されているのです。
※ 全くないとはいいません。1,2,～10 が 1,10, 2,3～になってることもあります。
長くなったので、そろそろさっと行きましょうか。
図４ を見てください。
ある衣料品の売り上げを示すトランの行に、「S01:あったか 靴下」があったとします。
各店舗からの売り上げデータを集約したデータベースで、「S01:あったか 靴下」がいくつ売れたか見たいと思います。
ところが、各店舗からは、商品コード「S01」と商品名「あったか 靴下」を入力するシステムだったとします。
＜図４＞
さて、「S01:あったか 靴下」は、何行でしょう。
商品名が変わった時に、一括変換できるなんて思わないほうがいい、ということが、腑に落ちていただけましたでしょうか。
他人は、自分の想定を軽く超えてくるのです。
商品コード なんか覚えてられない、そりゃそうで、そこはシステムのお仕事です。
ですが通販で、品名で検索したとしても、注文時は番号や記号で入力しませんか。
カタログ片手に、掲載番号とかで入力しますよね。（コピペですとか言わんでいいのよ）
余裕があれば、「フールプルーフ」という言葉を覚えておくといいかもです。
「フールプルーフ」とは何かというと、システムの設計で出てくる言葉で、「そもそもユーザがエラー出せない設計」とでもいいましょうか。簡単なところでは、「プルダウンとかラジオボタンとかにしとけば、それ以外の回答は来ない」というものです。
類似に、「フェイルセーフ」「フェイルソフト」「フェイルオーバー」「フォールトトレランス」・・・などなどありますが、まあまあ興味がありましたら、調べてみてください。
IPAの情報処理技術者試験のITパスポートや基本情報などには、よく出てくる問題です。
「何かあっても安全に配慮する『フェイルセーフ』、トラブルがあった時に機能を最小限に落としてでも継続する『フェイルソフト』」などは、言葉は覚えてなくていいから、プロダクトを作るときに、考えておいた方がいいかもしれません。
締めの話
今回は、最低限の単機能についてお話ししました。
RDB の真骨頂は、テーブルを繋げていく処にあるのですが、その話はまたいずれ機会があれば。
リレーション（テーブルを繋げる）の話は、なかなか楽しいです。
プログラムは、「いかに抽象化、汎化できるか」です。
やりたいことをコードに落とし込むときに、どういう書き方をすれば、思う処理になってくれるか。
どういう IF文を作れば、思うように処理が分かれてくれるか。
共通する機能は極力まとめて、違うところだけを別に作る。
最初は１つ１つ別に作ってもいい。後から整理して、全然いい。
テーブル設計も同じことです。
長文失礼しました。
皆様のなにか、お役に立てれば幸いです。
ご質問や、「ここをもうちょっと詳しく」などがありましたらお寄せください。
機会がありましたら、またお話しすることもあるかもしれません。
では、よいクリスマスと、年末年始をお過ごしください。