如何设计具有撤消重做功能的 SQL 数据库？
我试图弄清楚如何设计我的数据库表以允许撤消重做。
假设您有一个具有以下结构的任务表：
id <int>
title <varchar>
memo <string>
date_added <datetime>
date_due <datetime>
现在假设在几天内多次登录，进行了多次编辑；但用户想要返回到其中一个版本。
- 您是否有一个单独的表来跟踪更改 - 或者 - 您是否会尝试将更改保留在任务表中（“幽灵”行，因为缺乏更好的术语）？
- 您会跟踪所有列还是仅跟踪每次更改的列？
如果重要的话，我正在使用 MySQL。另外，如果重要的话，我希望能够显示历史记录（ala Photoshop）并允许用户切换到任何版本。
额外问题：您会在更改时保存整个 memo 单元格还是会尝试仅保存增量？我问的原因是因为 memo 单元格可能很大，并且每个修订版可能只更改一个单词或字符。诚然，保存增量需要解析，但如果不经常进行撤消，那么节省空间而不是处理时间不是更好吗？
感谢您的帮助。
I'm trying to figure out how to design my DB tables to allow Undo-Redo.
Pretend you have a tasks table with the following structure:
id <int>
title <varchar>
memo <string>
date_added <datetime>
date_due <datetime>
Now assume that over a few days and multiple log-ins that several edits have taken place; but a user wants to go back to one of the versions.
- Would you have a separate table tracking the changes - or - would you try to keep the changes within the tasks table ("ghost" rows, for lack of a better term)?
- Would you track all of the columns or just the ones that changed each time?
If it matters, I'm using MySQL. Also, if it matters, I'd like to be able to show the history (ala Photoshop) and allow a user to switch to any version.
Bonus question: Would you save the whole memo cell on a change or would you try to save the delta only? Reason I ask is because the memo cell could be large and only a single word or character might be changed each revision. Granted, saving the delta would require parsing, but if undos aren't expected very often, wouldn't it be better to save space rather than processing time?
Thank you for your help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我会为您的任务表创建一个历史表。与任务相同的结构 + 一个名为 previousId 的新字段。这将保存之前的更改 ID，因此您可以来回执行不同的更改（撤消/重做）。
为什么需要新的历史表？原因很简单：不要用任务表之外的东西来超载任务表。
至于空间，在历史记录中，使用二进制格式而不是备忘录，并压缩要存储的文本内容。不要试图检测变化。您将遇到有错误的代码，这将导致挫败感和浪费时间......
优化：
更好的是，您可以在历史记录表中只保留三列：
1.taskId（任务的外键）
2. data - 二进制字段。在保存到历史记录表之前，创建一个仅包含已更改字段的 XML 字符串。
3. previousId（将帮助维护更改队列并允许来回导航）
至于数据字段，创建一个如下所示的 XML 字符串：
这基本上会告诉您，这次您仅更改了标题和 date_added 字段。
构建 XML 字符串后，如果需要，只需将其压缩并将其存储到历史表的数据字段中。
XML 还具有灵活性。如果您在任务表中添加/删除字段，则也不需要更新历史记录表。这样，任务表和历史表的结构就解耦了，因此您不需要每次更新两个表。
PS：不要忘记添加一些索引以快速浏览历史表。要索引的字段：taskId 和 previousId，因为您需要对此表进行快速查询。
希望这有帮助。
I would create a History table for your tasks table. Same structure as tasks + a new field named previousId. This would hold the previous change id, so you can go back an forth through different changes (undo/redo).
Why a new History table? For a simple reason: do not overload tasks table with things that it was not designed for.
As for space, in the History, instead of a Memo, use a binary format and zip the content of the text you want to store. Don't try to detect changes. You will run into a buggy code which will result in frustration and wasted time...
Optimization:
Even better, you may keep only three columns in History table:
1. taskId (foreign key to tasks)
2. data - a binary field. Before saving in the History table, create an XML string holding only the fields that have changed.
3. previousId (will help maintain a queue of changes and allow navigation back and forth)
As for data field, create an XML string like this:
This will basically tell you that this time you changed only the title and the date_added fields.
After the XML string is built, just zip it if you want and store it into History table's data field.
XML will also allow for flexibility. If you add / remove a field in tasks table, you don't need to update the History table, too. So this way the structure of the tasks table and History table are decoupled so you don't need to update two tables each time.
PS: don't forget to add some indexes to quickly navigate through the history table. Fields to be indexed: taskId and previousId as you will need fast queries against this table.
Hope this helps.
当我使用 SQL 执行类似类型的操作时，我总是使用第二个表来保存修订历史记录。这可以防止主表因版本而变得过大。理由是检索当前记录的情况几乎 100% 都会发生，查看历史记录和回滚（撤消）的情况非常罕见。
如果您只有一个 UNDO 或历史记录，那么跟踪表内可能就可以了。
您想要保存增量还是整个单元取决于预期的增长/使用情况。如果您愿意创建管理增量的逻辑，那将节省您的空间。如果事情没有真正创建新版本，我通常不会从那开始，（应用 YAGNI）
When I do similar types of things using SQL I always use a second table for revision history. This prevents your primary table from getting overly large with versions. The rationale is that retrieving the record that is current happens almost 100% of the time, viewing history and rolling back (undo) is very infrequent.
If you only have a single UNDO or history, then tracking in-table is probably fine.
Whether you want to save deltas or the entire cell depends on expected growth / usage. If you are comfortable creating the logic to manage deltas, that will save you space. If things don't really create new versions that often I wouldn't start with that, (applying YAGNI)
您可能希望以增量形式压缩修订版本，但您仍应保留完整的当前修订版本以便快速检索。
但是，较旧的到较新的增量需要大量处理，除非您有一些非增量可以作为基础。每次发生变化时，新到旧的增量都需要重新处理。因此，增量通常不会给您带来很多好处，但会带来更大的复杂性。
上次我查了一下，那是几年前的事了，MediaWiki，维基百科背后的软件，存储了全文和提供了一些使用 gzip 压缩旧版本的方法以节省空间，并为已删除的版本/页面提供了专用表
存档。他们的网站上有一个数据库布局的 ER 图，您可能会觉得有用。
You might want to compress revisions in delta form but you should still have the current revision in full for quick retrieval.
However, older to newer deltas require lots of processing unless you have some non-delta to base on. Newer to older deltas require reprocessing every time something changes. So deltas usually do not get you many benefits but greater complexity.
Last I checked, which is some years ago, MediaWiki, the software behind Wikipedia, stored full texts and provided some means to compress older revisions with gzip to save space and a dedicated table
archivefor deleted revisions / pages.Their website has an ER diagram of their database layout which you might find useful.