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

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

---

프로젝트에는 데이터베이스의 엔터티에 대한 모든 개정판 (변경 기록)을 저장해야합니다. 현재 우리는이를 위해 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 테이블과 함께 제공되는 모든 관련 테이블을 변경해야합니다. 스키마 변경 노력을 두 배로 늘릴 수 있습니다.
3. 디자인 1은 잘 작동하며 제대로 수행하면 성능 저하 측면에서 많은 비용이 들지 않습니다. xml 스키마 및 인덱스를 사용하여 가능한 성능 문제를 극복 할 수 있습니다. xml 파싱에 대한 귀하의 의견은 유효하지만 쿼리에 포함하고 조인 할 수있는 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 필드에서 필요한 정보를 결정할 것입니다 (위 참고 참조).

If you go for Design 2, all SQL commands needed to do that will be very very easy, as well as maintenance! Maybe, it will be much much easier if you use the auxiliary columns (RevisionId, DateModified) also in the Records table - to keep both tables at exactly the same structure (except for unique keys)! This will allow for simple SQL commands, which will be tolerant to any data structure change:

insert into EmployeeHistory select * from Employe where ID = XX

Don't forget to use transactions!

As for the scaling, this solution is very efficient, since you don't transform any data from XML back and forth, just copying whole table rows - very simple queries, using indices - very efficient!

---

If you have to store history, make a shadow table with the same schema as the table you are tracking and a 'Revision Date' and 'Revision Type' column (e.g. 'delete', 'update'). Write (or generate - see below) a set of triggers to populate the audit table.

It's fairly straightforward to make a tool that will read the system data dictionary for a table and generate a script that creates the shadow table and a set of triggers to populate it.

Don't try to use XML for this, XML storage is a lot less efficient than the native database table storage that this type of trigger uses.

---

Ramesh, I was involved in development of system based on first approach.
It turned out that storing revisions as XML is leading to a huge database growth and significantly slowing things down.
My approach would be to have one table per entity:

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

where IsActive is a sign of the latest version

If you want to associate some additional info with revisions you can create separate table containing that info and link it with entity tables using PK\FK relation.

This way you can store all version of employees in one table. Pros of this approach:

• Simple data base structure
• No conflicts since table becomes append-only
• You can rollback to previous version by simply changing IsActive flag
• No need for joins to get object history

Note that you should allow primary key to be non unique.

---

The way that I've seen this done in the past is have

Employees (EmployeeId, DateModified, < Employee Fields > , boolean isCurrent );

You never "update" on this table (except to change the valid of isCurrent), just insert new rows. For any given EmployeeId, only 1 row can have isCurrent == 1.

The complexity of maintaining this can be hidden by views and "instead of" triggers (in oracle, I presume similar things other RDBMS), you can even go to materialized views if the tables are too big and can't be handled by indexes).

This method is ok, but you can end up with some complex queries.

Personally, I'm pretty fond of your Design 2 way of doing it, which is how I've done it in the past as well. Its simple to understand, simple to implement and simple to maintain.

It also creates very little overhead for the database and application, especially when performing read queries, which is likely what you'll be doing 99% of the time.

It would also be quite easy to automatic the creation of the history tables and triggers to maintain (assuming it would be done via triggers).

---

Revisions of data is an aspect of the 'valid-time' concept of a Temporal Database. Much research has gone into this, and many patterns and guidelines have emerged. I wrote a lengthy reply with a bunch of references to this question for those interested.

---

I'm going to share with you my design and it's different from your both designs in that it requires one table per each entity type. I found the best way to describe any database design is through ERD, here's mine:

In this example we have an entity named employee. user table holds your users' records and entity and entity_revision are two tables which hold revision history for all the entity types that you will have in your system. Here's how this design works:

The two fields of entity_id and revision_id

