Databases
StackOverflow2013
Postsdbo
POImplementing efficient audit trail of record changes in Google App Engine - design patterns
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POImplementing efficient audit trail of record changes in Google App Engine - design patterns
    text
    copied!<p>I have a quite common design problem: I need to implement a history log (audit trail) for records in Google App Engine. The history log has to be structured, i.e I cannot join all changes into some free-form text and store in string field. </p> <p>I've considered the following options for the history model and, after noticing performance issues in option #1, I've chosen to implement option #3. But have stil some doubts if this solution is efficient and scalable. For instance: is there a risk that performance will degrade significantly with increased number of dynamic properties in option #3? </p> <p>Do you have some deeper knowledge on the pros/cons for each option or could suggest other audit trail design patterns applicable for Google App Engine DB characteristics?</p> <ol> <li>Use classic SQL "master-detail" relation <ul> <li>Pros <ul> <li>simple to understand for database developers with SQL background</li> <li>clean: direct definition for history record and its properties</li> <li>search performance: easy searching through history (can use indices)</li> <li>troubleshooting: easy access by administration tools (_ah/admin)</li> </ul></li> <li>Cons <ul> <li>one-to-many relations are often not recommended to be implemented this way in GAE DB</li> <li>read performance: excessive number of record read operations to show long audit trail e.g. in details pane of a big records list.</li> </ul></li> </ul></li> <li>Store history in a BLOB field (pickled python structures) <ul> <li>Pros <ul> <li>simple to implement and flexible</li> <li>read performance: very efficient</li> </ul></li> <li>Cons <ul> <li>query performance: cannot search using indices</li> <li>troubleshooting: cannot inspect data by admin db viewer (_ah/admin) </li> <li>unclean: not so easy to understand/accept for SQL developers (they consider this ugly)</li> </ul></li> </ul></li> <li>Store history in Expando's dynamic properties. E.g. for each field <code>fieldName</code> create <code>history_fieldName_n</code> fields (where n=&lt;0..N> is a number of history record) <ul> <li>Pros: <ul> <li>simple: simple to implement and understand</li> <li>troubleshooting: can read all the history properties through admin interface</li> <li>read performance: one read operation to get the record</li> </ul></li> <li>Cons: <ul> <li>search performance: cannot simply search through history records (they have different name)</li> <li>not too clean: number of properties may be confusing at first look</li> </ul></li> </ul></li> <li>Store history in some set of list fields in the main record. Eg. for each <code>fieldName</code> create a <code>fieldName_history</code> list field <ul> <li>Pros: <ul> <li>clean: direct definition of history properties</li> <li>simple: easy to understand for SQL developers</li> <li>read performance: one read operation to get the record</li> </ul></li> <li>Cons: <ul> <li>search performance: can search using indices only for records which whenever had some value and cannot search for records having combination of values at some particular time; </li> <li>troubleshooting: inspecting lists is difficult in admin db viewer</li> </ul></li> </ul></li> </ol>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload