I have a web project that uses a database to store data that is used to generate tasks that would be processed for remote machines to alter that records and store new data. My problem here is that I have to store all that changes on each table but I don't need all these information. For example, a table A could have 5 fields but I only need 2 for historical purposes. Another table B could have 3 and I would have to add another one (date for example). Also, I don't need changes during daily task generation, only the most recent one.
我有一个Web项目，它使用数据库来存储数据，这些数据用于生成将为远程计算机处理的任务，以更改记录并存储新数据。我的问题是我必须在每个表上存储所有更改，但我不需要所有这些信息。例如，表A可以有5个字段但我只需要2个用于历史目的。另一个表B可能有3个，我将不得不添加另一个表（例如日期）。此外，我不需要在每日任务生成期间进行更改，只需要更新最新任务。
Which is the best way to maintain a change history? Someone told me that a good idea is having two tables, the A (B) table and another one called A_history (B_history) with the needed fields. This is actually what I'm doing, using triggers to insert into history tables but I don't feel comfortable with this approach. My project uses Spring (Spring-data, Hibernate and JPA) and if I change the DB (currently MySQL) I'd have to migrate triggers. Is there a good way to manage history records? Tables could be generated with Hibernate/JPA annotations.
哪种维护更改历史记录的最佳方法？有人告诉我，一个好主意是有两个表，A（B）表和另一个名为A_history（B_history）的表，带有所需的字段。这实际上就是我正在做的事情，使用触发器插入历史表，但我对这种方法感到不舒服。我的项目使用Spring（Spring-data，Hibernate和JPA），如果我更改数据库（当前是MySQL），我必须迁移触发器。有没有一种管理历史记录的好方法？可以使用Hibernate / JPA注释生成表。
If I maintain the two tables approach, can I add a method to the repository to fetch rows from current table and history table at once?
如果我维护两个表的方法，我可以向存储库添加一个方法来立即从当前表和历史表中获取行吗？
2 个解决方案
#1
1
For this pourpose there is a special Hibernate Envers project. See official documentation here. Just configure it, annotate necessary properties with @Audited annotation and that's all. No need for DB triggers.
对于这个pourpose，有一个特殊的Hibernate Envers项目。请参阅此处的官方文档只需配置它，使用@Audited注释注释必要的属性即可。不需要DB触发器。
One pitfall: if you want to have a record for each delete operation then you need to use Session.delete(entity) way instead of HQL "delete ...".
一个陷阱：如果你想为每个删除操作创建一个记录，那么你需要使用Session.delete（实体）方式而不是HQL“delete ...”。
EDIT. Also take a look into native auditing support of spring data jpa.
编辑。另请参阅弹簧数据jpa的本机审计支持。
#2
1
I am not a database expert. What I have seen them do boils down to a few ways of approach.
我不是数据库专家。我所看到的它们归结为几种方法。
1) They add a trigger to the transactional table that copies inserts and updates to a history table but not deletes. This means any queries that need to include history can be done from the history table since all the current info is there too.
1）它们向事务表添加一个触发器，用于复制对历史表的插入和更新，但不删除。这意味着任何需要包含历史记录的查询都可以从历史记录表中完成，因为所有当前信息都在那里。
- a) They can tag each entry in the history table with time and date and keep track of all the states of the original records.
- a）他们可以使用时间和日期标记历史记录表中的每个条目，并跟踪原始记录的所有状态。
- b) They can only keep track of the current state of the original record and then it settles when the original is deleted.
- b）他们只能跟踪原始记录的当前状态，然后在删除原件时结算。
2) They have a periodic task that goes around and copies data marked as deletable into the history table. It then deletes the data from the transactional table. Any queries in the transactional table have to make sure to ignore the deletable rows. Any queries that need history have to search both tables and merge the results.
2）他们有一个周期性的任务，并将标记为可删除的数据复制到历史表中。然后它从事务表中删除数据。事务表中的任何查询都必须确保忽略可删除的行。任何需要历史记录的查询都必须搜索两个表并合并结果。
3) If the volume of data isn't too large, they just leave everything in one table and mark some entries as historical. Queries have to ignore historical rows. Queries that include history are easy. This may slow down database access as the table grows to include many unused rows but that can sometimes be ameliorated by clever use of indexes.
3）如果数据量不是太大，他们只需将所有内容保留在一个表中，并将某些条目标记为历史记录。查询必须忽略历史行。包含历史记录的查询很容易。这可能会减慢数据库访问速度，因为表会增长以包含许多未使用的行，但有时可以通过巧妙地使用索引来改善这些行。
#1
1
For this pourpose there is a special Hibernate Envers project. See official documentation here. Just configure it, annotate necessary properties with @Audited annotation and that's all. No need for DB triggers.
对于这个pourpose，有一个特殊的Hibernate Envers项目。请参阅此处的官方文档只需配置它，使用@Audited注释注释必要的属性即可。不需要DB触发器。
One pitfall: if you want to have a record for each delete operation then you need to use Session.delete(entity) way instead of HQL "delete ...".
一个陷阱：如果你想为每个删除操作创建一个记录，那么你需要使用Session.delete（实体）方式而不是HQL“delete ...”。
EDIT. Also take a look into native auditing support of spring data jpa.
编辑。另请参阅弹簧数据jpa的本机审计支持。
#2
1
I am not a database expert. What I have seen them do boils down to a few ways of approach.
我不是数据库专家。我所看到的它们归结为几种方法。
1) They add a trigger to the transactional table that copies inserts and updates to a history table but not deletes. This means any queries that need to include history can be done from the history table since all the current info is there too.
1）它们向事务表添加一个触发器，用于复制对历史表的插入和更新，但不删除。这意味着任何需要包含历史记录的查询都可以从历史记录表中完成，因为所有当前信息都在那里。
- a) They can tag each entry in the history table with time and date and keep track of all the states of the original records.
- a）他们可以使用时间和日期标记历史记录表中的每个条目，并跟踪原始记录的所有状态。
- b) They can only keep track of the current state of the original record and then it settles when the original is deleted.
- b）他们只能跟踪原始记录的当前状态，然后在删除原件时结算。
2) They have a periodic task that goes around and copies data marked as deletable into the history table. It then deletes the data from the transactional table. Any queries in the transactional table have to make sure to ignore the deletable rows. Any queries that need history have to search both tables and merge the results.
2）他们有一个周期性的任务，并将标记为可删除的数据复制到历史表中。然后它从事务表中删除数据。事务表中的任何查询都必须确保忽略可删除的行。任何需要历史记录的查询都必须搜索两个表并合并结果。
3) If the volume of data isn't too large, they just leave everything in one table and mark some entries as historical. Queries have to ignore historical rows. Queries that include history are easy. This may slow down database access as the table grows to include many unused rows but that can sometimes be ameliorated by clever use of indexes.
3）如果数据量不是太大，他们只需将所有内容保留在一个表中，并将某些条目标记为历史记录。查询必须忽略历史行。包含历史记录的查询很容易。这可能会减慢数据库访问速度，因为表会增长以包含许多未使用的行，但有时可以通过巧妙地使用索引来改善这些行。