*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
