草稿记录应该保存在单独的表中吗？
我们正在构建一个简单的基于网络的系统，由某人添加一条记录（例如 CMS 页面），该记录在显示在网站上之前会得到负责人的批准。
如果作者决定稍后编辑该页面，我们希望根据实时副本创建草稿，经批准后它将替换旧的实时页面。
我们考虑过进行完整的版本控制，但相信我们可以通过 1. 只是一份草稿，2. 只是一个实时，或 3. 一个草稿和一个实时来保持这个简单。
跨多个“事物”而不仅仅是页面需要此功能。
最后的问题是：您认为将这两条记录存储在同一个表中更好，还是镜像表更好？
我想这可能取决于但我不喜欢拥有两个具有相同结构的表的理想情况。稍微慢一点的操作（因为我们在显示数据时必须始终查询草稿）的权衡是否值得？
We're building a simple web based system whereby somebody adds a record, a CMS page for example, that gets approved by someone in charge before being shown on website.
If the author then decides to edit that page later, we want to create a draft based on the live copy, on approval it will replace the old live page.
We thought about doing complete version control but believe we can keep this simpler by just having 1. Just a draft, 2. Just a live, or 3. One draft and one live.
This functionality is required across multiple 'things' not just pages.
Finally the question: Do you think it would be better to store these two records in the same table, or would a mirror table be better?
I guess it probably depends but I don't like the ideal of having two tables with the same structure. Is the trade off for slightly slower operations (as we'll have to query out the drafts all the time when displaying the data) worth it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
当状态发生变化时将内容从一个表移动到另一个表是一个坏主意。
当您想要向工作流添加其他状态时，您必须添加更多表。
这只是状态变化——这就是关系数据库优化的目的。
一张表，多个状态是标准方法。
如果您发现事情慢得可怕——并且您可以证明基于状态的查询是全部原因——您可以诉诸“物化视图”或类似技术，其中状态更改（以及由此产生的移动）由关系型数据库管理系统。
每个状态一个表是一个坏主意。
您无法轻松添加状态。您还必须添加表格，这会很痛苦。此外，您必须使用新的表名称更新代码以反映新的工作流程。
如果状态只是一列，则添加新状态就是在代码中添加新值和新的 if 语句。状态更改只是更新，而不是“删除-插入”。
数据永远存在，每当用户有一个聪明的想法时，工作流程就会来来去去。不要因为他们想要改变工作流程而惩罚他们。
你不能轻易拥有子状态。许多状态机实际上是多个嵌套的状态机。添加具有每个状态表的子状态会创建更多具有更多规则的表。
如果状态只是一列，则嵌套子状态只是代码中带有新 if 语句的另一列。状态更改只是更新，而不是“删除-插入”。
您无法轻易拥有并行状态机。很多时候会有很多并行的状态码变化。有时会有手动工作流程（审批）和自动化工作流程（归档、复制到数据仓库等）。对于逐表和并行状态机，无法合理实现
如果每个状态只是一列，则并行状态机只是并行更新。
Moving stuff from table to table when there's a state change is a bad idea.
When you want to add additional states to the workflow, you have to add yet more tables.
It's just a state change -- that's what relational databases are optimized for.
One table, multiple states is the standard approach.
If you find that things are horrifyingly slow -- and you can prove that the state-based query is the entire cause -- you can resort to "materialized views" or similar technology where the state change (and resulting move) is handled by the RDBMS.
Table-per-state is a bad idea.
You can't easily add states. You have to add tables, also, making it painful. Further, you have to update code with the new table name(s) to reflect the new workflow.
If a state is just a column, adding new states is adding new values and new if-statements in the code. State changes are just updates, not "delete-insert".
Data lasts forever, workflows come and go every time a user has a clever idea. Don't punish them for wanting to change the workflow.
You can't easily have sub-states. Many state machines are actually multiple, nested, state machines. Adding a sub-state with table-per-state creates yet more tables with yet more rules.
If a state is just a column, a nested substate is just another column with new if-statements in the code. State changes are just updates, not "delete-insert".
You can't easily have parallel state machines. Many times there are many parallel status code changes. Sometimes there are manual workflows (approvals) and automated worflows (archiving, copying to the data warehouse, etc.) With table-per-state and parallel state machines, there's no way to implement it rationally
If each state is just a columns, parallel state machines are just parallel updates.
不。一种实体类型，一张表。
重新考虑的原因：
草稿记录的数量是实时记录的千分之一。
安全条件要求某些直接访问数据库的用户对 GRANT/REVOKE 级别的草稿或实时记录拥有某些权限，但对其他类型的记录没有权限。
安全条件要求某些直接访问数据库的用户对草稿或实时记录拥有某些
要考虑的第二种设计是一个用于 Items 的表，另一个用于 LiveItems 的表。第二个表仅包含活动项目的 ID。这样您就可以维护单表设计，但可以通过将单列表连接回主表来找到 LiveItems。
No. One entity type, one table.
Reasons to reconsider:
Draft records outnumber live records by a factor of thousands to one.
Security conditions require that some users who access the database directly have certain rights over draft or live records at the GRANT/REVOKE level but not over the other type of record.
A second design to consider would be one table for the Items and a second table for LiveItems. The second table contains only the IDs for the Items that are live. That way you're maintaining your single table design, but you can find the LiveItems by joining your one-column table back to the main table.
同意上面给出的所有评论：只有一张表。
使用 范围< /a>，您可以轻松地仅获取已发布的帖子或草稿。
我不会推荐它。
但是，如果您确实希望草稿和已发布条目有两种不同的模型，那么还有另一种解决方案： STI。
您将有两个模型：
任何草稿对象都取自 Post 表。
Type 参数使其成为帖子或草稿。
每当您想发布帖子时，您都必须执行以下操作：
Agreed with all the comments given above : only one table.
With the scopes, you can easily get only the published posts or the drafts.
I wouldn't recommand for it.
But if you really wish to have two different models for the drafts and published entries, there's an other solution though : STI.
You'd have two models :
Any Draft object is taken from the Post table.
The Type parameter makes it a post or a draft.
Whenever you want to publish a post you'd then have to do :
我刚刚为这样的用例制作了一个 gem。它将草稿存储在单独的表中：
https://github.com/ledermann/drafting
I just made a gem for such a use case. It stores drafts in a separate table:
https://github.com/ledermann/drafting