適用期間のあるデータを表現したい場合, シンプルに考えると適用開始日と適用終了日を持たせるテーブル設計を行うかと思います.
| ID(PK) | 適用開始日(PK) | 適用終了日 | 値 |
|---|---|---|---|
| 1 | 2018/1/1 | 2018/1/31 | a |
| 1 | 2018/2/1 | 2018/2/28 | b |
| 1 | 2018/3/1 | 9999/12/31 | c |
ただし, このテーブルには課題があります.
- 適用期間の不整合(重複, 逆転)を防止できない
- 未終了のデータについて, 適用終了日に将来の日付を入れておくべきかNULLを入れるべきかの判断が分かれる
このようなデータを効率的に管理するためのテーブル設計を考えてみます.
「適用期間に重複・空白は生じないものとする」と仮定した場合, IDと適用開始日のみをPKとするテーブルで任意の適用日基準のデータを取得することができます.
-- テーブル生成
CREATE TABLE #test_table
(
ID int NOT NULL,
適用開始日 datetime NOT NULL,
値 varchar(10)
primary key
(
ID,
適用開始日
)
)
-- テストデータ投入
INSERT INTO
#test_table
VALUES
(1, '2018/01/01', 'a'),
(1, '2018/02/01', 'b'),
(1, '2018/03/01', 'c')基準日を指定してレコードを取得するには以下のようにします.
DECLARE @基準日 datetime = '2018/01/15'
SELECT
*
FROM
#test_table
WHERE
適用開始日 = (
SELECT
MAX(適用開始日)
FROM
#test_table
WHERE
適用開始日 < @基準日
)ID 適用開始日 値
1 2018-01-01 00:00:00.000 a
自己結合で適用終了日を持った形式に変換することもできます.
-- 適用開始, 適用終了形式のビュー化
SELECT
b1.ID
,b1.適用開始日
,b1.適用終了日
,b2.値
FROM
(
-- 適用開始日に対応する適用終了日を取得
SELECT
a1.ID
,a1.適用開始日
,DATEADD(d, -1, MIN(a2.適用開始日)) 適用終了日
FROM
#test_table a1
,#test_table a2
WHERE
a1.ID = a2.ID
and a1.適用開始日 < a2.適用開始日
GROUP BY
a1.ID
,a1.適用開始日
UNION ALL
-- 適用開始日が最大の場合は未終了のため適用終了日を9999/12/31とする
SELECT
a3.ID
,MAX(a3.適用開始日)
,'9999/12/31'
FROM
#test_table a3
GROUP BY
a3.ID
) b1
INNER JOIN
#test_table b2
on
b1.ID = b2.ID
and b1.適用開始日 = b2.適用開始日ID 適用開始日 適用終了日 値
1 2018-01-01 00:00:00.000 2018-02-28 00:00:00.000 a
1 2018-03-01 00:00:00.000 2018-04-30 00:00:00.000 b
1 2018-05-01 00:00:00.000 9999-12-31 00:00:00.000 c
適用期間に空白があり得る場合, 適用開始日だけでなく適用終了日も持つ必要があります. このとき起こり得る不整合は以下の2通りあります.
- 適用開始日 > 適用終了日 (逆転)
- 直前期間の適用終了日 >= 適用開始日 (重複)
2.は他レコードとの比較となるためトリガーで検査します. 以下のサイトが参考になります.
テーブル設計は以下のようになります. DEFAULT制約を付けているのは, 任意の基準日に対して有効なレコードを範囲指定で検索する際にいちいちISNULLを入れるのが面倒だからです. 設計上, 適用開始日が重複することは許容しないので, PKはIDと適用開始日だけで十分です.
CREATE TABLE [dbo].[test_table]
(
ID varchar(20) NOT NULL,
適用開始日 datetime NOT NULL,
適用終了日 datetime NOT NULL DEFAULT '9999/12/31',
値 varchar(20) NULL
primary key
(
ID
,適用開始日
)
)
関連チェックを行うトリガーは以下のように作成します.
CREATE TRIGGER [dbo].[trIUD_test_table]
ON [dbo].[test_table]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
IF NOT (UPDATE(ID) OR UPDATE(適用開始日) OR UPDATE(適用終了日))
BEGIN
RETURN
END
-- 逆転チェック
IF EXISTS (
SELECT ID
FROM [dbo].[test_table]
WHERE
(
ID IN ( SELECT ID FROM INSERTED )
OR ID IN ( SELECT ID FROM DELETED )
)
AND 適用開始日 >= 適用終了日
)
BEGIN
RAISERROR (N'適用開始終了が逆転しています', 16, 1);
ROLLBACK TRANSACTION
RETURN
END
-- 全部重複チェック
IF EXISTS (
SELECT ID
FROM [dbo].[test_table]
WHERE
(
ID IN ( SELECT ID FROM INSERTED )
OR ID IN ( SELECT ID FROM DELETED )
)
GROUP BY
ID, 適用開始日, 適用終了日
HAVING COUNT(*) > 1
)
BEGIN
RAISERROR (N'適用開始終了が重複しています', 16, 1);
ROLLBACK TRANSACTION
RETURN
END
-- 一部重複チェック
IF EXISTS (
SELECT ID
FROM [dbo].[test_table] m
WHERE
(
ID IN ( SELECT ID FROM INSERTED )
OR ID IN ( SELECT ID FROM DELETED )
)
AND EXISTS (
SELECT *
FROM [dbo].[test_table] s
WHERE
s.ID = m.ID
AND s.適用開始日 < m.適用開始日
AND s.適用終了日 >= m.適用開始日
)
)
BEGIN
RAISERROR('適用期間が重複しています', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
END;
動作をテストするため最初に適当な期間をINSERTします.
INSERT INTO [db].[dbo].[test_table] (コード, 適用開始日, 値) values (1, '2018/04/01', 'first_period')コード 適用開始日 適用終了日 値
1 2018-04-01 00:00:00.000 2018-09-30 00:00:00.000 first_period
重複する期間をINSERTしようとした場合, トリガーによって期間重複の判定がなされます.
INSERT INTO [db].[dbo].[test_table] (コード, 適用開始日, 値) values (1, '2018-06-01', 'second_period')メッセージ 50000、レベル 16、状態 1、プロシージャ trIUD_test_table、行 80
適用期間が重複しています
メッセージ 3609、レベル 16、状態 1、行 18
トランザクションはトリガーで終了しました。バッチは中止されました。
最初に挿入した期間を適用終了させたうえで, 重複しないように後の期間をINSERTすると成功します.
UPDATE [db].[dbo].[test_table] SET 適用終了日 = '2018-09-30' WHERE 適用開始日 = '2018-04-01'
INSERT INTO [db].[dbo].[test_table] (コード, 適用開始日, 値) VALUES (1, '2018-12-01', 'second_period')(1 行処理されました)
(1 行処理されました)
コード 適用開始日 適用終了日 値
1 2018-04-01 00:00:00.000 2018-09-30 00:00:00.000 first_period
1 2018-12-01 00:00:00.000 9999-12-31 00:00:00.000 second_period
発展形として「重複する期間がINSERTされた場合, 自動的に前の期間を適用終了させたうえでINSERTする」「ある期間をDELETEした場合, 直前の期間の適用終了を伸ばして補完する」といった仕様も可能ですが, そうなると結局「適用期間に空白を許容しない」こととなり, 案1のように適用開始日のみ持てばよいことになるため今回は考慮しないこととしました.