1 Reply Latest reply on Mar 2, 2009 8:35 AM by Adam Warski

    Audit Tables Referential Integrity

    Ido Ran Newbie

      Hi,
      I'm very excited that this project exists, I was just start implement my own auditing framework and it's not a walk in the park.

      I would like to make sure I understand how Envers store auditing data, in particular reference between tables.
      Say I have Client entity which has collection (one-to-many) of Orders.
      When I add new Client a record is added to both CLIENT and CLIENT_AUD.
      When I add two orders for that customer two records are added to both ORDER and ORDER_AUD.

      Now, that's the question: when I'm now updating the name of the customer, how many records are added to the audit tables?
      I see two options:

      1. Add one record to the CUSTOMER_AUD table to record the change and two records to the ORDER_AUD table to keep referential integrity and more important so it will be easy to select the customer and orders in that revision.

      2. Add one record to CUSTOMER_AUD table and that's it. Not touch any other table. Off course this will make the AuditReader implementation harder because you now need to find the most recent orders this customer has. The good side is that we are not rewriting information that can, pretty easily to calculated.

      From experiments I did with Envers it look like the first option is used.
      I just think to myself what will happen when I will have more than two related tables. It can make the database much much bigger for very minor change.

      Thank you,
      Ido.

        • 1. Re: Audit Tables Referential Integrity
          Adam Warski Master

          Hello,

          Envers uses option 2. If you modify a field in an entity, only that entity will be persisted, and no other referenced entities. With option 1, the size of the database would easily explode :).

          There is one small exception. If you have a bi-directional relation, and a certain configuration option isn't set to "false", if for example you add a new element to the collection, or remove an element, a revision for the other side will be generated also.

          So if you update the name of the customer, the "order" table should have a revision generated only if the order has a collection of related customers and the equals() in the customer depends on the name. That is quite logical - in such circumstances, the value of the field in "order" that corresponds to customer changes.

          But as I said, you can switch it off - look in the docs for more details.

          --
          Adam