0

In our web application we have a MySQL table that stores details of notifications that are sent to users. These notifications, once read by the user, are flagged as 'dismissed' and unlikely to be viewed again, but we can't permanently delete them. There will be many notifications, so we need to move them out of the the 'live' table periodically . We are using Hibernate Envers. There seem to be two strategies we could use:

  1. Delete 'dismissed' notifications from the 'live' table periodically; these will be archived via Hibernate Envers in a Notification_AUD table. This makes it straightforward to find obsolete notifications if we need, and fits in with our auditing strategy, but over time the Notification_AUD table will grow massive.

  2. Maintain tables that are generated periodically that store stale notifications over a date range. E.g., Notifications_2013_01,Notifications_2013_02 etc. This has the advantage of not letting tables ever grow massive, but defeats the purpose of using a library such as Hibernate Envers, in that we're rolling our own solution. Furthermore Hibernate doesn't seem to support very well the concept of mapping a single entity class to multiple tables, so we'd need to use native MySQL queries, or perhaps some table of metadata to be able to find the correct table to look up notifications over a given date range, for example.

If anyone has any wisdom to impart from implementing anything similar, it would be great to hear from you, or an alternative strategy that worked for you.

Thanks Richard

otter606
  • 335
  • 1
  • 4
  • 21
  • 2
    Option 3: Partition the Notification_AUD table. Read chapter 18 or the MySQL manual for partitioning info. – DwB Mar 08 '13 at 15:16
  • Thanks! I guess I'd never got that far in the manual.... that seems a better approach. – otter606 Mar 08 '13 at 16:38
  • Or maybe when the notification is read by the user, you can delete is right away? The history would be stored in the _AUD table anyway. You can then periodically clean the _AUD tables by removing revisions with a timestamp older than x. This should delete any references by cascade. – adamw Mar 09 '13 at 12:40

0 Answers0