EDU: Notes – Management: Data Relationships

By Sol in Lab

Requirements

  • Link to/from anything
    • Content to Content
      • Same content type
      • Different content types
    • Content to User
    • User to Content
    • User to User
  • Define relationship abilities on content type/user role basis
    • Relationship type (see below)
    • What content types/roles items of this type can be associated with
  • Define relationship type
    • One to Many
    • Many to Many
    • Many to One
  • Track relationship changes (date/time of change, etc.)

Examples

User to Content

  • Teacher to Class (many/many)
  • Student to Class (many/many)

Content to Content

  • Class to Grade level (parent/child?) (many/one)

Implementation (Options)

  1. Custom DB Tables
    • Store relationships in custom tables in database
    • Tables
      • Relationship Types
        • Defines possible relationship types for current item
      • Relationships
        • Manages relationships between items
        • Item ID, Type (content, user, etc.), Relationship¬† ID, Relationship Type
    • - Custom tables require more management
    • + Control over data/handling
    • - Must build from ground up
    • + Better query precision for finding appropriate data
    • ? WP Taxonomy already has such functionality?
  2. Meta/Options
    • Store relationships for an item in WP meta table
      • postmeta, usermeta, etc.
    • Possible relationship types stored as option
    • Methodology
      • Store
        • Example: Teacher to Class (User to Content)
          • Relationship: many/many
        • Relationships (array)
          • Type: Content/User
          • ID: Class ID
          • Active: true
          • Date created: now()
          • Date modified: now()
            • Updated when changes
        • Track all relationships (do not delete) in array
        • Add array to usermeta
      • Retrieve
        • Example: Teacher’s classes
        • Retrieve user’s relationships (array) from user metadata
        • Search array for active relationships to content
        • Filter results to IDs that match “class” content type
        • ? Class teachers
          • - Need to store relationship with both parties
    • - Duplication of data – Relationship must be stored in meta for both items
    • - Management
      • Must update metadata for each item when updating
      • Potential for stale/incorrect data if both items are not updated
    • - Relationships array will grow bigger and bigger over time
      • Since old relationships are retained for history
  3. WP Taxonomy
    • Manage relationships using build in taxonomy functionality
    • Need to look into WP taxonomy capabilities
    • + Use prebuilt API
    • - May not be tailor-made to fit needs
    • Methodology
      • Setup
        • Register custom taxonomy for each relationship type (class, grade level, etc.)
        • Set possible object types for taxonomy (user_student, user_teacher, etc.)
      • Store
        • Example: Teacher’s classes
          • Create term for each class
            • Term references item ID (from posts table)
              • To retrieve details from later
          • List classes on teacher edit form (like categories– grouped by grade level)
          • Term relationships saved automatically
      • Retrieve
        • Example: Teacher’s classes
          • get_object_terms($user_id, "user_$role")
            • Retrieves all user classes (active & inactive)
          • Filter out inactive classes
            • History stored as user metadata (e.g. class_history)
              • Class ID, Start date, End date
        • Example: Class Users (teacher(s)/students)
          • Get term associated with class (stored as metadata)
          • Get all objects associated with term (e.g. teacher_class, class_student, etc.) [in term_taxonomy table]
          • Retrieve objects (teachers, students, etc.) using IDs
      • Creating term for custom post type
        • Example: Class – Math
          • Properties
            • post_type: class
            • ID: 3
        • Term
          • terms
            • name: math
            • slug: math
              • Cannot depend on slug for matching with content
            • Useful so we don’t have to retrieve full object just to list term
          • term_taxonomy
            1. Link term to post type
              • term_id: [value from terms table]
              • taxonomy: obj-reference
            2. Link users to class (User to Content)
              • term_id: [value from terms table]
              • taxonomy: class (post type)
          • term_relationships
            • object_id: 3 (math post ID)
            • term_taxonomy_id: [ID from term_taxonomy]
      • Linking users to term
        • ? Use same/different taxonomy when adding term to user? (options)
          1. Same: One taxonomy for all associations (e.g. class <> user)
            • Students/Teachers will be grouped together
              • - Additional filtering necessary to retrieve only teachers or students associated with class
            • Use get_users()
              • Arguments
                • include: [CSV of users associated with class],
                • role: teacher
            • - Query will take longer since all users fetched even if only one role is needed
            • + Simpler management – Users may have multiple roles so selecting specific role may be difficult
            • - Relationship type not clear
              • Is user teach or student of class (e.g. if they have both roles)
                • User can be a teacher of one class and a student of another at a university/college
              • Would need to define relationship somewhere (terms, usermeta, etc.)
            • + Easy to fetch all terms associated with user at once (since single taxonomy)
          2. Different taxonomy depending on role
            • Example: Class’ teacher is lined using class_teacher taxonomy
            • + Easy to retrieve all users acting as teachers
            • - Multiple queries required to get all users associated with class (teachers and students)
            • + Efficient – Only retrieve users with desired role
            • + Relationship defined
              • class_teacher, class_student, etc.
              • Usable roles are defined in code when defining relationships
                • + Simple to iterate over roles to get all users linked to class