【データベース】日付テーブル（カレンダーテーブル）とは：利点や作成方法
2023.10.23 /
本記事ではシステムのデータベースにおける、日付テーブル（カレンダーテーブル）について利点や作成方法などを詳しく解説していきます。
勤怠管理アプリケーションや予約管理アプリケーションなどを作成する場合、データベースに日付テーブルを作成すると効率的な検索・フィルタリングやデータに正確性を持たすことができます。
本記事を通して、アプリケーションにおける日付テーブルについて理解を深めてください。
データベース：日付テーブル（カレンダーテーブル）
日付テーブルとは
現代のほぼすべてのシステムにはデータベースが備わっています。データベースとは大量の「データ」を蓄積して、簡単にデータを利用可能にするシステムです。
データベースの構造
データベースは複数のテーブルから構成されます。テーブルは１つ以上のカラム（列またはフィールドとも呼ばれる）とロウ（行またはレコードとも呼ばれる）からなる、Excelのような二元素表です。
日付に関連するデータを扱うWebアプリケーションでは日付テーブル（Date Table）を利用することが多いです。日付テーブルとは日付に関する情報を格納したデータベースのテーブルです。
日付テーブルは勤怠管理システムや予定管理システムなどで利用されます。日付テーブルを利用することで検索やフィルタリングを効率的に行えるようにし、日付に関連するデータに一貫性や正確性を持たすことができます。
日付テーブルのカラム（フィールド）
日付テーブルのカラムは基本的に以下のようなカラム（フィールド）で構成されます。
| カラム名（フィールド名） | 説明 |
|---|---|
| ID | 日付テーブルのレコードを識別するための主キー（IDが主キー以外のカラムとなることもあります） |
| 日付（Date） | 日付を示すフィールド。日付テーブルのメインとなるフィールド |
| 年（Year） | 日付の年を示すフィールド。特定の年で検索・フィルタリングするときなどで使用される |
| 月（Month） | 日付の月を示すフィールド。特定の月で検索・フィルタリングするときなどで使用される |
| 曜日（DayOfWeek） | 日付の曜日を示すフィールド |
| 休日（Holiday） | 祝日や休日を示すフィールド |
| メモ（Notes） | 日付に関連するコメントを登録するフィールド |
開発しているシステムや日付テーブルの用途によって、構成されるカラムは異なります。ですが上記のフィールドは基本的には必要となるものばかりです
日付フィールドは日付型か文字列型か
日付フィールドを日付型ではなく文字列型で作成している人がいます。しかし基本的には日付フィールドは日付型で作成することがお勧めです。
日付型を使用することでデータの整合性やクエリの効率性を確保できます。
日付の計算をする場合を考えると、日付型では簡単に計算できますが文字列型では面倒な処理が必要となります。また日付型であれば国の違いによる時差も国を指定することで自動変換が可能です。
日付テーブルの利用用途
日付テーブルは以下のようなアプリケーションで利用されることが多いです。
- 勤怠管理アプリケーション
- 予定管理（カレンダー）アプリケーション
- 予約アプリケーション
例えば勤怠管理アプリケーションではデータベースに以下のようなテーブルを作成します。
- 従業員ID（主キー）
- 名前
- 部署名
- 役職
- メールアドレス
- 権限
- ID（主キー）
- 日付
- 年
- 月
- 曜日
- 休日
- メモ
- ID（主キー）
- 従業員ID（外部キー）
- 日付テーブルのID（外部キー）
- 出勤時間
- 退勤時間
- 休憩時間
- 勤務時間
- 残業時間
勤怠記録テーブルでは日付テーブルのIDを外部キーとして参照します。これによりどの日付に勤怠記録が関連しているかや検索・フィルタリングの効率的な実行、外部キー制約によるデータの整合性を確保することができます。
日付テーブルの作成方法
ここでは10年間分の日付データを日付テーブルに挿入する方法を記します。
まず以下のSQL文を実行して日付テーブル（DateTable）を作成します。
CREATE TABLE DateTable (
DateID INT PRIMARY KEY AUTO_INCREMENT,
Date DATE,
DayOfWeek VARCHAR(100),
Month VARCHAR(100),
Year INT,
Holiday VARCHAR(100)
);
次に10年間分の日付データを生成し、INSERT文により日付テーブルに日付データを追加していきます。
INSERT INTO DateTable (Date, DayOfWeek, Month, Year, Holiday)
SELECT
DATE_ADD('2023-08-28', INTERVAL seq DAY) AS Date,
DAYNAME(DATE_ADD('2023-08-28', INTERVAL seq DAY)) AS DayOfWeek,
MONTHNAME(DATE_ADD('2023-08-28', INTERVAL seq DAY)) AS Month,
YEAR(DATE_ADD('2023-08-28', INTERVAL seq DAY)) AS Year,
NULL AS Holiday
FROM (
SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
...
SELECT 364 UNION ALL SELECT 365
) AS days;
上記のSQLを実行することにより、10年間分のデータ3650行が日付テーブルに追加されます。
関連記事
