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.
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?
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?
- Use classic SQL "master-detail" relation
- Pros
- simple to understand for database developers with SQL background
- clean: direct definition for history record and its properties
- search performance: easy searching through history (can use indices)
- troubleshooting: easy access by administration tools (_ah/admin)
- Cons
- one-to-many relations are often not recommended to be implemented this way in GAE DB
- read performance: excessive number of record read operations to show long audit trail e.g. in details pane of a big records list.
- Pros
- Store history in a BLOB field (pickled python structures)
- Pros
- simple to implement and flexible
- read performance: very efficient
- Cons
- query performance: cannot search using indices
- troubleshooting: cannot inspect data by admin db viewer (_ah/admin)
- unclean: not so easy to understand/accept for SQL developers (they consider this ugly)
- Pros
- Store history in Expando's dynamic properties. E.g. for each field
fieldName
createhistory_fieldName_n
fields (where n=<0..N> is a number of history record)- Pros:
- simple: simple to implement and understand
- troubleshooting: can read all the history properties through admin interface
- read performance: one read operation to get the record
- Cons:
- search performance: cannot simply search through history records (they have different name)
- not too clean: number of properties may be confusing at first look
- Pros:
- Store history in some set of list fields in the main record. Eg. for each
fieldName
create afieldName_history
list field- Pros:
- clean: direct definition of history properties
- simple: easy to understand for SQL developers
- read performance: one read operation to get the record
- Cons:
- 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;
- troubleshooting: inspecting lists is difficult in admin db viewer
- Pros: