DB設計(勤怠管理)について

Question

前提・実現したいこと

RecoRuのような勤怠管理Webアプリを作成したいのですが、DB設計について悩んでいます。
現在は社員テーブルと勤怠テーブルがあり、それぞれ以下のような構造になっています。

社員テーブル 勤怠テーブル
id id
name user_id
. 日にち(yyyyMMdd)
. 出勤時間
. 退勤時間
. 休憩時間
. 出勤区分

この状態で勤怠表を打刻し、DBに登録するとuser_idと日にち毎にデータが挿入されるため、1年間で365件×従業員数のレコードが登録されることになります。これでは検索が大変になるのではと思いテーブルを分けようと思ったのですが、どのように分ければよいかご助力願えたらと思います。
自分が考えたDB構造としては以下のようなものがあります。

社員テーブル 出勤時間テーブル 退勤時間テーブル 休憩時間テーブル 出勤区分テーブル
id id id id id
name user_id user_id user_id user_id
. date_id date_id date_id date_id
. 出勤時間 退勤時間 休憩時間 出勤区分

それとも勤怠表は月ごとに切り替わるため、月ごとにわけるほうがよいのでしょうか？その際どのような設計にするのが望ましいでしょうか？(月の中間テーブルを作成？)

補足情報（FW/ツールのバージョンなど）

java
SpringBoot
PostgreSQL

Comments

[退会済みユーザー]

> これでは検索が大変になる
そのためのデータベースソフトウェアですよ？ 高度な集計もクエリーひとつでサクッと解決するんですけど。

[退会済みユーザー]

すみません。検索が大変になるというより、値を取得する際にテーブルを分けたほうが処理が速いかな？と思ってのことだったんですが、変わらないですかね？

[退会済みユーザー]

インデックスを適切に設定したり、回答にも書きましたがパーティショニングやビューなど、方法はいくらでもあります。

[dodox86]

> 1年間で365件×従業員数のレコードが登録されることになります。これでは検索が大変になる
そんなに心配なのであればテスト用のデータ数千でも数十万でも作ってブチ込んで試してみてはいかがでしょう。多分件数自体は問題にならないと思います。あと気になるのは、今のご質問内容では業務案件の相談、コンサルティング依頼です。

[退会済みユーザー]

テーブル構成は、マークダウンのテーブルレイアウトで作成しましょう

Answer 1

個人的な意見ですが、出勤区分は良いとして、出勤・退勤・休憩については、識別した区分を持つ一つのテーブルにすると思います。

出勤・退勤・休憩の何れにせよ時間だけは絶対ですのでそれらは時系列に並ぶはずです。
識別を補正するにしてもテーブルの移動はせずに済みますので、一つのテーブルになっていた方が確認もし易そうです。

date_idが何なのかは分かりませんけども、勤怠であれば締めのコード（そのコードを日付の範囲で変換できるテーブルを別に持つ）を持っていた方が良いかと思います。

データ量については、postgresならパーティション分割できますので、後から考えても良いかと思います。

Answer 2

勤怠管理システムのおおよそとして、
・リアルタイムな日々の記録テーブル
・集計単位（週/月/年）ごとの確定データを集約したテーブル
の2段構えというのは全然アリだと思います。

日々の記録を一つのテーブルに溜め込んで、10年後20年後のレコード数も概算できると思いますが、
例えば、5.11. テーブルのパーティショニングを活用することでパフォーマンス改善したりもできますし、
古典的には
日々のデータを記録するテーブルを年単位で作っておき、
検索用に年単位のデータを串刺しで検索できるようにビューを組む、
などとすることで記録性や応答性を犠牲にせずデータの肥大化にも対応できるかと。

応答の遅いクエリーを改善するのに14.1. EXPLAINの利用によってボトルネックをあぶり出し、
インデックスの最適化を行ったりパーティショニングしたりすればいいです。

24.1. 定常的なバキューム作業も駆使してパフォーマンスが落ちないようにするのも忘れずに。