存储对需要批准才能可见的数据库记录的更改的最佳方法是什么? [英] What's the best way to store changes to database records that require approval before being visible?

问题描述

我需要将用户输入的更改存储到特定表中，但在管理用户查看和批准之前不显示这些更改.虽然这些更改仍处于待处理状态，但我仍会显示旧版本的数据.存储这些等待批准的更改的最佳方式是什么?

I need to store user entered changes to a particular table, but not show those changes until they have been viewed and approved by an administrative user. While those changes are still in a pending state, I would still display the old version of the data. What would be the best way of storing these changes waiting for approval?

我想了好几种方法，但不知道什么是最好的方法.这是一个非常小的网络应用程序.一种方法是拥有一个模仿另一个表模式的 PendingChanges 表，然后一旦更改获得批准，我就可以使用信息更新真实表.另一种方法是进行某种记录版本控制，我将数据的多个版本存储在表中，然后始终提取已标记为已批准的最高版本号的记录.这将限制额外表的数量(我需要为多个表执行此操作)，但需要我在每次提取一组记录时进行额外处理以确保我得到正确的记录.

I have thought of several ways, but can't figure out what is the best method. This is a very small web app. One way would be to have a PendingChanges table that mimics the other table's schema, and then once the change is approved, I could update the real table with the information. Another approach would be to do some sort of record versioning where I store multiple versions of the data in the table and then always pull the record with the highest version number that has been marked approved. That would limit the number of extra tables (I need to do this for multiple tables), but would require me to do extra processing every time I pull out a set of records to make sure I get the right ones.

对这些方法或其他可能有用的方法有任何个人经验吗?

Any personal experiences with these methods or others that might be good?

更新:澄清一下，在这种特殊情况下，我对历史数据不太感兴趣.我只需要某种方式来批准用户在网站上上线之前所做的任何更改.因此，用户将编辑他们的个人资料"，然后管理员将查看该修改并批准它.审核通过后即成为显示值，旧版本无需保留.

Update: Just to clarify, in this particular situation I am not interested so much in historical data. I just need some way of approving any changes that are made by a user before they go live on the site. So, a user will edit their "profile" and then an administrator will look at that modification and approve it. Once approved, that will become the displayed value and the old version does not need to be kept.

有人尝试过下面的解决方案，您可以将任何表中的待处理更改存储在特殊的 PendingChanges 表中作为 XML 进行跟踪吗?每条记录都有一个列，说明更改针对哪个表，一个列可能存储将要更改的记录的 ID(如果是新记录，则为 null)，一个日期时间列用于存储更改的时间，以及用于存储更改记录的 xml 的列(可能会序列化我的数据对象).由于我不需要历史记录，因此在更改批准后，将更新真实表并删除 PendingChange 记录.

Anybody tried the solution below where you store pending changes from any table that needs to track them as XML in a special PendingChanges table? Each record would have a column that said which table the changes were for, a column that maybe stored the id of the record that would be changed (null if it's a new record), a datetime column to store when the change was made, and a column to store the xml of the changed record (could maybe serialize my data object). Since I don't need history, after a change was approved, the real table would be updated and the PendingChange record could be deleted.

对这种方法有什么想法吗?

Any thoughts about that method?

推荐答案

尺寸是你的敌人.如果您正在处理大量数据和大量行，那么将历史数据与当前数据混合在一起会打击您.如果在确保获得正确行的情况下加入其他数据，您也会遇到问题.

Size is your enemy. If you are dealing with lots of data and large numbers of rows, then having the historical mixed in with the current will hammer you. You'll also have problems if you join out to other data with making sure you've got the right rows.

如果您需要保存历史数据以显示随时间的变化，我会使用单独的历史表，一旦批准就会更新实时、真实数据.它只是全方位清洁.

If you need to save the historical data to show changes over time, I would go with the separate historical, table that updates the live, real data once it's approved. It's just all-around cleaner.

如果您有很多具有此机制但不需要保留历史记录的数据类型，我会建议使用公共队列表来查看待处理的项目，例如存储为 xml.这将只允许管理员读取一个表，并使您能够相当轻松地将此功能添加到系统中的任何表.

If you have a lot of datatypes that will have this mechanism but don't need to keep a historical record, I would suggest a common queue talbe for reviewing pending items, say stored as xml. This would allow just one table to be read by administrators and would enable you to add this functionality to any table in you system fairly easily.

这篇关于存储对需要批准才能可见的数据库记录的更改的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！