WordPress Development Recipe: Efficient binary storage and retrieval in custom tables using Strongly typed objects

Database Schema Design for Binary Data

When dealing with binary data—such as images, serialized objects, or other file types—within a WordPress custom table, efficiency and data integrity are paramount. Storing raw binary blobs directly in MySQL’s `BLOB` or `LONGBLOB` types can lead to performance bottlenecks, especially with large datasets or frequent read/write operations. A more robust approach involves a hybrid strategy: store metadata and identifiers in dedicated columns and the binary data itself in a separate, optimized location. For this recipe, we’ll focus on storing serialized PHP objects representing complex data structures, which can be treated as binary for storage purposes.

We’ll define a custom table structure that accommodates this. The table will include:

• An auto-incrementing primary key (`id`).
• A foreign key or identifier linking to a WordPress post, user, or other relevant entity (`parent_id`).
• A string column for a descriptive name or identifier (`name`).
• A `LONGTEXT` column to store the serialized PHP object. While `BLOB` types are for raw binary, `LONGTEXT` is often more performant for serialized PHP objects due to MySQL’s internal handling and character set considerations, especially if the serialized data might contain UTF-8 characters.
• Standard WordPress timestamp columns (`created_at`, `updated_at`).

MySQL Table Creation

The following SQL statement will create the necessary table. It’s crucial to use `utf8mb4` for character sets to ensure compatibility with a wide range of characters, including emojis, which can appear in serialized data.

