[sql] 개정을위한 데이터베이스 설계?

Program / 글쓴이

프로젝트에는 데이터베이스의 엔터티에 대한 모든 개정판 (변경 기록)을 저장해야합니다. 현재 우리는이를 위해 2 가지 설계 제안을 가지고 있습니다.

예 : “직원”엔터티

디자인 1 :

-- Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

-- Holds the Employee Revisions in Xml. The RevisionXML will contain
-- all data of that particular EmployeeId
"EmployeeHistories (EmployeeId, DateModified, RevisionXML)"

디자인 2 :

-- Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

-- In this approach we have basically duplicated all the fields on Employees 
-- in the EmployeeHistories and storing the revision data.
"EmployeeHistories (EmployeeId, RevisionId, DateModified, FirstName,
      LastName, DepartmentId, .., ..)"

이 일을하는 다른 방법이 있습니까?

“디자인 1″의 문제점은 데이터에 액세스해야 할 때마다 XML을 구문 분석해야한다는 것입니다. 이로 인해 프로세스 속도가 느려지고 개정 데이터 필드에 조인을 추가 할 수없는 등 몇 가지 제한 사항이 추가됩니다.

그리고 “디자인 2″의 문제점은 모든 엔터티의 각 필드를 복제해야한다는 것입니다 (우리는 개정을 유지하려는 약 70-80 개의 엔터티가 있습니다).

답변

  1. 마십시오 하지 대해 IsCurrent 판별 속성으로 하나 개의 테이블에 모두 넣어. 이로 인해 문제가 발생하고 대리 키와 다른 모든 종류의 문제가 필요합니다.
  2. 디자인 2에는 스키마 변경에 문제가 있습니다. Employees 테이블을 변경하면 EmployeeHistories 테이블과 함께 제공되는 모든 관련 sprocs를 변경해야합니다. 스키마 변경 노력을 두 배로 늘릴 수 있습니다.
  3. 디자인 1은 잘 작동하며 제대로 수행하면 성능 저하 측면에서 많은 비용이 들지 않습니다. xml 스키마 및 인덱스를 사용하여 가능한 성능 문제를 극복 할 수 있습니다. xml 파싱에 대한 귀하의 의견은 유효하지만 xquery를 사용하여 쉽게 뷰를 만들 수 있습니다. xquery를 사용하면 쿼리에 포함시키고 조인 할 수 있습니다. 이 같은…
CREATE VIEW EmployeeHistory
AS
, FirstName, , DepartmentId

SELECT EmployeeId, RevisionXML.value('(/employee/FirstName)[1]', 'varchar(50)') AS FirstName,

  RevisionXML.value('(/employee/LastName)[1]', 'varchar(100)') AS LastName,

  RevisionXML.value('(/employee/DepartmentId)[1]', 'integer') AS DepartmentId,

FROM EmployeeHistories

답변

여기서 물어볼 핵심 질문은 ‘누가 / 이력을 사용할 것인가?’입니다.

대부분 사람이 읽을 수있는 기록을보고 할 예정이라면 과거에이 체계를 구현했습니다.

‘AuditTrail’이라는 테이블 또는 다음 필드가있는 테이블을 만듭니다.