Each entity in your system will have a unique entity id of its own. Your entity might go through revisions but its entity_id will remain the same. You need to keep this entity id in you employee table (as a foreign key). You should also store the type of your entity in the entity table (e.g. 'employee'). Now as for the revision_id, as its name shows, it keep track of your entity revisions. The best way I found for this is to use the employee_id as your revision_id. This means you will have duplicate revision ids for different types of entities but this is no treat to me (I'm not sure about your case). The only important note to make is that the combination of entity_id and revision_id should be unique.

There's also a state field within entity_revision table which indicated the state of revision. It can have one of the three states: latest, obsolete or deleted (not relying on the date of revisions helps you a great deal to boost your queries).

One last note on revision_id, I didn't create a foreign key connecting employee_id to revision_id because we don't want to alter entity_revision table for each entity type that we might add in future.

INSERTION

For each employee that you want to insert into database, you will also add a record to entity and entity_revision. These last two records will help you keep track of by whom and when a record has been inserted into database.

UPDATE

Each update for an existing employee record will be implemented as two inserts, one in employee table and one in entity_revision. The second one will help you to know by whom and when the record has been updated.

DELETION

For deleting an employee, a record is inserted into entity_revision stating the deletion and done.

As you can see in this design no data is ever altered or removed from database and more importantly each entity type requires only one table. Personally I find this design really flexible and easy to work with. But I'm not sure about you as your needs might be different.

[UPDATE]

Having supported partitions in the new MySQL versions, I believe my design also comes with one of the best performances too. One can partition entity table using type field while partition entity_revision using its state field. This will boost the SELECT queries by far while keep the design simple and clean.

---

If indeed an audit trail is all you need, I'd lean toward the audit table solution (complete with denormalized copies of the important column on other tables, e.g., UserName). Keep in mind, though, that bitter experience indicates that a single audit table will be a huge bottleneck down the road; it's probably worth the effort to create individual audit tables for all your audited tables.

If you need to track the actual historical (and/or future) versions, then the standard solution is to track the same entity with multiple rows using some combination of start, end, and duration values. You can use a view to make accessing current values convenient. If this is the approach you take, you can run into problems if your versioned data references mutable but unversioned data.

---

If you want to do the first one you might want to use XML for the Employees table too. Most newer databases allow you to query into XML fields so this is not always a problem. And it might be simpler to have one way to access employee data regardless if it's the latest version or an earlier version.

I would try the second approach though. You could simplify this by having just one Employees table with a DateModified field. The EmployeeId + DateModified would be the primary key and you can store a new revision by just adding a row. This way archiving older versions and restoring versions from archive is easier too.

Another way to do this could be the datavault model by Dan Linstedt. I did a project for the Dutch statistics bureau that used this model and it works quite well. But I don't think it's directly useful for day to day database use. You might get some ideas from reading his papers though.

---

How about:

• EmployeeID
• DateModified
  • and/or revision number, depending on how you want to track it
• ModifiedByUSerId
  • plus any other information you want to track
• Employee fields

You make the primary key (EmployeeId, DateModified), and to get the "current" record(s) you just select MAX(DateModified) for each employeeid. Storing an IsCurrent is a very bad idea, because first of all, it can be calculated, and secondly, it is far too easy for data to get out of sync.

You can also make a view that lists only the latest records, and mostly use that while working in your app. The nice thing about this approach is that you don't have duplicates of data, and you don't have to gather data from two different places (current in Employees, and archived in EmployeesHistory) to get all the history or rollback, etc).

---

If you want to rely on history data (for reporting reasons) you should use structure something like this:

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

// Holds the Employee revisions in rows.
"EmployeeHistories (HistoryId, EmployeeId, DateModified, OldValue, NewValue, FieldName)"

Or global solution for application:

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

// Holds all entities revisions in rows.
"EntityChanges (EntityName, EntityId, DateModified, OldValue, NewValue, FieldName)"

You can save your revisions also in XML, then you have only one record for one revision. This will be looks like:

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

// Holds all entities revisions in rows.
"EntityChanges (EntityName, EntityId, DateModified, XMLChanges)"

---

We have had similar requirements, and what we found was that often times the user just wants to see what has been changed, not necessarily roll back any changes.

I'm not sure what your use case is, but what we have done was create and Audit table that is automatically updated with changes to an business entity, including the friendly name of any foreign key references and enumerations.

Whenever the user saves their changes we reload the old object, run a comparison, record the changes, and save the entity (all are done in a single database transaction in case there are any problems).

This seems to work very well for our users and saves us the headache of having a completely separate audit table with the same fields as our business entity.

---

It sounds like you want to track changes to specific entities over time, e.g. ID 3, "bob", "123 main street", then another ID 3, "bob" "234 elm st", and so on, in essence being able to puke out a revision history showing every address "bob" has been at.

The best way to do this is to have an "is current" field on each record, and (probably) a timestamp or FK to a date/time table.

Inserts have to then set the "is current" and also unset the "is current" on the previous "is current" record. Queries have to specify the "is current", unless you want all of the history.

There are further tweaks to this if it's a very large table, or a large number of revisions are expected, but this is a fairly standard approach.

참고URL : https://stackoverflow.com/questions/39281/database-design-for-revisions