将更改存储在需要批准的数据库记录之前，最好的方法是什么？ [英] 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），进行更改时存储的datetime列;以及一个列存储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屋！