On Fri, Oct 21, 2016 at 12:10 PM, Bhathiya Jayasekara <[email protected]> wrote:
> > On Wed, Oct 12, 2016 at 12:30 PM, Inosh Goonewardena <[email protected]> > wrote: > >> >> >> On Tue, Oct 11, 2016 at 2:40 PM, Uvindra Dias Jayasinha <[email protected] >> > wrote: >> >>> 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 >>> schema. >>> >>> 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 >>> TABLE_NAME VARCHAR >>> ENTITY_ID FK >>> DIFF BLOB >>> ACTION *VARCHAR* >>> ACTION_BY *VARCHAR* >>> ACTION_TIME *TIMESTAMP* >>> >>> >> +1 for having single audit table and recording a diff value. Using a >> structure with old/new column could become unmanageable when the changes >> happen to multiple columns. Also we are planning to do audit table updates >> from our code right? Database level triggers can be used in such cases but >> IMO we should avoid using triggers since it could affect the performance. >> > > Another reason to avoid database triggers is that if we use triggers, we > complemetely depend on trigger support in each database. So we may not be > able to support all databases, and we'll have to spend a lot of time and > effort to support auditing for different databases. > > Another important thing is that when we implement auditing from our code, > we need to make sure to do it in async manner to avoid performance > degradations. > Normally auditing with trigger is used in financial system where auditing is very important, but in our case we don't have much importance for auditing, so we can avoid using triggers. > > Thanks, > Bhathiya > > > >> >> >> >>> >> >>> [1] https://code.google.com/p/google-diff-match-patch/ >>> >>> On 11 October 2016 at 13:44, Sanjeewa Malalgoda <[email protected]> >>> 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 >>>> [email protected] >>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>> >>>> >>> >>> >>> -- >>> Regards, >>> Uvindra >>> >>> Mobile: 777733962 >>> >>> _______________________________________________ >>> Architecture mailing list >>> [email protected] >>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>> >>> >> >> >> -- >> Thanks & Regards, >> >> Inosh Goonewardena >> Associate Technical Lead- WSO2 Inc. >> Mobile: +94779966317 >> >> _______________________________________________ >> Architecture mailing list >> [email protected] >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >> > > > -- > *Bhathiya Jayasekara* > *Senior Software Engineer,* > *WSO2 inc., http://wso2.com <http://wso2.com>* > > *Phone: +94715478185 <%2B94715478185>* > *LinkedIn: http://www.linkedin.com/in/bhathiyaj > <http://www.linkedin.com/in/bhathiyaj>* > *Twitter: https://twitter.com/bhathiyax <https://twitter.com/bhathiyax>* > *Blog: http://movingaheadblog.blogspot.com > <http://movingaheadblog.blogspot.com/>* > > _______________________________________________ > Architecture mailing list > [email protected] > https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture > > -- Thanks Abimaran Kugathasan Senior Software Engineer - API Technologies Email : [email protected] Mobile : +94 773922820 <http://stackoverflow.com/users/515034> <http://lk.linkedin.com/in/abimaran> <http://www.lkabimaran.blogspot.com/> <https://github.com/abimarank> <https://twitter.com/abimaran>
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
