質問: ユーザー数を月次で年齢別に集計し、その集計結果を保存しておくためのテーブル設計について、迷っていることがあります。 それは集計する年齢が30~70歳のように幅が決まっているので、「age_30,age_31...」のように年齢ごとにカラムを設けるべきか、 あるいは「age」というようなカラムを設けて、年齢はレコードの方に値として持つべきか、 どちらが良いか判断がつかないので、こちらにすべきというご意見がございましたら、お聞かせ願いたいです。 **機能要件** 集計は月に一度行い、処理実行時点のデータが集計対象となります。 集計後のデータの利用としては、選択された月の集計結果を年齢毎の表として表示することです。 現在のところ、これ以上の分析等は必要とされていない状況です。 **データ数** userの全レコードがだいたい5万程度 **テーブル定義** ``` -- ユーザー CREATE TABLE user ( id int unsigned NOT NULL COMMENT 'id', last_name varchar(30) COMMENT 'ユーザー名(姓)', first_name varchar(30) COMMENT 'ユーザー名(名)', birth_of_date DATE COMMENT '生年月日', PRIMARY KEY (id), ) -- 年齢毎集計テーブル(年齢の分だけカラムを定義) CREATE TABLE number_of_users_by_age ( year tinyint(4) unsigned NOT NULL COMMENT '年', month tinyint(2) unsigned NOT NULL COMMENT '月', age_30 int(6) unsigned DEFAULT 0 COMMENT 'ユーザー数(30歳)', age_31 int(6) unsigned DEFAULT 0 COMMENT 'ユーザー数(31歳)', -- ---------------------------- -- 略 -- -------------------------- age_70 int(6) unsigned DEFAULT 0 COMMENT 'ユーザー数(70歳)' ) -- 年齢毎集計テーブル(年齢というカラムを定義し、レコードが増える形) CREATE TABLE number_of_users_by_age ( year tinyint(4) unsigned NOT NULL COMMENT '年', month tinyint(2) unsigned NOT NULL COMMENT '月', age tinyint(2) unsigned NOT NULL COMMENT '年齢', number int(6) unsigned DEFAULT 0 COMMENT 'ユーザー数', ) ```
回答: まず、「テーブル設計にするべき」かは「どのような機能を要求されているか」によります。 今回は > > 選択された月の集計結果を年齢毎の表として表示する > > > とのことですので、最終の目標が以下の表を出力することだとしましょう ``` | 年/月| 30| 31|.......| 70| |2020/09| 1| 1|.......| 0| |2020/10| 2| 1|.......| 0| |2020/11| 1| 1|.......| 0| |2020/12| 3| 1|.......| 1| ``` 表を出力するのであればこのままテーブルを組んでしまうほうが出力は簡単でしょう。 わざわざこの表と違う集計テーブルを作成して、再度そのテーブルを加工してデータとして出力するぐらいだったら最初から出力を上記の形で元テーブルから作ったほうが良いと思います。 もちろん別の機能要件があるもしくは近いうちに実装しなければならない可能性が場合は考慮が必要です。 例えば月で絞り込み機能が欲しいとなった時に、 月で絞りたいとなったら年と月を分けておいたほうがSQLはシンプルかつindexでのパフォーマンスも最大で出るでしょう。 例えばその集計データから50歳以上のユーザ数を表示したいとなった場合には、 「年齢の分だけカラムを定義」したテーブルですと 年齢がカラムで分かれていると50才以上のカラムを列挙してSQLを作成する必要がありますが、 「年齢というカラムを定義し、レコードが増える形」のテーブルであれば age > 50 という条件が一行でかけるのでSQLはシンプルになり、indexでのパフォーマンスも期待できます。 もちろんその時に集計テーブルを改修するというのも手です。 「何をどこまで見越して作りこむか?」は 「本当にそのタイミングでそこまでのコストをかけれるのか?」 という問題になりますので、 プロダクトオーナーやマネージャーとよく相談して決めたほうが良いと思います。