处理需要批准的记录更改的结构 [英] Structure to handle changes to records that require approval
问题描述
正在处理需要输入更改权限的数据输入Web应用程序。后端数据库是PostgreSQL 9.6。不需要保留更改历史记录（审核记录），只需要保留记录的原始副本和授权的完整副本即可。在检查其他问题时，建议的解决方案似乎有些过头。
Working on a data-entry web application, that requires changes to be authorised. The back-end database is PostgreSQL 9.6. There is no requirement for a change history (audit trail) to be kept, only a dirty copy of the record and an authorised clean copy. Checking other questions the suggested solutions seem rather overkill.
并使用链接这样的时态数据库解决方案，似乎
And using a temporal database solution like link, definatly seems overkill.
一个选择是每个字段都有两列，一个保留干净副本，另一个保留脏副本以及记录状态枚举字段。数据提供者的任何插入或修改将被写入脏字段，并且状态将适当更改。当管理员授权更改时，脏字段值将被复制到清除字段，并且状态将更改。数据的公共视图只会收集干净的字段并过滤出插入的记录。
One option would be to have two columns for each field, one holding the clean copy and the other dirty copy and a record status enum field. Any inserts or amendments by the data providers would be written to the dirty field and the status changed appropriately. When an admin authorises the changes, then the dirty field value is copied to the clean field and the status changed. The public view of the data would only pick up the clean fields and filter out inserted records.
这似乎有点小道理，而且不遵循规范化规则。但这似乎是满足要求的最简单的解决方案。使用单独的表保存更改时，处理父子关系（在父记录插入之前授权子记录插入）没有问题。
This seems a bit of a hack and does not follow rules of normalisation. But it does seem to be the simplest solution that meets the requirements. It does not have the problem with dealing with parent child relations (authorising a child record insert before a parent record insert) when using a separate table to hold the changes.
如果任何人都可以提出更好的设计，我将不胜感激。
If anybody could suggest a better design, I would be grateful.
小。该应用程序要求授权用户可以输入有关其项目的详细信息。任何更改或添加都必须先由管理员审核，然后才能生效。如前所述，目前无需保留更改历史记录。一个项目几乎总是由一个人编辑。
So to clarify the requirements a little. The application has the requirement that authorised users can enter details about their projects. Any changes or additions have to be reviewed by admins before they are allowed to be put live. As mentioned currently there is no need for history of changes to be kept. A project will nearly always be edited by one person.
使用PostgreSQL 9.6，Flask和SQLAlchemy用于ORM和HTML 5，jquery用于前端。
Using PostgreSQL 9.6, Flask with SQLAlchemy for the ORM and HTML 5 and jquery for the frontend.
希望使解决方案尽可能简单，尤其是使Web界面保持简单。
Would like to keep the solution as simple as possible, specifically keeping the web interface simple.
推荐答案
在您的Web应用程序中，具有适当权限的人将更改一个字段。
更改应转到单独的表，该表引用了应更改的列和状态列（如果更改为
该表应该为时间戳记增加1行，因为有1个以上的用户可以尝试更改同一字段，并且当然要有进行更改的用户ID /名称的列。 >
审阅者应该能够看到Web上的更改并批准。
批准将意味着更改该工作表中的状态列。
一个单独的脚本可以在该工作表中进行更改并更新主表。
需要指出的一件事：
In your web application someone with the proper rights would change a field.
The change should go to a separate table with a reference to the column that should be changed and a status column if the change is authorized or rejected.
The table should have 1 extra row for timestamp since more than 1 users may try to change the same field and of course a column for the user id/name who made the change.
The reviewer should be able to see the changes in the web and approve.
Approval would mean change of the status column in that work table.
A separate script could pick up changes in that work table and update main table.
One thing to point out:
不需要保留更改历史记录（审核记录） ，
There is no requirement for a change history (audit trail) to be kept,
根据我的经验，一旦您开始批准更改，这种要求很快就会发生/变化。您应该考虑到这一点
From my experience once you start with approving changes such requirements can come/change very fast. You should take that into consideration
这篇关于处理需要批准的记录更改的结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
