MySQLの構造について

質問

応援しているサッカーチームの試合データをMySQLを利用しテーブルに格納したいと考えています。
そこでMySQLやPHPに詳しい方にお聞きしたいことがあります。

例えばA・Ｂ・Ｃと3チームあるとしたら、A対B、A対Cの試合データは格納しますが、B対Cのデータは不要です。あくまでもAチームに関するデータのみを格納し、私が開設しているサイトで閲覧者に検索フォームから自由な形でデータを参照できるようにしたいです。

参考までに格納したい試合のデータは下記のようなデータです。
http://www.j-league.or.jp/result/2014/j2/030201.html

現在、既に一定のデータはテーブルに格納しています。現在のテーブルの構造です。
https://www.dropbox.com/s/z2qc2zff2fn8m8f/gamedb.pdf?dl=0

スタメンの選手やベンチの選手、交代選手、警告をもらった選手、得点者などはテーブルに格納していません。これらのデータは試合ごとに大きく数が異なるのでどのようにして格納すれば良いのか分からなかったので。

現在は格納していないこれらのデータを格納することで下記のような条件を指定しても対象データが羅列されるようにしたいです。
・○○選手がイエローカードをもらった試合。
・点を取られている選手ランキング。□□選手(※対戦相手の選手)には一番点を取られているなど。
・○○選手がスタメンだった試合。
・○○選手が途中出場した試合。
・○○選手はこれまで△試合に▲分出場している。
・□分～□分の間の得点は何点。逆に失点は何点など。
などなどです。

私が現在思いついている構造ですが、新しくテーブルを複数作成する。こういったケースではそれぞれのテーブルに共通のIDを振る必要があるとのことですが、これは日付（開催日）で代用しようと思っています。同じ日に2試合は絶対にないので。
作成するテーブルは下記のようにします。
・ホームチームスタメンテーブル
・アウェーチームスタメンテーブル
・ホームチームの警告をもらった選手テーブル
・アウェーチームの警告をもらった選手テーブル
・ホームチーム得点者テーブル
・アウェーチーム得点者テーブル
.....などなど。

こういったテーブルを作成し、それぞれID(開催日)と一定数のカラムを予め用意する。得点者のテーブルでしたら1試合で一方のチームが10点を取ることは限りなく0に近いのでとりあえず10用意する。同じように得点した時間も10用意。

datadating(開催日) | goal_1_min(得点時間) ｜goal_1（得点者） ｜ goal2_min(得点時間) | goal2(得点者) |..........

このように格納していけば思い通りの結果が得られるようになるのかな？と思っていますが、果たしてこのようなテーブルで作成していき、後々サイト側の検索フォームから指定した条件で無事に結果を得られるか不安です。

ちょっとしたことでも良いので、詳しい方の意見をお聞きしたいです。
どうぞよろしくお願いします。
長文失礼しました。

回答No.2

mitoneko · ベストアンサー

まず、やっていいこと、いけないこと。から。

＞これは日付（開催日）で代用しようと思っています。同じ日に2試合は「絶対に」ないので。
　この部分は、極めて正しい対応です。
　でも、
＞1試合で一方のチームが10点を取ることは「限りなく0に近いので」とりあえず10用意する。
　これは、絶対にやってはいけません。こういう事をやった時に限って、１００年に一度しか無い、11対１５なんてスコアに巡り会うことになります。（迷信のようだというなかれ。マーフィーの法則として知られる法則群の一つです。限りなく０％に近い確率で起こることは、何れ、必ず、そして最悪のタイミングで発生すると言います。）

　さて、全部をテーブル化しようとすると、スペースにも限りが有るので、考え方の一つを紹介しておきます。
　まず、属性がいろいろと出ていますが、その属性が、「何に依存するものなのか？」を考えます。言い換えると、何が決まれば、その属性を特定することが出来るかです。
　例えば、ホームチームの１ゲームの得点で有れば、開催日と開催場所が決まれば、必ず特定できますね。（もし、有るチームの試合に対して「だけ」のデータであれば、開催場所は不要です。）
　この組み合わせで特定できる属性の他の例は、ホーム・アウェイの各チームの監督名とか、主審名とか、１ゲーム全体に関わる属性群となります。

　さて、有るゴールの得点者は、何に依存しているでしょう？開催日と開催場所と、発生時間（試合開始からのタイム）となります。

　さて、こうやって、全部の属性が、何に依存しているかを整理してみましょう。
　こうやって作業していった時に、「開催日と開催場所」とか、「開催日と開催場所と、発生時間」とかいった有る事象を特定するためのキーになる物を主キーとします。
　そして、主キーに依存しているものを全て、テーブルの項目としてテーブル内に格納します。
　（こうすれば、ゴール１・ゴール２・・・・なんて項目にはならないはずです。ある特定の時間に発生するゴールは絶対に１つだからです。）

　考え方によっては、「開催日と開催場所、発生時間」の組で特定できるものが他にもあります。警告や反則の発生です。これも、ゴールと同じテーブルに入れることが出来ます。ただし、この場合は、この時間に何が起こったのかを記録するフィールドがひとつ必要になります。その代わり、ゴールも警告も反則もとにかく、ゲーム中に発生したイベントは全てこのテーブルに格納できます。

　ここで注意するのは、どちらのチームで発生したのか（どちらのチームの得点になったか）は、記録の必要はありません。選手名が解れば、各チームの選手リストを登録してあるテーブルから調べられますからね。

　むやみにテーブルを増やす必要はありませんが、テーブルを分けるのを怖がってはいけません。事象を特定するためのキーが異なる時は、あっさりとテーブルを分けましょう。

　この作業を、「正規化」と言います。（かなり作業を簡略化しています。ここまでを正しくやれば、第２正規化と言われる部分までの作業が終わるはずです。「ここで注意するのは」の項が見落としなく配慮出来れば、第３正規化まで終わるはずです。普通は、ここまで出来れば、よしとされます。）
　データベースのテーブル設計をする時には、避けて通れない作業ですから、根気よくやってみてください。この作業が正しく出来れば、次にデータベースを使うアプリケーションを作るのが、ぐっと楽になります。
　頑張ってくださいね。

お礼

mitonekoさん

回答ありがとうございます。
また、御礼が遅くなってしまい申し訳ありません。

正規化について丁寧に説明して頂きありがとうございます。
やはり素人が簡単に出来ることではないですね。
ここまで何となくで進めてきて、理想の結果を出すことができていたのでろくに勉強もしていませんでしたが。
もう少しデータベースの設計について勉強してみます。

ありがとうございました。

回答No.1

maiko0318

データベースの設計理論は重複データの廃棄と配列の廃止です。
よって、１レコード上に１０個持つことはご法度です。

datadating(開催日) | goal_1_min(得点時間) ｜goal_1（得点者）

というテーブルを作りましょう。

・「限りなく0に近い」・・・あったら破綻しますよ。
・今日、田中くんの得点は？という時に縦並びのほうが検索しやすい。
・配列定義できないものを検索すると、１０個を探しまわることになる。

お礼

maiko0318さん

回答ありがとうございます。
また、御礼が遅くなってしまい申し訳ありません。

テーブルの構造についてご教示頂きありがとうございます。
ただやはり私のやり方だとホーム側とアウェー側でテーブルで分けている為、検索する時にどちらに所属している選手かを判断するのが難しそうですね。いちいち選手名で照会するわけにはいきませんし。
自チームと相手チームの得点者というテーブルで分ける必要があるかもしれません。
再度設計について考えてみます。

大変勉強になりました。
ありがとうございました。