QA@IT

カラム数が多すぎるテーブルの1対1関連での分割方法

7739 PV

お世話になります。

カラム数が200を超えるテーブルを複数扱う案件を受け持ちました。
あまりにも一つのテーブルにカラムが多すぎて、情報の場所やカラム名を見つけるのが大変なため、テーブルを分けたいと考えています。

実際のテーブル情報などは載せれないので似た例としてゲームの情報を扱うテーブルがあったとします。
今回は簡単ですが、このgenreについてもそれぞれたくさんの情報があると50を超えるカラムがあると考えてください。
__________2016-04-20_23.50.28.png

私が考える構成は以下のようなものです。
__________2016-04-20_23.30.53.png
game_information_genres、game_information_makersそれぞれにも50を超えるカラムがあるとします。

以下のようにgame_informationsテーブルでまとめなくても良いとは思うのですが、game_information_genres、game_information_makersのデータを一度にまとめて取得したいと言った時に簡単に取得できるので中間テーブル?としてgame_informationsテーブルも設置しました。
__________2016-04-20_23.54.22.png

この構成(2つ目の画像)は良い設計と言えるでしょうか?

私はこの構成だと以下の問題を持つと考えています。

  • テーブル名が長くなりすぎる
  • それに伴って、関連キーのカラム名が長くなりすぎる
  • SQL実行1回のコストが高くなる

しかし、メリットとして

  • 情報が視覚的に分かりやすくなる
  • 情報取得コードがシンプルになる(ActiveRecord)
  • 構成の変更がしやすい もあげれるのではないかと考えています。

非常にカラム数が多くカオスで、今後の保守・開発がスムーズに進むようにもっとも望ましい形にしたいと考えています。
皆様のお力を貸していただきたいです。
どうぞよろしくお願い致します。

回答

実際のデータはわかりませんのでそれを抜きにして提示された情報で感じた印象ですが、
game_informationsテーブルの役割がよくわかりません。

gamesにはゲーム固有の情報の一部(メーカーやジャンルにまつわる情報以外)が入るのでしょうか。gamesのidは、game_informationsのgame_idと紐づくわけですね。

game_informationsはgame_idを持たないといけないので、仮に同ジャンルを同じメーカーから発売していてもgame_idの数より少なくなることはできません。これはこのテーブルがあくまでゲーム固有の情報の一部を垂直分割しただけのテーブルなのでいいということなのでしょう。

そしてgame_information_makersはgame_information_idを持つのでgame_information_idより少なくなることはできません。つまりはgamesと少なくとも同じ件数になるわけですね、分割前を考えれば同じ件数ということでしょうか。

ということは、game_information_makers.idとはgames.idの別称ともいえるわけでgame_information_idではなくgames_idを持っても同じことになるような気がします。
game_informationsにどういう情報を持たせるのかにもよってくるのですが

  1. game_informationsにはgamesとそれ以外のテーブルを紐づけるためのキー情報しか持たない
    この場合はテーブルが増えるだけでgame_informationsは分割に寄与していません。
    なぜなら、game_informations.idとgame_informations.game_idが同じ値でも成り立ってしまうからです。

  2. game_informationsはキー以外に実データも含む
    この場合を突き詰めていくとgamesテーブルはidだけでよくなります。idしか持たないテーブルが有用かどうかは説明するまでもないと思います。

なので、gamesとgame_informationsのいずれかは不要な表ではないかと感じました。

ここで、3番目の画像を見てみると、gamesはジャンルとメーカーを持っていますね、これはジャンルとメーカーのキーを持っていてそれをつかってジャンルやメーカーの情報を取得しにいく単純な正規化の話でしょう。そしてジャンルやメーカーはゲームのidに紐づく情報を持っていません。

もう一度 2番目の画像を見てみるとジャンルやメーカーがゲームに紐づくための情報を持っていることがわかります。

3番目の画像で

games --> game_information_makers

という探索方向だったものが2番目の画像では

games <-- game_informations  <-- game_information_makers

という探索方向に変わっているということです。。

新規に設計した場合ならば、メーカーよりもゲームの方が多いと思いますので、主キーが重複してしまうことになるでしょう。
と、いうことで2番目の設計はよくないと思います。

たぶん、

<初期状態>
Table: games 
  Fields: game-id,   name, release-date, 
          maker-name, maker-country,
          genre-name, genre-kana

<正規化する>
Table: games 
  Fields: game-id, name, release-date, 
          maker-id, genre-id

Table: makers 
  Fields: maker-id, maker-name, maker-country

Table: genres
  Fields: genre-id, genre-name, genre-kana

