適用期間のあるデータをうまく表現するテーブル設計を考える

適用期間のあるデータを表現したい場合, シンプルに考えると適用開始日と適用終了日を持たせるテーブル設計を行うかと思います.

|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を入れるべきかの判断が分かれる

このようなデータを効率的に管理するためのテーブル設計を考えてみます.

# 設計案
## 案1 適用終了日を持たない

「適用期間に重複・空白は生じないものとする」と仮定した場合, IDと適用開始日のみをPKとするテーブルで任意の適用日基準のデータを取得することができます.

```sql
-- テーブル生成
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')
```

基準日を指定してレコードを取得するには以下のようにします.

```sql
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
```

自己結合で適用終了日を持った形式に変換することもできます.

```sql
-- 適用開始, 適用終了形式のビュー化
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通りあります.

1. 適用開始日 > 適用終了日 (逆転)
2. 直前期間の適用終了日 >= 適用開始日 (重複)

2.は他レコードとの比較となるためトリガーで検査します. 以下のサイトが参考になります.

[関連チェックはトリガーで - SQLer 生島勘富 の日記](http://d.hatena.ne.jp/Sikushima/20110715/1310693513)

テーブル設計は以下のようになります.
DEFAULT制約を付けているのは, 任意の基準日に対して有効なレコードを範囲指定で検索する際にいちいちISNULLを入れるのが面倒だからです.
設計上, 適用開始日が重複することは許容しないので, PKはIDと適用開始日だけで十分です.

```sql:
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
		,適用開始日
	)
)
```

関連チェックを行うトリガーは以下のように作成します.

```sql:
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します.

```sql
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しようとした場合, トリガーによって期間重複の判定がなされます.

```sql
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すると成功します.

```sql
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のように適用開始日のみ持てばよいことになるため今回は考慮しないこととしました.