DB のスキーマ設計とかの話
はじめに
ここ1年くらい、わりとまじめに DB 設計やってみて、うまくいったもの、失敗したものなど、いろいろ出てきました。その知見(というほどものじゃないけど)を書いてみようかなー、と思います。
僕は基本的にアプリ屋さん(+運用)なので、データモデルの綺麗さよりもアプリからの使いやすさ(+運用しやすさ)を重視してると思います。
前提
業務系のアプリケーションで、DB は MySQL を想定しています。*1。Web 屋さんとは違って、「COUNT したら返ってこない」、「ALTER TABLE が半日たっても終わらない」みたいな恐ろしいテーブルはありません。(とはいえ、データ量は GB 単位で、それなりにでかくて困ってるテーブルとかもあったりします)
主キーについて
いかなる場合も「主キーなし」はやめた方がいいと思います。あとで削除とか変更が必要になった際に泣くから。
キーになるものが無かったら、とりあえず実績系はオートナンバー、マスタ系は '0001', '0002' みたいな適当な連番を振っておけばよいと思います。
連携する上位システム*3がある場合、そこの主キーと合わせるか合わせないかは微妙な問題です。今はウチは合わせていますが、上位システムの設計がボロボロだったりする場合は、独自にキーを定義して、上位システムのキーは何らかの制約としたほうが良いかもしれません。*4
実績系
主キーはオートナンバー。自然キーがある場合は一意制約を貼るのがいいとおもう(複合主キーは使わない)。
子テーブルができた場合の外部参照がしやすいことと、削除とかのメンテがしやすいから。
自然キーは、上述したように使わないほうがいいと思うけど、それが本当に自然で、かつ数が多くない(最大3くらいかな)なら使っても良いと思う。たとえば、「注番」+「明細番号」とかそういうのはアリかなー、と思うので。(逆にこういう場合に「明細ID」だけで管理するのはヘンでしょ？ 場合によりそうだけど)。
マスタ
たとえ数字しか使わなくても、主キーは文字型がいいと思う。
マスタといえども変更は入るので、テスト時は実データではなく、テストデータを使った方が良いのだけど、その際に実データと重複しないキーを作りやすくしたいから。たとえば実データは '001', '002' として、テストデータは '__1', '__2' とかそんな感じ。
オートナンバーはやめておいたほうがよいと思う。開発・ステージング・本番でマスタの値が合わなくなるから。
テーブルの命名
実績系は 'T', マスタは 'M', 相関テーブルは 'R', 上位システム由来のものは「それとわかる prefix」をテーブル名の頭文字につけています。
これは僕が考えたわけじゃなく、最初に導入した際からそういう規則でした。これでいいと思います。
ただ、マスタは、「マスタのくせに変更が多い」テーブル*5があったりして、「っていうか、マスタの定義ってなんだろ？？」とは悩みます。こういうテーブルが多い場所では、prefix をつけないほうがいいかもしれないです。
カラムの命名
たとえば、TOrder (受注テーブル)があったとして、コレの主キーだと orderID(もしくは orderNo。あと order だとヘンだけど orderCode みたいにつく場合もあるかな。)みたいにつけてます。僕は id か no の方がいいんじゃないかな、と思っていますが、規則が混在するのは良くないと思うので、昔の流儀に従っています。
orderID みたいなつけ方だと、SQL 上で TOrder.orderID みたいになるので、なんかダサいです。
あとテーブル名もカラム名も、基本的に CamelCase 使ってます。(テーブル名は先頭大文字、カラム名は先頭小文字)。これも昔から決まってたっぽい。大文字だけとかより読みやすいですね。
データの縦持ちについて
項目が可変だったり、いまいち要件が決まり切っていない場合にデータを縦持ちしたくなるけど、実績系については避けたほうがよいと思います。
たとえば項目が100個あって、データが1000万件とかあったらレコードが10億件に達してしまい、いろいろ困ることになりそうです。1000万件なら何とかなるのに。項目数が数十程度なら、逆に縦持ちするメリットもあんまりないし。
あと、実装にもよりそうだけど、縦持ちしてる場合はページングがやりにくいです。データが多くて困ってるのにページングできないとかマジで死ねますよ。。。*6
賛否ありそうだけど、項目が不定なら、TEXT 型で JSON なり XML なりに変換して突っ込んだ方がまだマシだと思う。
マスタで項目が安定してない場合は縦持ちしてもいいと思うけど、まだあんまりノウハウないので自信は無いです。
トリガーについて
これまた賛否ありそうだけど、アプリケーションロジックとしてトリガーを使うのは避けた方がいいんじゃないか、と思います。データベースのメンテナンスをする際の、たとえばカラム名の変更, 移動, 追加とか、データのパージで使えるように取っておくほうがいいんじゃないかな。
データのパージについて
業務系はパージしにくいものが多いですねー。なんとかしたいんですがね。。。途中のデータを保持するようなもの(集計とかされて最終データが別のテーブルに記録されるようなやつ)は、期間を決めてパージしてます。
外部キー
僕はあんまり好きじゃないです。テストデータを突っ込むのがやりにくいから。スキーマを説明するときは資料に FK とか書いてるけど、実際は貼らない、みたいなパターンが多いです。で、必要に応じて普通のインデックスを貼っています。
ストアドプロシージャ
MySQL の場合は、いろいろ問題があるので避けた方が良いと思う。更新系なら検討の余地もあるかもしれない。(でも僕は嫌いです)
See Also: MySQL のストアドプロシージャの話
正規化
あんまりまじめに考えていないです。
基本的にはちゃんと正規化してるはず。JOIN が重なって、「遠いなー」*7と思った場合は正規化崩してることもありそう。ただ、一度決めたら変えることはあんまりないです。
削除
可能なら物理削除が望ましいです。削除復活とかでの悩みが減るから。今のところは無いのですが、場合によっては削除フラグを主キーに含めるとかも検討してみた方がいいのかなー。
履歴
ユーザさんは「履歴欲しいです」とよく言うのですが、本当に必要じゃない限りは実装しないほうがいいと思う。めんどくさいから。
テーブル的には、更新毎の親テーブルと、項目毎の子テーブルを作ることが多いです。項目単位のテーブルだけの場合もあるかな。。トリガーは使わず、アプリ側で対応しています。(だからめんどくさいのかなー。でもトリガー書くのも十分面倒だしなー)
親子関係
アプリケーションに「商品」オブジェクトと、サブクラスの「果物」オブジェクトみたいのがある場合、DBのテーブルは親子のすべての特性をカラムとして持つ方法(商品テーブルのみを使う)や、親テーブル(商品テーブル)と子テーブル(果物テーブル)で分ける設計などがあります。僕は親子テーブルに分けるのが好きです。テーブルがなるべく小さくなるほうが分かりやすいと考えているからです。
相関テーブルかませてなんかする設計もできるし、ウチにもあるんですが、コレは嫌いです。
予備のカラム
なにそれおいしいの？
必要になった時に、必要なカラムを追加する派です。
不要になったカラム
不要になったリリースの「次のリリース」で削除しています。
相関テーブル
基本的に嫌いです。N:M にならざるを得ない場合以外は使いません。*8
汎用のカラム
基本的に嫌いです。value4 って何よ、みたいな。専用のテーブル、カラムを用意すべきだと思います。
汎用のマスタ
「コード」を「名称」に置き換えるためのマスタはあってもいいと思います。「コード名」、「コード」、「名称」の汎用テーブル。*9
っていうか汎用
僕も含めて未熟だからかもしれませんが、いろんなレイヤーにおいて、汎用を意識して作ったもので、うまく作れたものはほとんどありません。専用で作ってある程度知見を得てから、必要に応じて(リファクタリングとかして)まとめ上げていかないとダメなんじゃないかなー、というのが今の見解。
おわりに
まとめる気もなく、つらつら書いてみましたが、ネタがなくなったのでとりあえずおしまいです。
DB 設計の話(とか本)って、DB 屋さんか、DB まわりの教育やってる人が書いてるのが多い気がします。アプリとか運用メインでやってる人から見ると、少し違うんじゃないかなー、と思って書いてみたのですが、あんまり違わないかもしれませんね。
そもそも DB 設計まわりって、定番の書籍とかもよく分からなくて、古いの探せばいいような気もするけど、古すぎるとオブジェクトのこと考えて無いとか、データベースリファクタリングは考えてないとか、最近の大規模系(sharding とか レプリケーションとかパーティショニングとか)が考えられてなかったりとかありそうで、うーんどうしたものか、といった感じですね。なんかお勧めあったら教えてください。
*1:Oracle だと rowid があるから、主キーなしでも大丈夫とかが違うはず
*2:無停止でやってる所の設計や運用のノウハウは個人的にすごくしりたいです。
*4:幸運にも上位システムがボロボロじゃないってことです
*5:たとえば小売業向けのシステムにおける「商品マスタ」なんかがこれに該当すると思います。
*6:まだ1億レコード行ってないけど、普通に死んでます orz
*7:直観もあるけど、どちらかというとパフォーマンス上の問題がありそうだなー、という場合
*8:1:N とか N:N(つまり 1:1) なのになぜか使われてるんだよなー。僕が担当する前に設計された DB で...
*9:でも、ウチではこれに value4 までつけてしまい、ハイパーカオスに。。。しかも主キーなし。。。