※ この例では列数が少ないからわからないが、元の列の数が何十倍にもなると
   この段階ではgamesがidだらけでgameの情報を探すのが大変だったり
   idなんだか日付なんだかもパッと見でわからない状態で不満が残ったとする。

<中間テーブル作る>
Table: games 
  Fields: game-id, name, release-date

Table: games-ext-info
  Fields: game-id, maker-id, genre-id

Table: makers 
  Fields: maker-id, maker-name, maker-country

Table: genres
  Fields: genre-id, genre-name, genre-kana

というのと、

<初期状態>
Table: games 
  Fields: game-id,   name, release-date, 
          maker-name, maker-country,
          genre-name, genre-kana

<垂直分割する>
Table: games 
  Fields: game-id, name, release-date

Table: games-makerpart
  Fields: game-id, maker-name, maker-country

Table: games-genrepart
  Fields: game-id, genre-name, genre-kana

(makerやgenreが正規化できたとして、中間テーブル作るとさっきと同じものができると思います)

がごっちゃになったんじゃないでしょうか。

前置き(というか重箱の隅というのか)がだいぶ長くなってしまったんですが、
現状で分割することばかりに意識がとられていて概念設計(データベースを抜きにして、データにはどういう実体が存在しているのか、どういう関連なのかの整理)や論理設計が不十分な状態で物理設計にはいっているような印象があります。(やっていたらごめんなさい)

この手の状況はメインフレームのデータをRDBに落とし込むときによく見かける気がしますが、この手のデータで気になるのはデータの質といいますか正規化が可能な状態にあるかですね。

たとえばユーザーの住所情報で言えば、郵便番号がキーとなりえて、都道府県、市区町村が選択された値が基本であれば、ユーザーによるばらつきがない個所はマスタデータなどに分離して一部のデータはキーで管理することができます。
しかし郵便番号、都道府県、市区町村の項目はわかれてはいるが入力者が思うがままに入力可能だったり、「xx年より前はカナしかなくxx年より後は漢字しかない(そして古いデータのメンテもされない)」といったばらつきがあれば同じ件数のテーブルに分ける(垂直に分割する)しかできません。この場合は中間テーブルを作ったところで基本的に分割前のキーを複数持つだけなのでその意味合いは薄くなるように思います。

複数の1対1の関係を1つの中間テーブルに閉じ込める場合(私が書いたTable: games-ext-infoの様な状態)ですが、仮に 1つのゲームを複数の会社が共同で作るから複数登録したいと言われた場合にどうするかですね。
「構成の変更がしやすい」には反するかと思います。
一部の情報だけ選択したい場合に不要なキー情報がたくさんついてくるのも気になりますね。中間テーブルがシンプルでない(複数の関係情報を持つ)のでActiveRecord(パターン?Rubyの方?)に頼った場合はコストが心配になります。
すべてのケースに対応するのは不可能ですし、仮定の話ですが様々なことを予想しておくのは大事だと思います(実装に盛り込む必要はないですが)。

「情報が視覚的に分かりやすくなる」は「誰」が「どんなときに」分かりやすいのかまで考えるといいと思います。

求めていた回答とはちがうかもしれませんが長々と失礼しました。

編集 履歴 (0)
  • @flied_onion さん、丁寧なご回答ありがとうございます。画像を使いたいため、回答として返信差し上げましたのでご回答いただけたら幸いです。よろしくお願いいたします。 -

返信が遅くなりすみません。

タグはdatabaseですが、内容から見るに DBの設計というよりはORMフレームワークを前提にしたモデル設計の話の様なので その方向で回答します。
垂直分割自体は悪ではないですが(この構造ならそこまでやるなら正規化したほうが良いとは思いますが)、N+1問題は気を付けたほうが良いでしょう。関連が1箇所に集約されているのでEager loadingするにも毎度全て収集するのかといったバランスも考えないとダメかもしれません。

あとはstatusの全てが外にあったほうが良いのか(itemに常に必要とされるstatusはないのか)といった部分は整理されたほうがいいと思います。

また、最初の回答にも書きましたが誰にとって良い設計なのかも考えておくといいと思います。
モデルだけを扱うだけで良い(DBは意識しない)から、追加のSQLを気にしなくて良いから、DB構造が綺麗になるからか、インデックスのコスト、インポート容易性など
いくつかの視点があると思いますが、全てをクリアできない時にどれを優先するのかは考えておくといいと思います。

コード上の見渡し(ミスの軽減)を目的としているという記述は見受けられましたが、モデル直接扱わずに間に薄い層を設けたり、テストコードで解決する手もあるでしょう。
DB保守の面で言えば後述しますが図にもいくつか疑問があります。

