Hi, What about having one AUDIT table with below schema structure?
ENTRY_ID PK TABLE_NAME VARCHAR FIELD_NAME VARCHAR OLD_VALUE VARCHAR NEW_VALUE VARCHAR ACTION_BY VARCHAR ACTION_TIME VARCHAR It's rare, that, we have to update all the Columns in a single update. Also, this will reduce the number of AUDIT tables in the system. With this approach, we can easily find things like 1. Which tables were updated recently 2. Which tables have not been updated in the past year 3. Which tables have never been updated 4. All changes made by a specific user in a time period 5. Most active tables in a time period On Tue, Oct 11, 2016 at 11:50 AM, Uvindra Dias Jayasinha <uvin...@wso2.com> wrote: > *Context* > We have started to look into API Manager's DB design for C5 and want to > evaluate what was done in the past and see if there is room for improvement. > > This is specifically to talk about the below audit columns, > > CREATED_BY *VARCHAR* > CREATED_TIME *TIMESTAMP* > UPDATED_BY *VARCHAR* > UPDATED_TIME *TIMESTAMP* > > > that are currently present in many of the tables such as AM_API, > AM_APPLICATION, AM_SUBSCRIBER, AM_SUBSCRIPTION, etc. > > *Cons of current approach* > > 1. Since the columns are part of the entities definition, we can only > store a single value for UPDATED_BY and UPDATED_TIME for a given entity. > Currently we store only the latest values so we can actually keep track of > history. > > 2. Cant keep track of when an entity is deleted(Maybe we need this, maybe > we dont) > > 3. In order to truly be bale to audit we need to keep track of the changes > that were done to a given entity along with who changed it and when, but > this is not possible at the moment with our current design. > > *Pros of current approach* > > 1. Easy to manage data since audit columns are part of the entity > itself(No need for a separate table to store history and no need to deal > with growing historical audit data) > > > *Possible alternative* > > So things are much more simple now and easier to manage but does it really > achieve what it sets out to do? To keep track of auditing information we > ideally need to keep track of every single action that was performed on a > given entity(n number of actions for a given entity). So this requires a > separate table definition. I'm *NOT* proposing that we define a separate > audit table for every single entity in the DB, just for a few important > ones such as AM_API. So for example for > APIs we could have an audit table with columns like below, > > ENTRY_ID *INTEGER PRIMARY KEY* > API_ID *INTEGER* > ACTION *VARCHAR* > ACTION_BY *VARCHAR* > ACTION_TIME *TIMESTAMP* > > *Cons of new approach* > > 1. Data could grow for a given entity and may need to be managed separately > > *Pros of new approach* > > 1. Above design does not cater for keeping track of the actual change > that took place. I left that out on purpose because we dont support this > currently also, but having separate table means we have provision to add a > diff column and implement this feature in the future. > > 2. We can keep track of all the changes that the entity went through, who > did it and when(including deletions if we want to). > > I would like to here your thoughts on this. Is this worth exploring or > should we just do things the way we have always done? > > > -- > Regards, > Uvindra > > Mobile: 777733962 > > _______________________________________________ > Architecture mailing list > Architecture@wso2.org > https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture > > -- Thanks Abimaran Kugathasan Senior Software Engineer - API Technologies Email : abima...@wso2.com 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 Architecture@wso2.org https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
