Data Modeling

Custom Database Tables

For specialized data structures that do not fit within the standard WordPress post or comment schemas, this project utilizes custom relational tables. These tables are optimized for high-read performance and structured querying.

Interacting with Custom Tables

All database interactions are handled through the global $wpdb object to ensure SQL injection prevention and compatibility with different database environments.

Example: Fetching records from a custom table

global $wpdb;

$table_name = $wpdb->prefix . 'custom_entity_data';
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM $table_name WHERE status = %s LIMIT %d",
        'active',
        10
    ),
    ARRAY_A
);

Schema Updates

Schema changes are managed via dbDelta(). If you are extending this repository, ensure any modifications to the table structure are defined in your activation hooks using standard SQL syntax.

Metadata Structures

We leverage the WordPress Metadata API to extend standard objects (Posts, Users, and Comments) without modifying core tables. This allows for flexible data attributes that can be queried using WP_Query or get_metadata.

Post Metadata Usage

Standard metadata is used to store configuration or extended properties for custom post types.

Example: Updating and retrieving metadata

// Update metadata for a specific post
update_post_meta( $post_id, '_ua_processing_status', 'completed' );

// Retrieve metadata
$status = get_post_meta( $post_id, '_ua_processing_status', true );

Object Caching Strategies

To reduce database load and improve response times, this project implements a granular object caching strategy using the WP_Object_Cache API.

Cache Groups

Data is categorized into specific groups to allow for targeted invalidation:

ua_queries: Cached results of complex SQL queries.
ua_transients: Temporary data fetched from external APIs.

Usage Example

When retrieving data, always check the cache before querying the database or an external service.

$cache_key   = 'ua_data_item_' . $item_id;
$cache_group = 'ua_queries';

// Attempt to get data from cache
$data = wp_cache_get( $cache_key, $cache_group );

if ( false === $data ) {
    // Cache miss: Fetch from DB
    $data = $wpdb->get_row( ... );
    
    // Set cache for 1 hour (3600 seconds)
    wp_cache_set( $cache_key, $data, $cache_group, 3600 );
}

return $data;

Invalidation

Data integrity is maintained by invalidating the cache during update or delete operations:

wp_cache_delete( 'ua_data_item_' . $item_id, 'ua_queries' );

Note: For persistent caching across page loads, ensure an object cache drop-in (like Redis or Memcached) is active in your WordPress environment. Otherwise, the cache will only persist for the duration of a single request.