跟踪“历史”的良好设计方法是什么？ J2EE+MySQL Web 应用程序触发器、存储过程和应用程序逻辑中的记录数量？
在我们的应用程序中，用户可以编辑/删除墙上的某些项目 - 同一团队中的其他人可以查看这些项目。现在解决历史维护问题的最佳设计方法是什么？
以下是我想到的一些设计想法，但不知道哪种方法更好/更好：
- 为当前表创建一个重复的架构（或每个表一个重复的表）。在更新之前调用触发器。但我只希望它仅在“文本”发生变化而不是其他属性发生变化时触发。甚至不知道如何
- 创建一个“历史表” - 即一个全局表，其中的属性用于隔离历史记录所属的表/项目。
- 有一个异步组件，负责使用相应的更新来更新特殊表 - 更像是哈希映射类型的存储。但我不知道如何使用 Java/Mysql 来完成此操作，或者是否值得付出努力。创建异步组件并非易事
这两种方法中的第一种将使用触发器。但是，如果只是更新了记录的时间戳或其他配置相关字段，我不希望触发器执行任何操作。假设大约有 50-100 个并发用户，触发器是一个不错的选择吗？ （或者最好使用存储过程方法来执行此操作......不确定）。
这样设计的意图：我们希望基本上对更改/删除等进行一些数据分析，我们还希望检查“添加的新项目”，但这从时间戳记来看很容易，并且不会影响历史记录方面。我必须针对上述每一点进行不同的设计，并想知道如何最好地解决问题 - 即在企业应用程序中处理类似问题的最佳方法。我希望它由社区运行，以获得某些方法的一些建议/方向/优点。非常欢迎新的/其他更好的方法！
我已经浏览过一些关于 SO 的帖子，它们似乎是特定于数据库的，但不一定直接回答我的问题 - 他们告诉我第一个选项的方法，但不一定是一个好方法： 数据历史记录跟踪最佳实践和 如何跟踪数据库表中的数据更改
PS：I我没有使用任何 ORM，而是使用 Spring JDBC。我们决定 ORM 不值得......
In our application a user can edit/delete certain items on a wall - which are viewable by others on the same team. Now what's the best design approach to solving this issue of history maintenance?
Here are some design ideas that I have in mind but don't know which is a good/better approach to go with:
- Create a duplicate schema (or a duplicate table per table) for the current tables. Call a trigger before update for each of them. But I only want it to fire only if the 'text' changes and not other attributes. Don't even know how to go about this
- Create a 'history table' - i.e., a global table where an attribute is used to isolate which table/item that history record is for.
- Have an asynchronous component that is responsible for updating a special table(s) with the corresponding update - more like a hashmap kind of storage. But I don't know how could this be done with Java/Mysql or is it even worth the effort. Creating asynchronous components are non-trivial
The first of the two approaches would use triggers. But then I don't want the trigger to do anything if just the timestamp or other configuration related fields have gotten updated for a record. Assuming about 50-100 concurrent users, would triggers be a good option? (or is it better to go with a stored procedure approach to do this...not sure).
Intention of such a design: We wish to basically perform some data analysis on the changes/deletions etc., We also wish to check for 'new items added' but that's easy from the timestamp and doesn't affect the history aspect. I'll have to design for each of the above points differently and wanted to know how best to approach the problem - i.e., best way to handle something like that in enterprise applications. I wanted it to run it by the community to get some suggestions/directions/pro-cons for some of the approaches. New/other better approaches are more than welcome!
I've gone throug some posts on SO and they seem to be DB specific but don't necessarily answer my question directly - they tell me the how for the first option but not necessarily whether it is a good way to go about it:
Data history tracking best practice and
How to track data changes in a database table
PS: I'm not using any ORM, but rather going with Spring JDBC. It was decided an ORM was not worth it...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可能会问的是所谓的审计跟踪。
最好的方法是维护一个单独的审计跟踪表来维护操作（添加/删除/修改）。
在我之前工作的一些应用程序中，我使用过这样的表，它们通过原始表的外键引用。
如果发生删除，主表中的记录将被软删除，以便保留审计跟踪引用。
清除机制根据预先确定的时间间隔清除（级联删除）主数据库和审计跟踪。
我结合使用了应用程序逻辑和 sql 查询。没有存储过程。
What you probably is asking is called an audit trail.
The best way is to maintain a separate audit trail table for maintaining actions (add/delete/modify).
In some of the applications I have worked earlier, I have used such tables, which are referenced by a foreign key to the original table.
in case of an delete the record in the master table is soft deleted, so that the audit trail reference remains.
A purge mechanism clears (cascade delete) the master and the audit trail based on a pre-decided interval.
I used a combination of app-logic and sql queries. no stored procs.
使用 java 将记录记录到历史表的重复模式中。维护 java 比维护触发器更容易。
记录历史记录时，无需检查差异。
应在验证阶段检查差异
如果记录尚未更新，则阻止保存。
Log the records into a duplicate schema for history tables using java. It is easier to maintain java than to maintain triggers.
When logging history, it is not necessary to check the differences.
The differences should be checked during the validation phase to
prevent a save if the record has not been updated.