CREATE TABLE IF NOT EXISTS wp_my_binary_data (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
    name VARCHAR(255) NOT NULL DEFAULT '',
    serialized_data LONGTEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY parent_id (parent_id),
    KEY name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Strongly Typed Object for Data Representation

To ensure data integrity and developer experience, we’ll define a strongly typed PHP object that represents the data we intend to store. This object will be serialized before insertion and unserialized upon retrieval. Using PHP’s type hinting and potentially a library like `jms/serializer` or PHP 8’s native attributes for more complex scenarios can further enhance this.

For this example, we’ll create a simple data structure. Imagine storing configuration settings for a specific e-commerce product variant.

Create a file named MyBinaryData.php within your plugin’s includes directory.

MyBinaryData.php:

namespace MyPlugin\DataObjects;

class MyBinaryData {
    public int $variantId;
    public string $sku;
    public array $attributes;
    public float $weight;
    public bool $isAvailable;

    public function __construct(int $variantId, string $sku, array $attributes, float $weight, bool $isAvailable) {
        $this->variantId = $variantId;
        $this->sku = $sku;
        $this->attributes = $attributes;
        $this->weight = $weight;
        $this->isAvailable = $isAvailable;
    }

    // Optional: A factory method for easier creation from an array
    public static function fromArray(array $data): self {
        return new self(
            $data['variantId'] ?? 0,
            $data['sku'] ?? '',
            $data['attributes'] ?? [],
            $data['weight'] ?? 0.0,
            $data['isAvailable'] ?? false
        );
    }

    // Optional: Method to get data as an array
    public function toArray(): array {
        return [
            'variantId' => $this->variantId,
            'sku' => $this->sku,
            'attributes' => $this->attributes,
            'weight' => $this->weight,
            'isAvailable' => $this->isAvailable,
        ];
    }
}

Database Interaction Layer (DAO/Repository Pattern)

A dedicated class to handle database operations will abstract the complexities of SQL queries and data serialization/unserialization. This follows the Data Access Object (DAO) or Repository pattern, promoting cleaner code and easier maintenance.

Create a file named BinaryDataRepository.php.

namespace MyPlugin\Database;

use MyPlugin\DataObjects\MyBinaryData;
use wpdb;

class BinaryDataRepository {
    private wpdb $wpdb;
    private string $table_name;

    public function __construct(wpdb $wpdb) {
        $this->wpdb = $wpdb;
        $this->table_name = $wpdb->prefix . 'my_binary_data';
    }

    /**
     * Inserts or updates binary data.
     *
     * @param MyBinaryData $dataObject The strongly typed data object.
     * @param int $parentId The ID of the parent entity.
     * @param string $name A descriptive name for the data.
     * @param int|null $id Optional. If provided, updates the existing record.
     * @return int|false The ID of the inserted/updated row, or false on failure.
     */
    public function save(MyBinaryData $dataObject, int $parentId, string $name, ?int $id = null): int|false {
        $serialized_data = serialize($dataObject);
        $data = [
            'parent_id' => $parentId,
            'name' => $name,
            'serialized_data' => $serialized_data,
        ];
        $format = ['%d', '%s', '%s'];

        if ($id !== null && $this->exists($id)) {
            // Update existing record
            $result = $this->wpdb->update($this->table_name, $data, ['id' => $id], $format, ['%d']);
            if ($result === false) {
                // Log error or handle failure
                return false;
            }
            return $id;
        } else {
            // Insert new record
            $result = $this->wpdb->insert($this->table_name, $data, $format);
            if ($result === false) {
                // Log error or handle failure
                return false;
            }
            return $this->wpdb->insert_id;
        }
    }

    /**
     * Retrieves a strongly typed data object by its ID.
     *
     * @param int $id The ID of the record to retrieve.
     * @return MyBinaryData|null The data object, or null if not found.
     */
    public function getById(int $id): ?MyBinaryData {
        $row = $this->wpdb->get_row($this->wpdb->prepare(
            "SELECT * FROM {$this->table_name} WHERE id = %d",
            $id
        ));

        if (!$row) {
            return null;
        }

        return $this->unserializeData($row->serialized_data);
    }

    /**
     * Retrieves data objects associated with a parent ID.
     *
     * @param int $parentId The parent ID.
     * @return MyBinaryData[] An array of data objects.
     */
    public function getByParentId(int $parentId): array {
        $results = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT * FROM {$this->table_name} WHERE parent_id = %d ORDER BY name ASC",
            $parentId
        ));

        $dataObjects = [];
        if ($results) {
            foreach ($results as $row) {
                $dataObject = $this->unserializeData($row->serialized_data);
                if ($dataObject) {
                    // Optionally attach the row ID and name to the object if needed for context
                    // $dataObject->recordId = $row->id;
                    // $dataObject->recordName = $row->name;
                    $dataObjects[] = $dataObject;
                }
            }
        }
        return $dataObjects;
    }

    /**
     * Deletes a record by its ID.
     *
     * @param int $id The ID of the record to delete.
     * @return bool True on success, false on failure.
     */
    public function delete(int $id): bool {
        $result = $this->wpdb->delete($this->table_name, ['id' => $id], ['%d']);
        return $result !== false;
    }

    /**
     * Checks if a record with the given ID exists.
     *
     * @param int $id The ID to check.
     * @return bool True if the record exists, false otherwise.
     */
    public function exists(int $id): bool {
        $count = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->table_name} WHERE id = %d",
            $id
        ));
        return $count > 0;
    }

    /**
     * Unserializes the data, handling potential errors.
     *
     * @param string $serialized_data The serialized string.
     * @return MyBinaryData|null The unserialized object or null on failure.
     */
    private function unserializeData(string $serialized_data): ?MyBinaryData {
        if (empty($serialized_data)) {
            return null;
        }
        $data = @unserialize($serialized_data); // Use @ to suppress warnings on invalid data

        if ($data === false && $serialized_data !== 'b:0;') { // Handle unserialize failure, but allow boolean false
            // Log unserialize error
            error_log("Failed to unserialize data: " . $serialized_data);
            return null;
        }

        // Basic type validation to ensure it's our expected object type
        if (!$data instanceof MyBinaryData) {
            // Log type mismatch error
            error_log("Unserialized data is not an instance of MyBinaryData.");
            return null;
        }

        return $data;
    }
}

