「All-in-Oneテーブル区分値」とは？
完全にDBFlute用語ですが...
全ての区分値を一つのテーブルにまとめて管理する方式のことを示します。
#
# (ただの)テーブル区分値
#
会員ステータスとか製品ステータスとか、それぞれの区分値をそれぞれの独立したテーブルとして管理。
ExampleDB はこの方式を採用しています。
普通に正規化していくと自然にこの形になる。
#
# All-in-Oneテーブル区分値
#
会員ステータスも製品ステータスも全ての区分値を、「区分値」という抽象的な一つのテーブルに管理。
ナチュラルキーとして区分値のコードだけでなく、「区分値を特定するコード」が必要になる。
こちらの記事でも話題になりました(Blogコメントも含めて)
// DBFluteのClassification機能のおさらい
http://d.hatena.ne.jp/jflute/20090703/1246549011
で、この「All-in-Oneテーブル区分値」ですが、よく議論の的になります。
採用されているプロジェクトもかなり多く、ひたすらこれにこだわる人もいます。
で、議論すると結構ポイントの整理が大変で、本来それらポイントをまず互いにしっかり共有しないと話が進まないのですが、まとまった資料も見当たらないし、仕事の合間やランチの時間のちょっとした話し合いではとても時間が足りないものです。
(わざわざ会議で議題にするほど大げさなものでもないし)
で、見つけたのがこの記事です。
// 「SQLアタマアカデミー 第3回」がWebで公開
http://d.hatena.ne.jp/mickmack/20091114/1258173320
ポイントがよくまとまった記事で、まずはこれを互いに読んでから、メリット・デメリットを考慮して現場でどうするか？議論すると良いでしょう。
こういう資料がとてもとても欲しかったという感じです。
但し、リンク先のBlogコメントで自分が二点ほど補足をしています。
必ずあわせてご覧下さい。
なので、「OTLTのメリット３つ、デメリット５つ」ではなく、「OTLTのメリット３つ、デメリット７つ(５＋２)」という感じです。
ちなみに、「OTLT(One True Lookup Table)」という正式な(!?)名称があるようです。
ただ、DBFluteでは「区分値」という概念に着目した機能と関連付けるため、ベタベタで直感的な感じで
「All-in-Oneテーブル区分値」と呼んでいます。
All-in-Oneのメリット・デメリットの補足
細かい説明等は先ほどのリンク先を読んで下さい。(先にリンク先を読むことをお奨めします)
ここでは、Blogコメントでもしていない補足をさせて頂きます：
メリットで挙げられている、「コード検索のSQLを共通化できるため，コーディングを簡略化できる」ですが、アプリケーション開発者からの視点で言えば、ほとんど極小のメリットと感じられます。
結局、区分値を特定するコードを指定しなければならないので、それはLEFT OUTER JOINで対象の区分値テーブルを指定するのとほとんど変わりませんし、逆に、テーブル名は間違えてもちゃんとエラーになりますが、区分値を特定するコードは間違えてもエラーになりませんので、「いざ間違ってしまった時に間違いに気付くスピード」では劣るかと思います。
もしサロゲートキーを使っていないような場合は複合FKでの関連となり、なおさら煩雑で間違い易いSQLを書かなければならなくなります。
また、自分がBlogコメントで補足した：
「テーブル間の関係が全て同じような方式で関連付いてる方がわかりやすい」
という意見とこのメリットがまさに相反します。
そのため、少なくともアプリの開発現場においては、極めて小さいメリットというように感じます。
また、その他のメリットもよくよく考えると、
「DB設計者(DBA)のメリット」であり、「ディベロッパーのメリット」ではないことに気付きます
DB設計をよくやるのでわかるのですが、ERDツールでテーブル一個作って、それぞれのカラムを入力して、和名を付けたり、説明を付けたり、って、確かになかなか面倒なものです。
だんだんERDが大きくなって来て、テーブルの居場所に困ることもあります。なので、テーブルがごそっと減るのは「DB設計者にはとてもラクチン」なわけです。
でも、それは「ディベロッパーにとってのうれしいことではない」ですね。
自分がBlogコメントで補足した「表裏一体のデメリット」を考えると、どちらかというとディベロッパーにとってはうれしくない状況です。普通にテーブルとして区分値が表現されてた方が、余計なドキュメントや補足説明を読まなくていいし、区分値特定コードなんて意識しなくていいからです。
それだったら、DB設計者はちょっと我慢して頑張って、(もちろん、ちゃんと工数をもらって)ディベロッパーたちに気持ちよく実装に集中してもらった方が、システム開発全体のことを考えたら圧倒的に良いと考えます。
それに、区分値の閲覧性の問題であれば、DBFluteのSchemaHTMLで、
SchemaHTMLの区分値一覧
で、見ることができますしね。
要は...
「管理がしやすい・しにくい」という問題は、ツール次第(工夫次第)でどうとでもできる
んですね。
なんか似たような話が昔ありましたね。
「DBFlute: AdditionalForeignKey」
FK制約の付与に関することで、そこでも「工夫次第」って要素が出て来ています。
ということで、唯一挙げられている３つのメリットも、もちろん「メリットが無い訳じゃない」ですが、そこまで厚いメリットではないかな、という感じです。
まとめ
どっちを採用するにしろ、それぞれのメリット・デメリットのポイントを押さえて、何かしらの対策をして、しっかりマネジメントすることが大事です。
ちなみに自分がDB設計するときは「All-in-Oneテーブル区分値」は採用しません。
(もちろん、行った先の組織の文化を尊重して色々議論してからだけど)
薄いメリットのために多くのデメリットにコストを掛けるのは、対費用効果的に良いものであると思えないからです。
ちょっと残業して、もしくは、工数をもうちょっともらって、非常に地味な作業ですが頑張ってそれぞれの区分値のためのテーブル作ります。
自分が見て来た「All-in-Oneテーブル区分値」採用の現場では、デメリットが丸出しな状態がかなり多かったです。
ディベロッパー経験もたくさんありますが、それはもう実装しづらかった覚えがあります。
余談
こういったテーブル設計に関する記事というのはあまり世にありません。正規化の記事は多いですが、こういった現場寄りの細かい話はほとんど見つけることができません(ないわけじゃないけどまあ少ないかな)。それだけに、今回紹介した記事はとてもとても貴重だと考えます。他にもとても良い記事があるので、どんどん読んでみると頭の中でもやもやしていたものが綺麗に整理できるかもしれません。
一方で、世の中のテーブル設計に関する記事に関して読むときは、幾つか注意点があると考えます。大きく二つ：
o アプリ寄りなDB設計者、インフラ寄りなDB設計者
o 時代の違い
＜アプリ寄りなDB設計者、インフラ寄りなDB設計者＞
単独に話題にしている記事があります。基本的な考え方はこちらをご覧下さい。
o 基本：「アプリ寄りなDB設計者、インフラ寄りなDB設計者」
o 関連：「もうDBはこうなってますので」はもったいない」
ここで言いたいのは、ちょっと対象とする場所を変えた話(世の中の記事に着目)。
要は、テーブル設計に関する記事で取り上げられるポイントが、アプリ寄りな視点が抜けていたり、インフラ寄りな視点が抜けていたりと、完全ではないことが多いのでご注意下さい、という話です。
(別に今回の記事がそうだってわけじゃなくて、共通的な意味合いで)
全ての立場の視点に立ってる人なんていませんので当然かと思います。
大事なのは、読み手がそこを踏まえた上で自分の知識とすることです。
別にテーブル設計に関するものだけに限りませんが、「鵜呑み」ではなく、あくまで「参考」にするということです。なので、今日自分が書いた記事も「鵜呑み」にしてはいけません。
書いてあることをドライに自分の頭の中で解釈して、参考情報として自分の知識として保持しておくのが良いのです。
＜時代の違い＞
もう一つ、時代という違いがある場合があります。最近書かれた記事でも、昔の時代で正解だったやり方をずっと引きずってる場合もあります。
RDBという概念は昔からずっとあって、今でもあまり変わっていないように思えます。しかし、それを取り巻くインフラ環境は大きく大きく変わって来ました。５年前と今でも大きな違いを感じるくらいです。ましてや、１０年前・２０年前とは、想像もつかないくらい変わっているでしょう。
なので、考え方が変わらない部分もあれば、一方で、昔の環境に依存した考え方で今の環境には通用しない考え方もあるでしょう。
一例として...
「FK制約付けるとDB内でチェックが走るからパフォーマンスが...」
という主張を聞くことがありますが、そんなこと言ったらそもそもアプリもJavaで実装するんじゃなくてC言語で実装した方がいいよって話もあります。
結構トランザクション数の多いシステムもFK制約付けて運用してるのも多く見ていますが、FK制約が問題になってるのを見たことありません。
無論、本当に考慮すべき超絶トランザクション数のシステムはあるかと。でもそれは全体からすると稀で、気にする必要のある場面は多くないかと。
(気にする必要がある箇所があってもそれは局所的にFK外せば良いし)
でも、確かに１０年前は気にする必要があったのかもしれません。
(実際に先輩エンジニアからそういう話を聞きます)
そして、今の正解も１０年後は間違ってる可能性もあります。
記事を読むときは、きっちり分析してこの点を考慮して、自分で考えて頭に入れる必要があるかと思います。