ORMフレームワークを利用するにしても概念設計・論理設計は簡単にでも行っておいた方が良いとは思います。
単に分割するだけにならないように注意して設計してください。
視点が違えば優劣のつけ方も変わってくると思います。どちらが目的に適しているかで選択されると良いでしょう。


以下は追加の回答で提示していただいた図を見ての疑問点です。
ちょっと重箱の隅をつくような話なので、本題と関係なさそうであれば特に回答はなくて問題ありません。

主に2番目、3番目の図の話です。

  • 〜_hoge 列がなぜ各statusに移動していないのかがわからない(型としてキーでもなさそうなのでstatusに移動すべきではないか)
  • item_status_type1s テーブルには列がそのまま移植されているようであり、そうなれば 1:1のままではないか。 これは列を行にするつもりだったということでしょうか。
  • item_status_type4s テーブルは列は1つでは?(元のテーブルが _cN を持たないようなので)
  • item_status_typeNs テーブルに列 item_statuses_idがあるが関連としては逆では?(双方向に関連させるのでしょうか)

図にしてくれたのはありがたかったですが、部分的に疑問に思うことがあったためいくつか混んらんしてしまいました。

また、テーブル構成に置いて 3番目の図におけるitem_statuses_idは idと論理的に同じはずであり、手動で設計した場合は発生しない列です。
テーブルを管理する上では冗長な情報であるのでDBだけを見た場合のためにどこかに明示しておいたほうが良いでしょう。
3番目の図は話の流れでは itemsからitem_statusesが生まれており、それを知っていればitems(主)からstatus(従)を取りに行くのが自然に思いますが、
一方で双方向に関連をつけ艇た場合は item_statusesからitemsを分離したという見方もでき、 item_statusesを主としてitemsにアクセスすることができます。
これが許容できるのか(スタイルの一貫性などで問題とならないか)も考えたほうが良いでしょう。

編集 履歴 (0)

@flied_onion さん
丁寧なご回答ありがとうございます。
やりたかったことはgames-ext-infoです。

少しこちらの状況と私が提示した例が悪く、説明も不十分でした。

新しく例を作ってみたのですが、
__________2016-04-22_10.16.25.png
のようにすべてのstatusはitem一つ一つ違ったもので、そのstatusには様々な情報のtypeがあるとします。
これらのstatusはtypeごとに20近いカラムがあり、typeは6以上あるとします。
また、それぞれのtypeには一つのitemが複数持つことがあるデータ(type1を3つなど、1対多)、一つしか持たないデータ(1対1)があります。
そのため、単純に
__________2016-04-22_10_25_14.png
のようにしてしまうと、status_type1_hogeなどのtypeは分かれているけれどデータは一つなので1対多関連のstatus_type1s、status_type2sには含めれないということが起きます。
itemsはstatus以外にもたくさんのカラムを持っているため、status_type1_hogeなどの情報だけitemsテーブルにあると開発時に見落としが発生するなど問題が発生してしまいそうです。
そのため、せめてitem_statusでまとめようということで、以下のようにitem_statusesテーブルを作ろうかと思いました。
__________2016-04-22_15.58.40.png
ここで、item_status_type4....がitem_status_idを持つ理由は、
ItemStatusがItemStatusType4....をhas_oneする関係にあるため、itemのidでitem_statusを探し、item_status_idでitem_status_type4....を探し取得するように考えています。

この場合ですと、2つ目と3つ目の画像でどちらが良いでしょうか?
どうぞよろしくお願いいたします。

編集 履歴 (0)
  • たとえば「type1を3つ」の場合は今itemsテーブルでどう表現されるんでしょう? 3レコードで表現する場合、items.idで itemを特定できなくなると思うのですが。
    それとも列 type1, type2, type3 にひとつずつ入れる感じですか?

    (これから改善しようとしているデータ構造というのは理解してますので、現状で主キーをつけようがないとかそういう状態でもかまいません)
    -
  • たとえば「type1を3つ」の場合は今itemsテーブルでどう表現されるんでしょう?>>
    status_type1_c1_1, status_type1_c1_2, status_type1_c1_3, status_type1_c2_1, status_type1_c2_2, status_type1_c2_3.......
    のようにカラムが存在しています。
    -
  • このデータを取得するときは
    ```
    item = Item.find(:id)
    status_type1 = item.item_status_type1s
    ```
    のようにしようと考えています。
    -
ウォッチ

この質問への回答やコメントをメールでお知らせします。