SQL Serverでデータの変更履歴テーブルを作成しようとしています。
その際、主キーの一部に日付型（DATE）が含まれます。
ネットで調べてみると「主キーに日付型を使わない方がいい。」といった記事を見ました。
日付を主キーに含まないと重複してしまうのですが、どのように設計したら良いのでしょうか。
［changedate］日の［matter］に［item］の中身が［beforeval］から［afterval］になります。といったようなイメージです。
よろしくお願いします。
create table ［テーブル名］（
changedate date not null, --変更日
matter varchar(255) not null, --案件名
item varchar(255) not null,
beforeval varchar(255),
afterval varchar(255),
primary key (changedate,matter,item)
）;
主キーは専用の id フィールドを設けて、それを int または bigint 型で identity にしてはいかがですか？ changedate,matter,item の連結主キーにしているようですがメッチャ使いにくいと思いますけど。
以下の画像は Microsoft が提供しているサンプル SQL Server データベースの一部のテーブルです。赤枠が主キーですべて int 型の identity となっています。そして、Products テーブルの SupplierID と CategoryID フィールド (青枠) から Suppliers, Categories テーブルに FK が張られています (正規化)。 このようにしてはいかが？
とにかく changedate,matter,item の連結主キーにするなんてのは、どうしてもそうすべき理由がない限り、私の勝手な意見を言わせてもらえれば想像を絶する悪手です。正規化とか関係ない場合でも、主キーをたよりに行う UPDATE とか DELETE はどうするんですか？
履歴用のテーブルという前提であれば、別に主キーつけてもいいと思いますが、主キー付けずにインデックスだけつけるみたいな運用もあります。
通常のテーブルであれば、日付型のキーは避けてID振るのが正しいと思います。
PKをユニークするために日付を含めるのであれば、複合キーの最後がよいと思います。
複合キーの先頭が日付だと、PKを一意に指定しない限り、テーブルスキャンとなり検索性能は良く無いので、別に検索用のインデクスが必要になることがあると思います。
個人的には「主キーに日付型を使わない方がいい」以上に、「案件名のような任意に入力しそうなテキストを連結キーに使わない方がいい」と主張したいですね。
表記の揺らぎで同一視されなかったり、それを直そうにも連結キーだから簡単に直せなかったりとプログラム側では対処困難なことが起こりやすいです。
「(運用上は問題ないので)過去に使われたのと同じ案件名を登録したいけど、連結キーになってるため同じ案件名は使えない」みたいなこともあるかもしれません。
だから @SurferOnWww さんも提案されているように、identity などを使うことで表記の揺らぎや重複の可能性をなくした方が扱いやすくなります。
ついでに数値を検索することになるので文字列よりは速度も速くなります。
通常のテーブルであればそうだと思うのですが、履歴のテーブルだと、その時点での名前で残す必要があったりするので、敢えてIDで参照しないという事も有り得ます。（参照しているマスタ用テーブルで名前が変わっていたり、削除されていたりすると困る）
JOINしたりせずテーブル内で自己完結していて、一意である重要性の低いデータであるという前提ですね。
その時点での名前で残す必要があったりするので、敢えてIDで参照しないという事も有り得ます。
いやそれは「履歴に名前も残す」の理由にはなってますが、「敢えてIDで参照しない」の理由にはなっていませんよ。
両方残せばいいだけの話じゃないでしょうか。
IDも残しておけば「マスタから削除された」と「名前が変更された」を区別することもできるので、IDも残しておくに越したことはありません。
別に必要であればIDも残せばいいと思いますよ。
私は、履歴データの性質的に、そもそも主キーを持つ必要自体が薄いと思っていますが、どういう使われ方をするかで変わってきますし、質問文からはそこまで読み取るのは無理なので、想像の話になってきます。
一意である重要性が高いのであれば、素直に履歴用のID振ればいいと思います。
話題は「その時点での名前」なのだからIDとは名前を参照するためのIDのことで、履歴用のIDではないですよ。
データサイズとしても相対的にわずかなものだから、深く考えずにマスタに紐づくIDも残しておくに越したことはないという話です。
一意である重要性が高いのであれば、素直に履歴用のID振ればいいと思います。
むしろ履歴データのようなものはどんな使われ方をするかを考えず元データのキーとは別に独立したIDを振っておいた方が無難で、「一意である重要性が高いのであれば」なんて前提はない方がいいと思います。
話題は「その時点での名前」なのだからIDとは名前を参照するためのIDのことで、履歴用のIDではないですよ。
それは理解しています。
むしろ履歴データのようなものはどんな使われ方をするかを考えず元データのキーとは別に独立したIDを振っておいた方が無難で、「一意である重要性が高いのであれば」なんて前提はない方がいいと思います。
IDを振ったところで、それで特定するという用途をしないのであれば、私なら振らないですね。主義主張の話になってくるので、そこについてはここまでにしておきます。
皆さまの意見、本当にありがとうございます。
このテーブルを作る理由ですが、［item］の変化を時系列で確認したいためです。
そしてテーブルにデータを追加するタイミングは、［changedate］日に案件［matter］が完了した後に行います。
ですので、データ追加以降［changedate］や［matter］の値が変更されることはありません。
単純に［item］が時間とともどのように変化したのか。この［item］が［afterval］に変わった時はこの案件だったのか。というような情報を知りたかったため今回質問させていただきました。
このテーブルは私以外に業務で使うことはありません。
データの抽出の際もWhere句に「item ＝ ◯◯◯」といったイメージで使おうと考えています。
よろしくお願いします。
@Mit_tt さんしか見る事はなく、更新される事もないのであれば、とりあえず主キー無しで作って、whereやorder byに使う列にインデックス張っておけばいいんじゃないでしょうか。
自分の好きにすればいいと思います。人に聞く話ではなさそう
余計な心配かもですが、「同じ日に複数回変更できることにしたい」とかなったときに爆死しそうなので、Identityなどで代理キーを主キーとして、(今のところは)変更は日単位でユニークでなければならないということを、制約で表現しておいたほうが、安全なような気がします。
２５５サイズの項目なら「名称」です。「ID」ではありません。
変更日時の項目はをPKの一部にすると、履歴テーブルの意味は、ある日、名称Aー＞名称B、名称Cー＞名称D、、、の変更の記録です。
でも実際履歴テーブルが表すべき意味は、あるエンティティの名称変更の記録です。※一日内の変更回数制限があるかどうか業務次第です。
そして、以下のように設計したらよいかと思います。
１，履歴管理目標エンティティPK ＰＫ１
２，履歴枝番 ＰＫ２ ユニック
３，新名称
４，旧名称
。。。。
ＰＫ２のみでいいですが、履歴の利用を考慮すると、ＰＫ１＋ＰＫ２のほうが使いやすいと思います。
SQL Serverで変更履歴テーブルを設計する際に主キーとして日付型（DATE）を使用することに関して懸念があるというのは理解できます。確かに、日付型を主キーの一部に含める場合、その日付に対して複数の変更がある場合に問題が生じる可能性があります。特に、同一日に同じ案件名（matter）と項目名（item）に複数の変更がある場合、主キーが重複し、データを正しく格納できなくなります。
アプリケーションの要件やデータの使用方法によって異なる、私は考え出しの方法は：
複合キー
日付、案件名、項目名だけでなく、他の属性（例えば、変更を行ったユーザーIDなど）を追加することで複合キーの一部とすることも考えられます。これにより、同じ日に同じ案件と項目の変更があっても、異なるユーザーによる変更であれば区別がつけられます。
CREATE TABLE HistoryTable (
changedate DATE NOT NULL,
matter VARCHAR(255) NOT NULL,
item VARCHAR(255) NOT NULL,
user_id INT NOT NULL, -- 変更を行ったユーザーのID
beforeval VARCHAR(255),
afterval VARCHAR(255),
PRIMARY KEY (changedate, matter, item, user_id)
);