[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[EventDate] [datetime] NOT NULL,
[TableName] [varchar](50) NOT NULL,
[RecordID] [varchar](20) NOT NULL,
[FieldName] [varchar](50) NULL,
[OldValue] [varchar](5000) NULL,
[NewValue] [varchar](5000) NULL

그런 다음 테이블에서 업데이트 / 삽입을 수행 할 때마다 설정해야하는 ‘LastUpdatedByUserID’열을 모든 테이블에 추가 할 수 있습니다.

그런 다음 모든 테이블에 트리거를 추가하여 발생하는 모든 삽입 / 업데이트를 포착하고 변경된 각 필드에 대해이 테이블에 항목을 작성할 수 있습니다. 테이블에는 각 업데이트 / 삽입에 대해 ‘LastUpdateByUserID’가 제공되므로 트리거에서이 값에 액세스하여 감사 테이블에 추가 할 때 사용할 수 있습니다.

RecordID 필드를 사용하여 업데이트중인 테이블의 키 필드 값을 저장합니다. 결합 키인 경우 필드 사이에 ‘~’를 사용하여 문자열 연결을 수행합니다.

나는이 시스템에 단점이있을 것이라고 확신합니다-크게 업데이트 된 데이터베이스의 경우 성능이 저하 될 수 있지만 웹 응용 프로그램의 경우 쓰기보다 더 많은 읽기가 이루어지며 꽤 잘 작동하는 것 같습니다. 심지어 테이블 정의를 기반으로 트리거를 자동으로 작성하는 작은 VB.NET 유틸리티도 작성했습니다.

그냥 생각이야!

답변

데이터베이스 프로그래머 블로그 의 히스토리 테이블 기사 가 유용 할 수 있습니다. 여기에서 제기 된 일부 요점을 다루고 델타 스토리지에 대해 설명합니다.

편집하다

에서 역사 테이블 에세이, 저자 ( 케네스 다운스 ), 최소 7 개 컬럼의 역사 테이블을 유지하는 것이 좋습니다 :

  1. 변화의 타임 스탬프
  2. 변경 한 사용자
  3. 변경된 레코드 (이력이 현재 상태와 별도로 유지되는 위치)를 식별하는 토큰
  4. 변경 사항이 삽입, 업데이트 또는 삭제인지 여부
  5. 오래된 가치
  6. 새로운 가치
  7. 델타 (숫자 값 변경).

절대로 변경되지 않거나 기록이 필요하지 않은 열은 팽창을 피하기 위해 기록 테이블에서 추적해서는 안됩니다. 숫자 값에 델타를 저장하면 이전 값과 새 값에서 파생 될 수 있지만 후속 쿼리가 더 쉬워 질 수 있습니다.

히스토리 테이블은 비 시스템 사용자가 행을 삽입, 갱신 또는 삭제하지 못하도록 안전해야합니다. 전체 크기를 줄이고 (사용 사례에서 허용하는 경우) 주기적 제거 만 지원해야합니다.

답변

우리는 Chris Roberts가 제안한 솔루션과 매우 유사한 솔루션을 구현했으며 이는 우리에게 잘 작동합니다.

유일한 차이점은 새로운 가치 만 저장한다는 것입니다. 이전 값은 결국 이전 기록 행에 저장됩니다.

[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[EventDate] [datetime] NOT NULL,
[TableName] [varchar](50) NOT NULL,
[RecordID] [varchar](20) NOT NULL,
[FieldName] [varchar](50) NULL,
[NewValue] [varchar](5000) NULL

열이 20 개인 테이블이 있다고 가정하겠습니다. 이렇게하면 전체 행을 저장하지 않고 변경된 정확한 열만 저장하면됩니다.

답변

디자인 1을 피하십시오. 예를 들어 관리자 콘솔을 사용하여 자동 또는 “수동으로”이전 버전의 레코드로 롤백해야하는 경우 매우 유용하지 않습니다.

디자인 2의 단점은 실제로 보이지 않습니다. 두 번째 기록 테이블에는 첫 번째 기록 테이블에있는 모든 열이 포함되어야한다고 생각합니다. 예를 들어 mysql에서는 다른 테이블 ( create table X like Y) 과 동일한 구조로 테이블을 쉽게 만들 수 있습니다 . 그리고 라이브 데이터베이스에서 레코드 테이블의 구조를 변경하려고 할 때는 alter table어쨌든 명령 을 사용해야 합니다. 또한 이력 테이블에 대해서도 이러한 명령을 실행하는 데 큰 노력을 기울이지 않아도됩니다.

노트

  • 레코드 테이블에는 최신 개정 만 포함됩니다.
  • 히스토리 테이블에는 레코드 테이블의 모든 이전 레코드 레코드가 포함됩니다.
  • 히스토리 테이블의 기본 키는 추가 된 레코드 테이블의 기본 키입니다. RevisionId 열 .
  • ModifiedBy특정 개정을 작성한 사용자 와 같은 추가 보조 필드에 대해 생각해보십시오 . DeletedBy특정 개정을 삭제 한 사람을 추적 하는 필드가 필요할 수도 있습니다 .
  • DateModified의미 가 무엇인지 생각하십시오 .이 특정 개정이 작성된 위치를 의미하거나이 특정 개정이 다른 개정으로 대체 된시기를 의미합니다. 전자는 필드가 레코드 테이블에 있어야하며 첫눈에 더 직관적 인 것 같습니다. 그러나 두 번째 솔루션은 삭제 된 레코드 (이 특정 개정이 삭제 된 날짜)에 더 실용적인 것으로 보입니다. 첫 번째 해결책을 찾으면 두 번째 필드 DateDeleted가 필요할 것입니다 (물론 필요한 경우에만). 당신과 당신이 실제로 기록하고 싶은 것에 달려 있습니다.

디자인 2의 작업은 매우 간단합니다.

수정

  • 레코드 테이블에서 기록 테이블로 레코드를 복사하고 새 RevisionId (레코드 테이블에없는 경우)를 제공하고 DateModified를 처리합니다 (해석 방법에 따라 다름)
  • 레코드 표에서 레코드를 정상적으로 업데이트합니다.

지우다

  • 수정 작업의 첫 번째 단계와 정확히 동일합니다. 선택한 해석에 따라 DateModified / DateDeleted를 적절하게 처리하십시오.

삭제 취소 (또는 롤백)

  • History 테이블에서 가장 높은 (또는 특정?) 개정을 가져와 Records 테이블에 복사

특정 레코드에 대한 개정 내역 표시

  • 히스토리 테이블 및 레코드 테이블에서 선택
  • 이 작업에서 정확히 무엇을 기대하는지 생각하십시오. 아마도 DateModified / DateDeleted 필드에서 필요한 정보를 결정할 것입니다 (위 참고 참조).

Design 2로 가면 유지 관리뿐만 아니라이를 수행하는 데 필요한 모든 SQL 명령이 매우 쉬워집니다! 아마, 훨씬 더 쉽게 될 것입니다 당신이 보조 열을 사용하는 경우 ( RevisionId, DateModified)도 기록 테이블에 – 정확히 동일한 구조에서 두 테이블을 유지 (고유 키 제외)! 이를 통해 간단한 SQL 명령이 가능하며 데이터 구조 변경에 견딜 수 있습니다.

insert into EmployeeHistory select * from Employe where ID = XX

거래를 사용하는 것을 잊지 마십시오!

스케일링 의 경우이 솔루션은 XML에서 데이터를 앞뒤로 변환하지 않고 인덱스를 사용하여 매우 간단한 쿼리 전체 테이블 행을 복사하기 때문에 매우 효율적입니다!

답변

히스토리를 저장해야하는 경우, 추적중인 테이블과 동일한 스키마와 ‘개정 날짜’및 ‘개정 유형’열 (예 : ‘삭제’, ‘업데이트’)을 사용하여 새도우 테이블을 작성하십시오. 감사 테이블을 채우기 위해 일련의 트리거를 작성 (또는 생성-아래 참조)하십시오.

테이블에 대한 시스템 데이터 딕셔너리를 읽고 도구 테이블을 작성하는 섀도 테이블 및 트리거 세트를 작성하는 스크립트를 생성하는 도구를 작성하는 것은 매우 간단합니다.

이를 위해 XML을 사용하지 마십시오. XML 스토리지는이 유형의 트리거가 사용하는 기본 데이터베이스 테이블 스토리지보다 훨씬 덜 효율적입니다.

답변

Ramesh, 저는 첫 번째 접근 방식에 기반한 시스템 개발에 참여했습니다.
XML로 개정판을 저장하면 데이터베이스가 크게 증가하고 속도가 크게 느려집니다.
내 접근 방식은 엔티티 당 하나의 테이블을 갖는 것입니다.

Employee (Id, Name, ... , IsActive)

여기서 IsActive 는 최신 버전의 표시입니다.

일부 추가 정보를 수정본과 연관 시키려면 해당 정보를 포함하는 별도의 테이블을 작성하고 PK \ FK 관계를 사용하여 엔티티 테이블과 연결하십시오.

이렇게하면 모든 버전의 직원을 하나의 테이블에 저장할 수 있습니다. 이 접근법의 장점 :

  • 간단한 데이터베이스 구조
  • 테이블이 추가 전용이되므로 충돌이 없습니다.
  • 단순히 IsActive 플래그를 변경하여 이전 버전으로 롤백 할 수 있습니다
  • 객체 이력을 얻기 위해 조인이 필요 없음

기본 키는 고유하지 않아야합니다.