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?
我需要将用户输入的更改存储到特定表中,但在管理用户查看和批准之前不会显示这些更改。虽然这些更改仍处于暂挂状态,但我仍会显示旧版本的数据。存储这些变化等待批准的最佳方法是什么?
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.
我想过几种方法,但无法弄清楚什么是最好的方法。这是一个非常小的网络应用程序。一种方法是使PendingChanges表模仿其他表的模式,然后一旦批准更改,我就可以用信息更新真实表。另一种方法是进行某种记录版本控制,其中我在表中存储多个版本的数据,然后始终使用已标记为已批准的最高版本号来提取记录。这将限制额外表的数量(我需要为多个表执行此操作),但每次我提取一组记录以确保获得正确的记录时,我需要进行额外的处理。
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.
更新:只是为了澄清,在这种特殊情况下,我对历史数据不感兴趣。我只需要某种方式来批准用户在网站上发布之前所做的任何更改。因此,用户将编辑他们的“配置文件”,然后管理员将查看该修改并批准它。一旦获得批准,它将成为显示的值,并且不需要保留旧版本。
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.
有人试过下面的解决方案,你在任何需要在特殊的PendingChanges表中跟踪它们的表中存储挂起的更改吗?每个记录都有一个列,表示更改所针对的表,可能存储将要更改的记录的id的列(如果是新记录则为null),更改时存储的日期时间列,以及用于存储已更改记录的xml的列(可以序列化我的数据对象)。由于我不需要历史记录,在批准更改后,将更新真实表并删除PendingChange记录。
Any thoughts about that method?
有关该方法的任何想法?
8 个解决方案
#1
9
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.
如果您需要保存历史数据以显示随时间的变化,我会使用单独的历史数据表,一旦获得批准,即可更新实时数据。它只是全面清洁。
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.
如果您有很多具有此机制但不需要保留历史记录的数据类型,我建议使用常见的队列来查看待处理的项目,比如存储为xml。这将允许管理员只读取一个表,并使您能够相当容易地将此功能添加到系统中的任何表。
#2
19
Definitely store them in the main table with a column to indicate whether the data is approved or not.
绝对将它们存储在主表中,并带有一列,以指示数据是否被批准。
When the change is approved, no copying is required. The extra work to filter the unapproved data is the sort of thing databases are supposed to do, when you think about it. If you index the approved column, it shouldn't be too burdensome to do the right thing.
更改得到批准后,无需复制。过滤未经批准的数据的额外工作是数据库在您考虑它时应该做的事情。如果您对批准的列进行索引,那么做正确的事情不应该太繁琐。
#3
4
I work in a banking domain and we have this need - that the changes done by one user must only be reflected after being approved by another. The design we use is as below
我在银行业领域工作,我们有这种需求 - 一个用户所做的更改必须在被另一个用户批准后才能反映出来。我们使用的设计如下
- Main Table A
- Another Table B that stores the changed record (and so is exactly similar to the first) + 2 additional columns (an FKey to C and a code to indicate the kind of change)
- A third table C that stores all such records that need approval
- A fourth table D that stores history (you probably don't need this).
主表A.
另一个表B存储更改的记录(因此与第一个完全相似)+ 2个附加列(FKey到C和代码表示更改的类型)
第三个表C,存储需要批准的所有此类记录
存储历史的第四个表D(您可能不需要这个)。
I recommend this approach. It handles all scenarios including updates and deletions very gracefully.
我推荐这种方法。它非常优雅地处理所有方案,包括更新和删除。
#4
3
Given the SOx compliance movement that has been shoved in the face of most publically traded companies, I've had quite a bit of experience in this area. Usually I have been using a separate table with a time stamped pending changes with some sort of flag column. The person in charge of administration of this data gets a list of pending changes and can choose to accept or not to accept. When a piece of data gets accepted, I use triggers to integrate the new data into the table. Though some people don't like the trigger method and would rather code this into the stored procs. This has worked well for me, even in rather large databases. The complexity can get a little difficult to deal with, especially in dealing with a situation where one change directly conflicts with another change and what order to process these changes in. The table holding the request data can never be able to be deleted, since it holds the "bread crumbs" so to speak that are required in case there is a need to trace back what happened in a particular situation. But in any approach, the risks need to be assessed, such as what I mentioned with the conflicting data, and a business logic layer needs to be in place to determine the process in these situations.
鉴于大多数公开交易公司面临的SOx合规运动,我在这方面有相当多的经验。通常我一直在使用一个单独的表,其中带有时间戳的挂起更改,带有某种标志列。负责管理此数据的人员会获得待处理更改的列表,并可以选择接受或不接受。当一块数据被接受时,我使用触发器将新数据集成到表中。虽然有些人不喜欢触发器方法,但宁愿将其编码到存储过程中。这对我来说效果很好,即使在相当大的数据库中也是如此。复杂性可能有点难以处理,特别是在处理一个更改与另一个更改直接冲突的情况以及处理这些更改的顺序时。持有请求数据的表永远无法删除,因为它持有“面包屑”可以说,如果需要追溯在特定情况下发生的事情,则需要这些面包屑。但在任何方法中,都需要评估风险,例如我提到的冲突数据,并且需要有一个业务逻辑层来确定这些情况下的流程。
I personally don't like the same table method, because in the cases of data stores that are constantly being changed, this extra data in a table can unnecessarily bog down the request on the table, and would require a lot more detail to how you are indexing the table and your execution plans.
我个人不喜欢相同的表方法,因为在数据存储不断被更改的情况下,表中的这些额外数据可能会不必要地使表上的请求陷入困境,并且需要更多详细信息正在索引表和执行计划。
#5
2
I would create a table with an flag and create a view like
我会创建一个带有标志的表并创建一个类似的视图
CREATE OR REPLACE VIEW AS
SELECT * FROM my_table where approved = 1
It can help to separate dependencies between the aprovement and the queries. But may be is not the best idea if need to make updates to the view.
它可以帮助分离aprovement和查询之间的依赖关系。但如果需要对视图进行更新,可能不是最好的主意。
Moving records might have some performance considerations. But Partitioned tables could do something quite similar.
移动记录可能有一些性能考虑因素。但分区表可以做一些非常相似的事情。
#6
1
As this is a web app i'm going to assume there are more reads than writes, and you want something reasonably fast, and your conflict resolution (i.e out of order approvals) results in the same behaviour -- latest update is the one that is used.
由于这是一个Web应用程序,我将假设有更多的读取而不是写入,并且您想要一些合理的快速,并且您的冲突解决(即无序批准)会导致相同的行为 - 最新更新是一个用来。
Both of the strategies you propose are similar in they both hold one row per change set, have to deal with conflicts etc, the only difference being whether to store the data in one table or two. Given the scenario, two tables seems the better solution for performance reasons. You could also solve this with the one table and a view of the most recent approved changes if your database supports it.
你提出的两种策略都是相似的,它们每个变更集都有一行,必须处理冲突等,唯一的区别是是否将数据存储在一个或两个表中。鉴于这种情况,出于性能原因,两个表似乎是更好的解决方案。如果数据库支持,您还可以使用一个表和最近批准的更改视图来解决此问题。
#7
1
Yet another idea would be to have three tables.
另一个想法是有三张桌子。
- One would be the main table to hold the original data.
- The second would hold the proposed data.
- The third would hold the historical data.
一个是保存原始数据的主表。
第二个将保留提议的数据。
第三个将保留历史数据。
This approach gives you the ability to quickly and easily roll back and also gives you an audit trail if you need it.
这种方法使您能够快速,轻松地回滚,并在需要时为您提供审计跟踪。
#8
0
I think the second way is the better approach, simply because it scales better to multiple tables. Also, the extra processing would be minimal, as you can create an index to the table based on the 'approved' bit, and you can specialize your queries to either pull approved (for viewing) or unapproved (for approving) entries.
我认为第二种方法是更好的方法,因为它可以更好地扩展到多个表。此外,额外处理将是最小的,因为您可以根据“已批准”位创建表的索引,并且您可以将查询专门化为拉取批准(查看)或未批准(批准)条目。
#1
9
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.
如果您需要保存历史数据以显示随时间的变化,我会使用单独的历史数据表,一旦获得批准,即可更新实时数据。它只是全面清洁。
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.
如果您有很多具有此机制但不需要保留历史记录的数据类型,我建议使用常见的队列来查看待处理的项目,比如存储为xml。这将允许管理员只读取一个表,并使您能够相当容易地将此功能添加到系统中的任何表。
#2
19
Definitely store them in the main table with a column to indicate whether the data is approved or not.
绝对将它们存储在主表中,并带有一列,以指示数据是否被批准。
When the change is approved, no copying is required. The extra work to filter the unapproved data is the sort of thing databases are supposed to do, when you think about it. If you index the approved column, it shouldn't be too burdensome to do the right thing.
更改得到批准后,无需复制。过滤未经批准的数据的额外工作是数据库在您考虑它时应该做的事情。如果您对批准的列进行索引,那么做正确的事情不应该太繁琐。
#3
4
I work in a banking domain and we have this need - that the changes done by one user must only be reflected after being approved by another. The design we use is as below
我在银行业领域工作,我们有这种需求 - 一个用户所做的更改必须在被另一个用户批准后才能反映出来。我们使用的设计如下
- Main Table A
- Another Table B that stores the changed record (and so is exactly similar to the first) + 2 additional columns (an FKey to C and a code to indicate the kind of change)
- A third table C that stores all such records that need approval
- A fourth table D that stores history (you probably don't need this).
主表A.
另一个表B存储更改的记录(因此与第一个完全相似)+ 2个附加列(FKey到C和代码表示更改的类型)
第三个表C,存储需要批准的所有此类记录
存储历史的第四个表D(您可能不需要这个)。
I recommend this approach. It handles all scenarios including updates and deletions very gracefully.
我推荐这种方法。它非常优雅地处理所有方案,包括更新和删除。
#4
3
Given the SOx compliance movement that has been shoved in the face of most publically traded companies, I've had quite a bit of experience in this area. Usually I have been using a separate table with a time stamped pending changes with some sort of flag column. The person in charge of administration of this data gets a list of pending changes and can choose to accept or not to accept. When a piece of data gets accepted, I use triggers to integrate the new data into the table. Though some people don't like the trigger method and would rather code this into the stored procs. This has worked well for me, even in rather large databases. The complexity can get a little difficult to deal with, especially in dealing with a situation where one change directly conflicts with another change and what order to process these changes in. The table holding the request data can never be able to be deleted, since it holds the "bread crumbs" so to speak that are required in case there is a need to trace back what happened in a particular situation. But in any approach, the risks need to be assessed, such as what I mentioned with the conflicting data, and a business logic layer needs to be in place to determine the process in these situations.
鉴于大多数公开交易公司面临的SOx合规运动,我在这方面有相当多的经验。通常我一直在使用一个单独的表,其中带有时间戳的挂起更改,带有某种标志列。负责管理此数据的人员会获得待处理更改的列表,并可以选择接受或不接受。当一块数据被接受时,我使用触发器将新数据集成到表中。虽然有些人不喜欢触发器方法,但宁愿将其编码到存储过程中。这对我来说效果很好,即使在相当大的数据库中也是如此。复杂性可能有点难以处理,特别是在处理一个更改与另一个更改直接冲突的情况以及处理这些更改的顺序时。持有请求数据的表永远无法删除,因为它持有“面包屑”可以说,如果需要追溯在特定情况下发生的事情,则需要这些面包屑。但在任何方法中,都需要评估风险,例如我提到的冲突数据,并且需要有一个业务逻辑层来确定这些情况下的流程。
I personally don't like the same table method, because in the cases of data stores that are constantly being changed, this extra data in a table can unnecessarily bog down the request on the table, and would require a lot more detail to how you are indexing the table and your execution plans.
我个人不喜欢相同的表方法,因为在数据存储不断被更改的情况下,表中的这些额外数据可能会不必要地使表上的请求陷入困境,并且需要更多详细信息正在索引表和执行计划。
#5
2
I would create a table with an flag and create a view like
我会创建一个带有标志的表并创建一个类似的视图
CREATE OR REPLACE VIEW AS
SELECT * FROM my_table where approved = 1
It can help to separate dependencies between the aprovement and the queries. But may be is not the best idea if need to make updates to the view.
它可以帮助分离aprovement和查询之间的依赖关系。但如果需要对视图进行更新,可能不是最好的主意。
Moving records might have some performance considerations. But Partitioned tables could do something quite similar.
移动记录可能有一些性能考虑因素。但分区表可以做一些非常相似的事情。
#6
1
As this is a web app i'm going to assume there are more reads than writes, and you want something reasonably fast, and your conflict resolution (i.e out of order approvals) results in the same behaviour -- latest update is the one that is used.
由于这是一个Web应用程序,我将假设有更多的读取而不是写入,并且您想要一些合理的快速,并且您的冲突解决(即无序批准)会导致相同的行为 - 最新更新是一个用来。
Both of the strategies you propose are similar in they both hold one row per change set, have to deal with conflicts etc, the only difference being whether to store the data in one table or two. Given the scenario, two tables seems the better solution for performance reasons. You could also solve this with the one table and a view of the most recent approved changes if your database supports it.
你提出的两种策略都是相似的,它们每个变更集都有一行,必须处理冲突等,唯一的区别是是否将数据存储在一个或两个表中。鉴于这种情况,出于性能原因,两个表似乎是更好的解决方案。如果数据库支持,您还可以使用一个表和最近批准的更改视图来解决此问题。
#7
1
Yet another idea would be to have three tables.
另一个想法是有三张桌子。
- One would be the main table to hold the original data.
- The second would hold the proposed data.
- The third would hold the historical data.
一个是保存原始数据的主表。
第二个将保留提议的数据。
第三个将保留历史数据。
This approach gives you the ability to quickly and easily roll back and also gives you an audit trail if you need it.
这种方法使您能够快速,轻松地回滚,并在需要时为您提供审计跟踪。
#8
0
I think the second way is the better approach, simply because it scales better to multiple tables. Also, the extra processing would be minimal, as you can create an index to the table based on the 'approved' bit, and you can specialize your queries to either pull approved (for viewing) or unapproved (for approving) entries.
我认为第二种方法是更好的方法,因为它可以更好地扩展到多个表。此外,额外处理将是最小的,因为您可以根据“已批准”位创建表的索引,并且您可以将查询专门化为拉取批准(查看)或未批准(批准)条目。