SQL Server“回收站”
我正在构建一个内容管理网站，其中还将包含其他功能。当管理员成员通过管理面板删除项目时，我希望将该项目移动到“回收站”30 天（然后自动删除）。
实现此功能的最佳方法是什么？
我的一个想法是在表中添加一个“已删除”位列，然后仅显示 WHERE Hide=0 的记录。然而，这意味着每次我从表中SELECT时都必须记住将这个条件设置到位。
我的另一个想法是建立第二个表，删除时记录将移至其中。但是，我的网站中使用了许多表，因此这意味着表的数量加倍，并且具有重复的表结构（这可能会在将来导致一致性问题）。
理想情况下，我希望有一个“RecycleBin”表，所有记录都移动到其中，但这可能包含数百列，以便能够存储来自所有不同表的数据。
如果有人有任何其他想法，我们将不胜感激。
谢谢。
I'm building a Content Management website which will also include other features. When an admin member deletes an item through the admin panel, I would like this item to be moved to a 'recycle bin' for 30 days (then automatically deleted).
What is the best way of implementing this feature?
One idea I had was to have a 'Deleted' bit column in my table, then only show records WHERE Hide=0. However this would mean having to remember to put this condition in place everytime I SELECT from the table.
Another Idea I had was to have a second table where the records would be moved to when deleted. However I have many tables being used in my website, so this would mean doubling the amount of tables, and having duplicate table structures (which could cause consistency issues in the future).
Ideally I would like to have a 'RecycleBin' table which all the records are moved to, but this could contain 100s of columns to be able to store data from all the different tables.
If anyone has any other ideas it would be most appreciated.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我知道的大多数实现都使用
deleted列（不是布尔值，而是时间戳，因此您可以知道它何时被删除）。是的，您需要将其添加到所有查询中，但也许您正在使用 ORM 层，因此您只需添加一次？我建议走这条路。维基百科对其文章的当前版本和存档版本使用多个表，但这是因为它们非常庞大，并且当前版本的请求比旧版本要频繁得多。
如果您想使用一个
RecycleBin表，您可能需要考虑序列化行并将它们放入value列中，而不是存储所有单独的列。当然，这仅在您不需要查询已删除项目的各个列的内容时才有效，因为这将非常昂贵。Most implementations I know use a
deletedcolumn (not a boolean, but a timestamp, so you can tell when it was deleted). Yes, you will need to add this to all your queries, but maybe you are using an ORM layer, so you only have to add it once? I would suggest going this way.Wikipedia uses multiple tables for the current and the archived versions of their articles, but that is because they are out-of-league huge, and the current versions are requested much more often than the older versions.
If you want to use one
RecycleBintable, you might want to consider serializing the rows and putting them in avaluecolumn instead of storing all individual columns. Of course, this only works if you don't need to query on the contents of individual columns of deleted items, since that will be very costly.使用 IsDeleted 列是一种很好的技术。
您可以将其与删除而不是触发器结合使用，后者将使用该标志而不是常规删除操作。您还可以用视图包装表，以便视图中仅公开非删除行。
Using an IsDeleted column is a good technique.
You can combine this with a delete instead of trigger which will use the flag instead of a regular delete operation. You can also wrap the table with views, so that only non-delete rows are exposed in the view.