[database-design] 감사 로깅을위한 데이터베이스 디자인

몇몇 위키 플랫폼에서 사용하는 한 가지 방법은 식별하는 데이터와 감사하는 컨텐츠를 분리하는 것입니다. 복잡성을 추가하지만 편집 한 필드 목록뿐만 아니라 사용자에게 이전 레코드의 모양에 대한 정보를 제공하기 위해 매쉬업해야하는 완전한 레코드의 감사 추적으로 끝납니다.

예를 들어 영업 거래를 추적 할 수있는 기회 라는 테이블이있는 경우 실제로 두 개의 별도 테이블을 작성합니다.

기회
기회 _ 콘텐츠 (또는 이와 유사한 것)

기회 표는 고유 레코드를 식별하는 데 사용하는 것입니다 당신이 당신의 외래 키 관계를 참조하려는 기본 키를 수용 할 정보가있을 것입니다. Opportunities_Content의 표는 사용자가 변경할 수있는 모든 필드를 개최 것이며하는 당신은 감사 추적을 계속하고 싶습니다. Content 테이블의 각 레코드 에는 자체 PK와 수정 날짜 및 수정 날짜 데이터가 포함됩니다. 기회 표는 주요 레코드가 원래 누구에 의해 생성 될 때의 현재 버전을 참조뿐만 아니라 정보를 포함한다.

다음은 간단한 예입니다.

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 및 개정의 다중 열 키로 만들고 개정이 ID 유형 인 경우 수정합니다. 개정 열을 FK로 사용합니다. 그런 다음 다음과 같이 JOINing하여 통합 레코드를 가져옵니다.

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

거기에 약간의 오류가있을 수 있습니다 … 이것은 내 머리 꼭대기에 있습니다. 그러나 대체 패턴에 대한 아이디어를 제공해야합니다.

답변

timestamp
username
ip_address
procedureName (if called from a stored procedure)
database
table
field
accesstype (insert, delete, modify)
oldvalue
newvalue

CREATE TABLE `blog` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `title` text,
    `content` text,
    `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `ix_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts';

CREATE TABLE `audit` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `blog_id` mediumint(8) unsigned NOT NULL,
    `changetype` enum('NEW','EDIT','DELETE') NOT NULL,
    `changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `ix_blog_id` (`blog_id`),
    KEY `ix_changetype` (`changetype`),
    KEY `ix_changetime` (`changetime`),
    CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;