[解決済み] 監査ログを取得するためのデータベース設計【終了

質問事項

新しいデータベースを設計する必要があるたびに、私はかなりの時間を費やしています。 監査ログを残すために、データベーススキーマをどのように設定したらよいかを考えています。 を変更した。

この点については、すでにいくつかの質問がここで出されていますが、私は、このような すべてのシナリオに最適な方法は1つだけではありません。

• リビジョンのデータベース設計
• 変更ログ監査データベーステーブルの最適設計
• 監査証跡を取得するためのデータベース設計に関するアイデア

また、こんなものも発見しました。 データベースの変更履歴の管理に関する興味深い記事です。 それぞれのアプローチの長所と短所を挙げようとするものです。非常によく書かれていて、興味深い情報があるのですが、私の決断をさらに難しくしてしまいました。

質問です。 本や決定木のようなもので、いくつかの条件に基づいて、どの方向に進むべきかを決めるために、参考にできるものはありますか？ のような入力変数があります。

• データベーススキーマの成熟度
• ログの問い合わせ方法
• レコードの再作成が必要になる可能性
• 書き込み性能と読み込み性能のどちらを重視するか
• ログに記録される値の性質（文字列、数値、ブロブ）
• 使用可能なストレージ容量

私が知っているアプローチは

1. 作成日、更新日、ユーザーのカラムを追加する

テーブルの例

• id
• 値_1
• 値_2
• 値_3
• 作成日
• 修正日
• 作成者
• モディファイド・バイ

主な短所 変更の履歴を失う。コミット後のロールバックができない。

2. テーブルのみ挿入

テーブルの例 :

• id
• 値_1
• 値_2
• 値_3
• から
• へ
• 削除済み (ブール値)
• ユーザー

大きな短所 外部キーを最新の状態に保つには？膨大なスペースが必要

3. テーブルごとに別の履歴テーブルを作成する

履歴テーブルの例。

• id
• 値_1
• 値_2
• 値_3
• 値_4
• ユーザー
• 削除済み (ブール値)
• タイムスタンプ

主な短所 監査対象テーブルをすべて複製する必要がある。スキーマが変更された場合、すべてのログを移行する必要がある。

4. 全テーブルの統合履歴テーブルを作成する

ヒストリーテーブルの例。

• テーブル名
• フィールド
• ユーザー
• 新しい値
• 削除済み (ブール値)
• タイムスタンプ

主な短所 必要に応じてレコードを再作成（ロールバック）することが容易にできるか？new_valueカラムは巨大な文字列である必要があり、様々なカラムタイプに対応できる。

解決方法は？

いくつかのwikiプラットフォームで使用されている1つの方法は、識別データと監査するコンテンツを分離することです。 それは複雑さを加えますが、古い記録がどのようなものであったかのアイデアをユーザーに与えるためにマッシュアップしなければならない編集されたフィールドのリストではなく、完全な記録の監査証跡を得ることになります。

というテーブルがあった場合、例えば 機会 を作成する場合、実際には 2 つの別々のテーブルを作成することになります。

機会

機会_コンテンツ とか

は 機会 テーブルには、レコードを一意に識別するための情報と、外部キーの関係で参照する主キーが格納されます。 また 機会コンテンツ テーブルには、ユーザーが変更可能で、監査証跡を残したいすべてのフィールドが格納されます。 テーブルの各レコードは コンテンツ テーブルには、それ自身の PK と modified-by および modified-date データが含まれます。 また 機会 テーブルには、現在のバージョンへの参照と、メイン・レコードがいつ、誰によって作成されたかという情報が含まれます。

以下は簡単な例です。

CREATE TABLE dbo.Page(  
    ID int PRIMARY KEY,  
    Name nvarchar(200) NOT NULL,  
    CreatedByName nvarchar(100) NOT NULL, 
    CurrentRevision int NOT NULL, 
    CreatedDateTime datetime NOT NULL

そして、その内容。

CREATE TABLE dbo.PageContent(
    PageID int NOT NULL,
    Revision int NOT NULL,
    Title nvarchar(200) NOT NULL,
    User nvarchar(100) NOT NULL,
    LastModified datetime NOT NULL,
    Comment nvarchar(300) NULL,
    Content nvarchar(max) NOT NULL,
    Description nvarchar(200) NULL

私なら、コンテンツ・テーブルのPKは、PageIDとRevisionのマルチカラム・キーにすると思います（RevisionがID型である場合）。 RevisionのカラムをFKとして使用します。 そして、このようにJOINして連結レコードを引き出します。

SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID

これは私の頭の中にあるものです。 しかし、別のパターンのアイデアを得ることができるはずです。