いやまぁそのまんまなのですが。
おいちゃんの今までの経験的に
・ログとか履歴とか明細とかそーゆー類いのDBは「その1テーブル(群)で情報が完結する」ようにしておいたほうが圧倒的によい
と思っているので、その辺について少しかみ砕いて。
色々と痛いものは拝見しているのですが……記憶にある限りで割と「最悪」に近かったのは……どれだろう……うんあの「レンタル系」のを取り出してみよう(案件がバレないように、適当に情報を操作隠蔽しています)。
システムの大雑把な概要としては「とあるもの」のレンタルの管理です。
「量産品で数があるもの」ではなくて、割と「個々に比較的ユニークな」物品ですね。
レンタルなので
レンタル対象の物品のテーブル レンタル対象の物品id 対象物の名前 諸々の情報 1単位時間あたりのレンタル費
的な、いわゆるレンタル対象物品のマスターテーブルがあります(量産品のレンタル「ではない」ので。まぁ量産品のレンタルでも論旨は一緒なんですが)。
んで、レンタルすると
レンタルテーブル id レンタル対象の物品id FK ユーザID FK レンタルfrom_date レンタルto_date nullable その他情報
とかってテーブルで管理して、まぁ大雑把には「レンタルto_dateがnullならレンタル中」とかなんとか。
レンタルテーブルは、ようは「履歴テーブル」ですね。「レンタルの履歴」。厳密には「現在レンタル中の情報+レンタルが終わったものの履歴」。
なのでまぁ気になる人は
・レンタル中テーブル
・レンタル履歴テーブル
とかやってもよいか、と(個人的にはこっちのほうが好み。「1テーブルで複数state」は基本あまり好まないので)。
この場合「レンタル中テーブルにはレンタルto_dateがない」「レンタル履歴テーブルのレンタルto_dateはnullableではない」ってくらいの差異で以下のお話にはやっぱり影響しないのでどっちでも好きな方で。
これで「誰がいつ何をレンタルしたか」が、大体わかりますので、「ｘ年度の、例えばカテゴリ別とかの売り上げ」とかの集計も(物品テーブルからカテゴリがわかる前提ですが)大体一発です。
……ここで気づいた人はおとなしくしているように。
さて。
書いた通りですが、この手のビジネスはn年とか1年とか半期とか四半期とか一定の期間で「どの物品がおいくらほど売り上げたか」とかほにゃららとかありますので、売上金額の計算をします。
レンタルテーブルを左側にして、LEFT JOINでレンタル対象の物品のテーブルくっつけてレンタル費とか引っかけてきてあとは適当にGROUP BYして(書いてないけど「カテゴリ」とか商品にあるだろうし)集計すれば、比較的簡単に適当な表の元ネタができあがります。
んでもって。
「この手順で合計をSUMする」と、端的には「アウトでダウト」になります。
端的には「途中でレンタル費が上がったり下がったりしたら?」
レンタル対象の物品のテーブルはいわゆる「マスタテーブル」なのですが、マスタは基本「現在の情報」なので、「現在の情報」で、例えば「(値上がり前の)3年前の集計」とかやろうとすると、数値が狂い得ます。
なお実際に「n年とn+1年に、同じｘ年度の集計を出したんだけど出力結果が違うんだけど」という事象に遭遇しましたよ orz
なので個人的には「基本、マスタテーブルのレコードは大体まるっと一通りデータをcopyしておく」ほうがよいと思ってます(明らかにど～でもいい created_at とかは削るにしても)。
別解として「マスタテーブル側をごにょごにょする(具体的には「INSERTのみでUPDATEもDELETEもしない(日付で最新情報と過去の情報を把握)」ってやり方)」もあるのですが、それはそれで割と手間がかかるので、まぁお好みで。
例えばECなんかの「売り上げテーブル + 売り上げ明細テーブル」なんかも、おいちゃん的には「履歴テーブル」だと思っているので、同様。
なお「売り上げ明細と売り上げのJOIN」は「履歴と履歴のJOIN」なので、これは、あり。
なのでまぁ、基本おいちゃんは「履歴テーブルには"全情報を入れる"」って方向に倒すことが多いです。
っていうか「マスタテーブルは時系列を持っていない事が多い」ので、「時系列情報を確保する」か「履歴に全情報を入れる」か、どっちかしないと事故ります*1。
……ってあたり、割とちょいちょいお話をする事があるので、一度整理したかったので、ざっくり書いてみました。
*1:ほかの方法がある可能性を否定はしないんだけど、おいちゃんは知らない
