数据更新审批的DB设计
我正在开发一个项目，我们需要让某些用户输入或更新的数据在添加到“实时数据”之前经历待处理状态。
在准备数据时，用户可以保存不完整的记录。当数据处于待处理状态时，我们不希望数据影响对编辑实时数据的用户施加的规则，例如，处理实时数据的用户在输入已存在的相同数据时不应遇到唯一的约束。待处理状态。
我设想数据更新集将被分组为“数据提交”，并且当有人对提交进行质量控制时，数据将被重新验证和更正/拒绝/批准。
我考虑过关于存储数据的两种场景：
1）将挂起的状态数据与实时数据保存在同一个表中，但添加一个标志来指示其状态。我可以看到这里存在必须删除约束或使必填字段可为空以支持“不完整”状态数据的问题。然后存在如何处理更新现有数据的问题，您必须添加一个新行进行更新并将其链接回现有的“实时”行。这对我来说似乎有点混乱。
2) 添加镜像活动表的新表并将数据存储在其中，直到获得批准。这将使我能够完全控制现有的活动表，而“待处理”表可以被用户认为他想放入其中的任何内容滥用。这样做的缺点是我最终会在数据库中得到很多额外的表/SP。我正在考虑的另一个问题是用户如何在两个记录之间进行链接，由此链接到的记录可能是活动表中的记录或待处理表中的记录，但我想在这种情况下，您始终可以获取该记录的副本链接记录并将其视为更新？
这两种解决方案似乎都是完美的，但第二个对我来说似乎是更好的选择 - 还有第三种解决方案吗？
I'm working on a project where we need to have data entered or updated by some users go through a pending status before being added into 'live data'.
Whilst preparing the data the user can save incomplete records. Whilst the data is in the pending status we don't want the data to affect rules imposed on users editing the live data e.g. a user working on the live data should not run up against a unique contraint when entering the same data that is already in the pending status.
I envisage that sets of data updates will be grouped into a 'data submission' and the data will be re-validated and corrected/rejected/approved when someone quality control the submission.
I've thought about two scenarios with regards to storing the data:
1) Keeping the pending status data in the same table as the live data, but adding a flag to indicate its status. I could see issues here with having to remove contraints or make required fields nullable to support the 'incomplete' status data. Then there is the issue with how to handle updating existing data, you would have to add a new row for an update and link it back to existing 'live' row. This seems a bit messy to me.
2) Add new tables that mirror the live tables and store the data in there until it has been approved. This would allow me to keep full control over the existing live tables while the 'pending' tables can be abused with whatever the user feels he wants to put in there. The downside of this is that I will end up with a lot of extra tables/SPs in the db. Another issue I was thinking about was how might a user link between two records, whereby the record linked to might be a record in the live table or one in the pending table, but I suppose in this situation you could always take a copy of the linked record and treat it as an update?
Neither solutions seem perfect, but the second one seems like the better option to me - is there a third solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
你的选项 2 听起来是最好的主意。如果您想使用引用完整性以及通过 DBMS 获得的所有好处，则不能将待处理的数据放在同一个表中。但是不需要非结构化数据 - 待处理数据仍然是结构化的，并且您可能希望数据库在执行规则方面发挥作用，即使是在这些数据上。即使您没有这样做，待处理的数据也非常适合标准表结构。
一组单独的表格听起来是正确的答案。您可以将要更改的行的主键放入待处理表中，以便您知道正在编辑哪个项目，或者正在链接到哪个项目。
我不完全了解您的情况，因此这可能不合适，但一个想法是有一个单独的表来存储正在进行的批量编辑，因为这样您就可以对批次进行质量控制，或将批次提交给居住。每个挂起的表都可以有一个批次键，以便您知道它属于哪个批次。您必须找到一种方法来控制对同一行的多个待处理编辑（如果您愿意），但这似乎不是一个很难解决的问题。
我不确定这是否合适，但可能值得研究“主数据管理”工具，例如 SQL Server 的主数据服务。
Your option 2 very much sounds like the best idea. If you want to use referential integrity and all the nice things you get with a DBMS you can't have the pending data in the same table. But there is no need for there to be unstructured data- pending data is still structured and presumably you want the db to play its part in enforcing rules even on this data. Even if you didn't, pending data fits well into a standard table structure.
A separate set of tables sounds the right answer. You can bring the primary key of the row being changed into the pending table so you know what item is being edited, or what item is being linked to.
I don't know your situation exactly so this might not be appropriate, but an idea would be to have a separate table for storing the batch of edits that are being made, because then you can quality control a batch, or submit a batch to live. Each pending table could have a batch key so you know what batch it is part of. You'll have to find a way to control multiple pending edits to the same rows (if you want to) but that doesn't seem too tricky a problem to solve.
I'm not sure if this fits but it might be worth looking into 'Master Data Management' tools such as SQL Server's Master Data Services.
“工作单元” 是“数据提交”的好名字。
您可以将其序列化到不同的位置，例如（非关系）面向文档的数据库，并且仅在批准后保存到关系数据库。
取决于仍有多少实时数据约束需要应用于未批准的数据。
'Unit of work' is a good name for 'data submission'.
You could serialize it to a different place, like (non-relational) document-oriented database, and only save to relational DB on approval.
Depends on how many of live data constraints still need to apply to the unapproved data.
我认为第二种选择更好。要管理它，您可以使用视图，它将包含两个表，并且您可以通过视图使用此结构。
另一个好的方法是在单独的表中使用 XML 列来存储必要的数据（因为列的数量/名称未知）。您可以仅创建一个包含 XML 列和“类型”列的表来确定此文档与哪个表相关。
I think second option is better. To manage this, you can use View which will contain both tables and you can work with this structure through view.
Another good approach is to use XML column in a separate table to store necessary data(because of unknown quantity/names of columns). You can create just one table with XML column ad column "Type" do determine which table this document is related with.
第一个场景看起来不错。
在表中添加状态列。无需删除可为空约束，只需添加一个函数来根据标志检查所需字段，如如果标志为 1（不完整）则允许为空，否则不允许。
关于第二个问题，您想追加数据还是更新整个数据。
First scenerio seems to be good.
Add Status column in the table.There is no need to remove Nullable constraint just add one function to check the required fields based on flag like If flag is 1(incomplete) Null is allowed otherwise Not allowed.
regarding second doubt do you want to append the data or update the whole data.