Thanks for the feedback, some interesting points were brought up

@Abimaran, the problem with maintaining a rigid structure like old/new
column is that if a user changes the value of 5 columns at a given time
that would mean 5 different inserts to the table, when in actual fact it
was a single transaction that took place when the user did the change and
saved. So its better to use a implementation like
google-diff-match-patch[1] to record the string diff between the values of
the columns before the change took place and after the update. Though we
dont need to worry about this implementation detail for now. The idea of
using a single table to store the history of all tables that will require
auditing sounds good.

@Sanjeewa, yes this would improve performance when trying to retreive the
LAST_UPDATED_TIME for a given entity.

Let me elaborate a bit on Sanjeewa's point. So there can be only one
CREATED_BY and CREATED_TIME for a given entity so that can remain as part
of the original entities schema. Having the LAST_UPDATED_TIME as part of
the original entities schema gives a performance advantage on checking if a
given entity has been modified since it was last checked. This is vital for
features such as ETags support for the REST API. So CREATED_BY,
CREATED_TIME, LAST_UPDATED_TIME can remain with the original entities

We can still use the master audit table(building on Abimarans idea) to
actually keep track of change history of a given entity, so that table
could look like this,

ENTRY_ID           PK
ENTITY_ID          FK
DIFF                  BLOB
ACTION            *VARCHAR*


On 11 October 2016 at 13:44, Sanjeewa Malalgoda <> wrote:

> I think we can manage audit table while still having CREATED_BY,
> CREATED_TIME,UPDATED_BY, UPDATED_TIME  in same tables. So with that
> approach we may never need to do table scan of audit table while fetching
> updates. So each updates will recorded in separate table while original
> table having all relevant information. WDYT?
> Thanks,
> sanjeewa.
> _______________________________________________
> Architecture mailing list


Mobile: 777733962
Architecture mailing list

Reply via email to