Usage Example

Here’s how you would use the repository within your plugin’s logic, for instance, when saving product variant configurations.

Assume you have a product with `product_id = 123` and you want to save configuration for variant `variant_id = 456`.

// In your plugin's main file or an admin page handler:

// Ensure $wpdb is globally available or passed in
global $wpdb;

// Instantiate the repository
$repository = new \MyPlugin\Database\BinaryDataRepository($wpdb);

// --- Saving Data ---

// 1. Create a strongly typed data object
$variantConfig = new \MyPlugin\DataObjects\MyBinaryData(
    456, // variantId
    'SKU-XYZ-789', // sku
    ['color' => 'Red', 'size' => 'L'], // attributes
    1.5, // weight (kg)
    true // isAvailable
);

$parentId = 123; // The product ID
$dataName = 'variant_config_456';

// 2. Save the data object
$newRecordId = $repository->save($variantConfig, $parentId, $dataName);

if ($newRecordId) {
    echo "Successfully saved variant configuration with ID: " . $newRecordId . "\n";
} else {
    echo "Failed to save variant configuration.\n";
}

// --- Retrieving Data ---

// 1. Retrieve by parent ID (e.g., all configs for product 123)
$allConfigsForProduct = $repository->getByParentId($parentId);

echo "Found " . count($allConfigsForProduct) . " configurations for product {$parentId}:\n";
foreach ($allConfigsForProduct as $config) {
    // $config is an instance of MyBinaryData
    echo "- Variant ID: " . $config->variantId . ", SKU: " . $config->sku . ", Available: " . ($config->isAvailable ? 'Yes' : 'No') . "\n";
}

// 2. Retrieve a specific configuration by its record ID (if you stored $newRecordId)
if (isset($newRecordId)) {
    $specificConfig = $repository->getById($newRecordId);
    if ($specificConfig) {
        echo "Retrieved specific config (ID {$newRecordId}): SKU " . $specificConfig->sku . "\n";
    }
}

// --- Updating Data ---
if (isset($newRecordId)) {
    // Modify the retrieved object or create a new one
    $updatedConfig = $repository->getById($newRecordId);
    if ($updatedConfig) {
        $updatedConfig->isAvailable = false; // Mark as unavailable
        $updatedConfig->attributes['color'] = 'Blue'; // Change color

        // Save again, providing the existing ID to trigger an update
        $updateResultId = $repository->save($updatedConfig, $parentId, $dataName, $newRecordId);
        if ($updateResultId) {
            echo "Successfully updated variant configuration with ID: " . $updateResultId . "\n";
        } else {
            echo "Failed to update variant configuration.\n";
        }
    }
}

// --- Deleting Data ---
// if (isset($newRecordId)) {
//     $deleted = $repository->delete($newRecordId);
//     if ($deleted) {
//         echo "Successfully deleted record with ID: " . $newRecordId . "\n";
//     } else {
//         echo "Failed to delete record with ID: " . $newRecordId . "\n";
//     }
// }

Performance Considerations and Alternatives

Storing serialized PHP objects in a `LONGTEXT` column is a pragmatic approach for complex, structured data that doesn’t exceed reasonable size limits. However, for very large binary assets (like actual image files or large documents), this method is inappropriate. In such cases, you should:

• Store the binary data in the WordPress Media Library and store the attachment ID in your custom table.
• Store the binary data on an external object storage service (e.g., AWS S3, Google Cloud Storage) and store the URL or object key in your custom table.
• If the data is highly structured and frequently queried by its contents, consider using JSON instead of PHP serialization and storing it in a `JSON` type column (MySQL 5.7+) or `LONGTEXT` with JSON validation. This allows for more efficient querying within the database.

The `LONGTEXT` approach with `serialize()` is best suited for data that is primarily read and written as a whole unit, where the internal structure is only relevant to the PHP application logic and not for direct database querying.