Requirements

• Create “paper trail” of changes made to user
• Role, grade, position, etc.
• Date-based
• Track any type of change for any type of user
• Students, teachers, administration, etc.
• Allow retrieval of full user history
• Allow retrieval of history of based on relationships/connections between content types
• Example: Retrieve history of a class by finding all teachers associated with class in their histories
• Allow queries for users based on properties in history
• Example: Find all students who have had a class with a specific teacher

Data Storage (Options)

1. User metadata (separate records)
• Store history in built-in wp_usermeta database table
• Methodology: Changing a property for a user
1. Save old property value in special meta key: hist_{property-name}
• {property-name} is property being changed
2. Set new property value in default meta key: {property-name}
• - Changes not dated
• Option: Could store date with old value as serialized data
• - Would need to parse/sort full query before results based on specific properties/values could be returned
• + Simple to query based on meta key
• Find all users with specified property/value combination
• ? Can user metadata store multiple items with same key for user? (ala post meta)
• A: Yes
2. User metadata (serialized data)
• Entire user history stored in single row for user in built-in wp_usermeta database table
• Data stored as serialized array
• Key: Timestamp of user property change
• Value: Object/array of properties/values
• - Must query all users’ histories before any connections can be made
• + Keeps wp_usermeta table cleaner
3. Separate database table
• User history stored in bespoke database table(s): user_history
• Structure (schema)
• id: Unique row ID
• user_id: User ID
• date: Timestamp of property/value change
• property: Property being set/changed
• value: New value of property
• Example: Student grade-level change
• id: Automatically set
• user_id: {user-id} (ID from wp_users table)
• date: Current date time
• property: “grade-level”
• value: 2
• ? Current user properties: How to manage/query? (Options)
1. Mirror current properties/values in user metadata
• History table used primarily for past history
• All current user data should be accessed from user metadata
• History updated when property changed/set
• + Can perform queries for current data using metadata (all students in class “x”, etc.)
• - Duplication of data: wp_usermeta/user_history
• Not too much of an issue because history only used for archival purposes (e.g. not validating current user properties)
2. Add current column to user history table
• Flag indicates row contains latest data for user property
• - Must unset previous row flagged as current when setting new data
• +Simple to query current data using where clause

  ...WHERE current = true

3. Use date column
• Query only latest matching rows
• Unique rows sorted by date column
• Will return most recent row of each property for user
• ? How to retrieve multiple properties with same key for user?
• Example: Student may currently be in multiple classes (and assigned to each class at different times)
• - Only most recently-set class would be returned for student

Properties with multiple values (Options)

Examples

• Student in multiple classes in current semester
• Teacher teaching multiple classes in current semester

1. Serialized property value
• Store data as array
• - Does not track granular changes
• Example: Single class changed while others remain
2. Multiple rows with same property
• Example: Separate row for each class that student is enrolled in
• + Current classes managed as user metadata
• History just for chronological record
• Dates of property changes “tells the story” of user’s changes