我应该如何在数据库中设置表来保存记录的历史记录？
我正在尝试为 ASP.NET MVC 站点设置一个 SQL Server 数据库，该数据库将存储最新信息以及所有数据更改的历史记录。
我的网站的信息来自用户上传的 XML 文件。站点解析 XML 并将包含的信息写入站点数据库。连续上传的元素实际上可能代表相同的事物，但某些数据可能已更改。但就像我之前说过的，我想跟踪每个版本。
下表显示了我正在考虑解决这个问题的一种方法。我会为每次上传中的每个项目创建重复的记录。与之前上传的项目相匹配的新项目将被分配相同的 ID，但每个项目将被分配一个唯一的上传 ID。
Upload 1: Erik and Sara are added
Upload 2: Erik renamed to Eric, Bill added
Upload 3: Sarah grew 2" taller, Eric was removed.
[PERSONS TABLE]
PersonID Name Height UploadID
1 Erik 71 1
1 Eric 71 2
2 Sarah 70 1
2 Sarah 70 2
2 Sarah 72 3
3 Bill 76 2
3 Bill 76 3
[UPLOADS TABLE]
UploadID UploadTime
1 3/09/2011
2 4/01/2011
3 4/11/2011
然而，这对我来说似乎不是最佳解决方案，因为有多少信息最终会在数据库中重复。有没有更好的方法来解决这个问题，每次上传时只保存更改？
I'm trying to setup a SQL server database for an ASP.NET MVC site that will both store the latest information as well as a history of all the changes to the data.
The information for my site comes from XML files which are uploaded by the user. The site parses through the XML and writes the contained information into the sites database. Elements in successive uploads may actually represent the same thing, but some data may have changed. Yet like I said before I want to keep track of every version.
The table bellow shows one way I was thinking of approaching this. I would create duplicate records for each item in each upload. New items that match those in previous uploads will be assigned the same ID, but each item would be assigned to a unique upload ID.
Upload 1: Erik and Sara are added
Upload 2: Erik renamed to Eric, Bill added
Upload 3: Sarah grew 2" taller, Eric was removed.
[PERSONS TABLE]
PersonID Name Height UploadID
1 Erik 71 1
1 Eric 71 2
2 Sarah 70 1
2 Sarah 70 2
2 Sarah 72 3
3 Bill 76 2
3 Bill 76 3
[UPLOADS TABLE]
UploadID UploadTime
1 3/09/2011
2 4/01/2011
3 4/11/2011
However, this doesn't seem like the optimal solution to me because of how much information ends up being duplicated in the database. Is there a better way to approach this where only the changes are saved with each upload?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为问题是您的 PERSONS 表不再仅包含有关 PERSONS 的信息。它还包含有关上传的信息。我要推荐的可能不会减少数据库的大小；但它会让它更容易理解和使用。
我认为您无法做更多的事情来使您的表更简单或数据库更小。正如您所看到的，您的 PERSONS_EDIT 表将是最大的表。您使用的数据库可能提供自动执行此操作的机制（某种事务记录或其他东西），但我从未使用过类似的东西，所以我将把它留给 Stackoverflow 上的其他人提出任何类似的建议，如果他们存在。如果 PERSONS_EDIT 表变得太大，您可以考虑删除超过一周/一个月/一年的条目。何时执行此操作由您决定。
进行此更改的其他一些原因是，在第一个表中，您必须使用 PersonId 和 UploadID 作为 Persons 表的主键。因此，要真正在应用程序中获取某个人的最新版本，您必须执行以下操作：按 id 选择人员，然后按他们的 UploadId 排序，并在每次执行操作时选择具有最大上传 ID 的那个一个人的交易。
另一个好处是您不必执行一堆花哨的 sql 来获取编辑历史记录。只需从 PERSONS_EDIT 表中执行 select * 即可。
I think the problem is is that your PERSONS table no longer contains just information about PERSONS. It also contains information on the updloads. What I'm going to recommend probably won't decrease the size of your database; but it will make it a little easier to understand and work with.
I don't think you can do much beyond this to make your tables simpler or your database smaller. As you can see, your PERSONS_EDIT table is going to be your largest table. The database you're using might provide mechanisms to do this automatically (some sort of transaction recording or something) but I've never used anything like that so I'll leave it to other people on Stackoverflow to make any suggestions like that if they exist. If the PERSONS_EDIT table gets too large, you can look at deleting entries that are over a week/month/year old. The decision on when to do that would be up to you.
Some other reasons for making this change, in your first table, you had to use PersonId and UploadID as a primary key to your persons table. So, to actually get the most recent version of a PERSON within your application, you would have had to do something where you select person by id, and then order by their UploadId and select the one with the largest upload Id EVERY TIME YOU DO A TRANSACTION ON ONE PERSON.
Another benefit is that you don't have to do a bunch of fancy sql to get your edit history. Just do a select * from the PERSONS_EDIT table.