EDU: Notes – Core: User: History

By Sol in Lab


  • 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)


  • 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