跟踪变更历史的数据库结构 [英] Database structure to track change history

问题描述

我正在从事个人项目的项目管理系统的数据库设计工作，我遇到了一个困扰。

I'm working on database designs for a project management system as personal project and I've hit a snag.

我想实现一个票证系统希望门票看起来像 Trac中的门票。我用什么结构来复制这个系统？ （我没有任何成功安装任何我的系统的trac，所以我真的看不到它在做什么）

I want to implement a ticket system and I want the tickets to look like the tickets in Trac. What structure would I use to replicate this system? (I have not had any success installing trac on any of my systems so I really can't see what it's doing)

注意：我没有兴趣尝试以任何版本存放或显示机票。我只需要一个变化的历史。我不想存储额外的数据。此外，我已经在文本字段中使用序列化数组实现了这样的功能。我不想再作为一个解决方案来实现。

Note: I'm not interesting in trying to store or display the ticket at any version. I would only need a history of changes. I don't want to store extra data. Also, I have implemented a feature like this using a serialized array in a text field. I do not want to implement that as a solution ever again.

编辑：我只在寻找数据库结构。触发器/回调并不是一个问题。

推荐答案

我已经使用thin设计：

I have implemented pure record change data using a "thin" design:

RecordID  Table  Column  OldValue  NewValue
--------  -----  ------  --------  --------

你可能不想使用表和列，而是对象和属性等，具体取决于您的设计。

You may not want to use "Table" and "Column", but rather "Object" and "Property", and so forth, depending on your design.

灵活性和简单性的优势，以查询速度为代价 - 表和列列上的集群索引可以加快查询和过滤。但是，如果要在表或对象级别频繁地查看更改日志，您可能需要设计更平坦的东西。

This has the advantage of flexibility and simplicity, at the cost of query speed -- clustered indexes on the "Table" and "Column" columns can speed up queries and filters. But if you are going to be viewing the change log online frequently at a Table or object level, you may want to design something flatter.

编辑：有几个人正确地指出，用这个解决方案你不能把一个变更集合在一起。我在上表中忘记了这一点 - 我使用的实现还有一个事务表，其中包含datetime，用户和其他信息以及TransactionID列，因此设计将如下所示：

EDIT: several people have rightly pointed out that with this solution you could not pull together a change set. I forgot this in the table above -- the implementation I worked with also had a "Transaction" table with a datetime, user and other info, and a "TransactionID" column, so the design would look like this:

CHANGE LOG TABLE:
RecordID  Table  Column  OldValue  NewValue  TransactionID
--------  -----  ------  --------  --------  -------------

TRANSACTION LOG TABLE:
TransactionID  UserID  TransactionDate
-------------  ------  ---------------

这篇关于跟踪变更历史的数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！