| [ホーム] -> [Aache + PHP + PostgreSQL 実験室] |
SQL は非常に便利な上、データを格納しておく専用のソフトウェアだけあって、多量のデータの扱いは非常に得意です。しかし、それも適切に設計し、適切にコーディングした場合です。簡単に、そこら辺の説明をしたいと思います。
まず、よく言われることですが、データベースの設計だけが良くても、プログラムの設計だけが良くてもダメです。とはいっても、実際は全く同時に設計が行えるわけではありません。多くの場合、データベースの設計が出来ない限り、プログラムの設計は途中までしかできないからです。
データベースの設計は大きく分けて次の二点があります。
物理的な構成とは、どのマシンで動かすか、からに始まり、データベースを格納するディレクトリをどこにするかといことまでです。現実的に、PostgreSQL はクラスタリング構成が出来ないので、一台のマシンで動かすことになります。こうなると、注意が必要な点は、データベースのディスク配置（データファイル、ログファイル）くらいです。RAID 構成などによって適切な配置は変わってきます。データファイルと、ログファイルを別々の物理的なディスクに配置できるのならば、そうするべきです。
内部オブジェクトは、テーブルをどう作るか、インデックスをどう張るか、などです。物の本を読めば、正規化に関する説明されているでしょう。是非、一度何か本を読んでみてください。実際の現場では、第三正規化くらいまでが普通です。第四、第五正規化は、これをしてしまうと開発がとても面倒になるので、現実的ではありません。一応以降で正規化と、そのメリットデメリットについて簡単に説明します。
正規化についての簡単な説明です。詳しくは本なりを読んでください。
商品の売り上げを格納しておくテーブルを考えてみましょう。正規化される前は次のようになります。
** 非正規形 ** -- 売り上げテーブル -- | 売上番号 | 売上日付 | 商品番号1 | 商品名1 | 分類名称1 | 単価1 | 数量1 | 合計金額 | ... | 総合計 | | ０００１ | ０７０２ | １１１１ | いす | 家具 | ２０ | ４ | ８０ | ... | １７０ | | ０００２ | ０７０３ | ２４５１ | ペン | 小物 | ５ | １０ | ５０ | ... | ９１ |
... には、商品番号から合計金額までが５回繰り返されているとしましょう。第一正規化では、この繰り返しを無くします。
** 第一正規形 ** -- 売り上げテーブル -- | 売上番号 | 売上日付 | 商品番号 | 商品名 | 分類名称 | 単価 | 数量 | 合計金額 | 総合計 | | ０００１ | ０７０２ | １１１１ | いす | 家具 | ２０ | ４ | ８０ | １７０ | | ０００１ | ０７０２ | １１２１ | つくえ | 家具 | ９０ | １ | ９０ | １７０ | | ０００２ | ０７０３ | ２４５１ | ペン | 小物 | ５ | １０ | ５０ | ９１ | | ０００２ | ０７０３ | ３６２９ | コップ | 食器 | ７ | ３ | ２１ | ９１ | | ０００２ | ０７０３ | １１１１ | いす | 家具 | ２０ | １ | ２０ | ９１ |
各行に複数のデータを持つのではなく、繰り返し毎に複数の行にするのがポイントです。
** 第二正規形 ** -- 売り上げテーブル -- | 売上番号 | 売上日付 | | ０００１ | ０７０２ | | ０００２ | ０７０３ | -- 売り上げ明細テーブル -- | 売上番号 | 商品番号 | 数量 | | ０００１ | １１１１ | ４ | | ０００１ | １１２１ | １ | | ０００２ | ２４５１ | １０ | | ０００２ | ３６２９ | ３ | | ０００２ | １１１１ | １ | -- 商品テーブル -- | 商品番号 | 商品名 | 分類名称 | 単価 | | １１１１ | いす | 家具 | ２０ | | １１２１ | つくえ | 家具 | ９０ | | ２４５１ | ペン | 小物 | ５ | | ３６２９ | コップ | 食器 | ７ |
第二正規化では、複数行で重複しているデータを別テーブルに分ける作業をします。一回の売上のデータは「売り上げテーブル」に、一回の売り上げで何を買ったかは「売り上げ明細テーブル」に格納します。商品に関しても別のテーブルにします。また、総合計と、合計金額が削られましたが、これは、売上数量と商品の単価から求められるからです。
第二正規化が終わると、プライマリーキーの設定が出来るようになります。売り上げテーブルであれば売上番号、売り上げ明細テーブルであれば売上番号と商品番号の組み合わせ、商品テーブルは商品番号がプライマリーキーにあたります。
** 第三正規形 ** -- 商品テーブル -- | 商品番号 | 商品名 | 分類番号 | 単価 | | １１１１ | いす | １ | ２０ | | １１２１ | つくえ | １ | ９０ | | ２４５１ | ペン | ２ | ５ | | ３６２９ | コップ | ２ | ７ | -- 商品分類テーブル -- | 分類番号 | 分類名称 | | １ | 家具 | | ２ | 小物 | | ３ | 食器 |
売り上げテーブルと売り上げ詳細テーブルは変わらないので、省略しました。第三正規化では、重複してるデータに対しコードを振って別のテーブルに分ける作業をします。
これで各テーブルが非常にすっきりしました。でも、場合によってはここまでしてしまうのがベストというわけではありません。各段階でどういうメリットデメリットが発生したか説明していきましょう。
第一正規化のメリットは、一度に６個以上の買い物をした人にも対応できる点です。非正規形だと、その行にある繰り返しのカラム分しか格納できませんでした。これによるデメリットはまずありません。
第二正規化でテーブルが複数に分かれました。複数に分かれると言うことは、データを取得するときに組み合わせ直す必要が生じると言うことです。しかし、リレーショナルデータベースを使っている場合、これは当然の作業です。そのためにインデックスがあったり、テーブルを結合してデータを取得するする SQL があるのですから。従って、テーブルを分けることによるデメリットはありません。逆に、重複データを無くしたことで、データベースに格納するデータ量が減ります（大抵の場合）。これは非常に重要で、データ量が少ない方が速い という単純なメリットがあります。
また、第二正規化によりデータの重複が無くなったおかげで、データの変更も楽になっています。例えば、商品名の入力をミスして誤字があったとします。そしたら商品テーブルの該当データだけ変更すればいいことになります。
デメリットは実はいくつかあるのですが、まず売上明細テーブルから単価が無くなってしまった弊害として、商品テーブルの単価を変更することが出来ない 点があげられます。商品の単価が変わることは当然あるでしょう。しかし商品テーブルの単価を変えてしまうと、過去の売上金額が変わってしまいます。もちろん、これを回避する方法もあります。例えば、商品の履歴を格納しておくテーブルを用意し、売上日時点の商品の価格を調べれば正しい売上金額が求まります。
-- 商品履歴テーブル -- | 商品番号 | 変更日付 | 単価 | | １１１１ | ０１０４ | ２０ | | １１１１ | ０７１６ | １８ |
こんなテーブルがあればいいのでしょうか。確かに理論的には、売上日時点の単価を求めることが出来ますね。でもよく考えてください。結構面倒ではないですか？ 売上日が「０７０２」なら単価「２０」が正しいですが、これを SQL 文だけで求めるとなるとやっかいです。それだったら単純に売上明細テーブルに単価を持った方が良くないですか？ すでに売り上げてしまった単価を変更することもないでしょうし。
また、総合計が無いのも気になります。もちろん計算すれば求められます。そのために SQL には集合関数があります。しかしよく考えてください。売上番号「０００１」の総合計を求めるにはどうしたらいいかを。売上テーブルから売上明細テーブルを連結し、そこからさらに商品テーブルを結合し、その単価と売上明細テーブルの数量とかけて、それ全体を足すのです。SQL 文で書くと次のようになります。
select 売上テーブル.売上番号,
売上テーブル.売上日付,
sum(単価 * 数量) as 総合計
from 売上テーブル
join 売上明細テーブル
on (売上テーブル.売上番号 = 売上明細テーブル.売上番号)
join 商品テーブル
on (売上明細テーブル.商品番号 = 商品テーブル.商品番号)
where 売上テーブル.売上番号 = '０００１'
group by 売上テーブル.売上番号, 売上テーブル.売上日付
いや、これくらいの SQL は簡単な部類ですけどね。それでも、この SQL 文を何回も実行する必要があるならば、売上テーブルに総合計を持ってもいいと思いますよ。
第三正規化については特に何もないと思います。大抵しても問題ないものです。でも、まれに、し忘れます・・・。
パフォーマンスを考えると、Web サーバと、データベースサーバは別のマシンで動かすのがお勧めです。しかしここで考えないといけないのが、処理をどちらで行うか、です。
これは別の表現にすると、SQL で処理をするのか、PHP で処理をするのか、です。複雑な SQL を作ってデータベースサーバで処理するもよし、SQL では基本となる情報のみを持ってきて、PHP でデータを加工させるのか。
はっきり言って、どちらがいいかは、作って試してみるまで分かりません。一応選択肢をあげておきます。
それぞれメリットデメリットがあります。１は、PHP でデータを加工するタイプですが、元になるデータを大量に データベースから持ってくるとなると、ネットワークがネックになる可能性もあります。
２は、逆にデータベースサーバに負担をかける方法です。Web サーバでは、データベースにアクセスしない普通のページも処理しないといけないので、これも一つの方法です。データベースサーバに負担がかかっても、ただの HTML ページには問題なくアクセスできます。個人的には結構好きな方法です。SQL が得意な人向きかな。
３は、２の応用で、SQL 文だけではなく、ユーザ定義関数（ストアドプロシージャ）を作成してしまう方法です。この処理も当然データベースサーバ上で行われるわけですから、データベースサーバに負担がかかります。複雑な SQL 文を作らなくて良くなるかもしれないので、プログラマのレベルに左右されにくくなります。でもまれに、説明しても使ってくれない人たちもいます・・・。
４は、それほど負荷のかからない時間帯にデータを加工しておこうというものです。
例えばトップページにお勧め商品を表示したいとします。どの商品をお勧めにするか、その商品はいくらか、といった情報がデータベースに格納されていたとします。だから、トップページを PHP で作成しました。でもこのページ、ユーザがアクセスするたびにデータベースを参照する必要がありますか？ 日に一度夜間にでもトップページを生成しておくのでは問題がありますか？ 生成しておけば、アクセスがある度に PHP が実行されデータベースにアクセスする必要が無くなりますよ。
あるいは、各商品の説明ページがあったとして、その商品の情報をデータベースから取ってくるとします。でも毎回商品情報を見に行くのではなく、全商品のページをあらかじめ作っておくことも可能です（ディスク単価が安い今日では）。
あるいは、先で説明した合計金額とかは、夜間に集計しておくという方法もあります。本当にリアルタイムでデータベースを参照する必要があるのかよく考えてください。
５も本気です。本当にユーザが必要としているか確認してください。「ユーザ != 作る人」の場合、「こんな機能があったら便利かも」と思っただけで作ってくれと言ったりします。その機能を実現するのがどれほど困難だとしても、分からないのだから仕方ありません。もっと簡単に作れる別のに多機能を提案してみてはどうでしょう。ユーザの言ったものをそのまま作るのがシステム開発ではありませんよ。特に複雑な機能はバグが多いものです。簡単に出来る方法があるのなら、それを採用するに超したことはありません。
プログラムを設計する上での注意点をあげてみます。
うーん、あまり思いつかないですね。
１は何度も言っていることです。機能が多いとバグが増えます。どちらかというと、指数対数的に増えます。
２も上と同じ理由で、バグを少なくするためです。
３は、「ちょっと必要だから作るけど、後でどうこうするつもりはないよ」とか言われても、素直に適当に作り込んではいけません。後で「ここ直してよ」「こういう機能欲しいのだけど」って必ず言われます。そのためには、後から変更しやすいように作る必要があります。定数を利用する、機能を切り分けユーザ定義関数を作る（構造化）、オブジェクト指向な設計にする、などです。ちょっとでも共通化しておくと、他のアプリケーションを作ったときにも応用が利きます。「一部を切り取って、他にそのまま使える」を目標にしてください。「一部を切り取って、変更を加えれば他に使えるかもしれない」だと、自分以外は使えないと言うことを認識してください。
４は、３と関係しますが、「一部を切り取って、他にそのまま使える」を実現するための手法です。オブジェクト指向のデザインをしてください。末端のプログラム（個々のプログラム固有の機能）までオブジェクト化しろとはいいません（私も現実的に、すべてをオブジェクト化するのは疑問です）。しかし共通部品はオブジェクト化しておいて損はありません。「継承」の機能があるだけでどれだけ流用が楽なことか。
５は複数の人で開発するには必ず必要です。自分一人で作り、記憶力がいいのならいらないかもしれません。でも、思ったこと、望んでいることはかならずメモしておきましょう。それをきちんと清書する必要があるかどうかは、ケースバイケースですが。