Data Structure Design for WordPress

Ryan Kanner (@CodeProKid)

slides.com/codeprokid/data-wp

Me.

  • Working with WordPress for 10+ years.
  • Ex-east-coaster now live in Colorado
  • 4th time speaking at WC Denver!
  • Work at a company called NerdWallet working with WordPress on the content platform team.
  • Organizer of the WordPress Denver meetup!

slides.com/codeprokid/data-wp

What are we talking about & Why are we talking about it?

The What

  • Overview of the WordPress database schema.
  • What options we have to store data in WordPress as developers.
  • Pros and cons of different storage mechanisms.
  • Real world examples.

slides.com/codeprokid/data-wp

The why

  • Changing where your data is stored after you already have some data is pretty difficult.
  • A little bit of planning up front can help you a lot in the long run.
  • I've struggled with this in the past, and want to share what I've learned!

slides.com/codeprokid/data-wp

The scema

WordPress Object Types

Post

Term

User

Comment

Sub-Types

Post

Page

Attachment

Menu-items

Categories

Tags

Menus

Ping

Object Type Definition

  • An abstract model that organizes elements of data and standardizes how they relate to one another and to properties of the real world entities.
  • A group of "things" that have similar characteristics & traits.
  • A model for a set of data, where each object within the type conforms to the data model.
  • Objects can be differentiated by field values (think post_type), but ultimately are still modeled the same as every other object within the type.

Types of connections

One to one

Object 1

Object 2

Examples

  • Users to Posts
  • Users to Comments
  • Comments to Posts

Advantages

  • Quick Lookups
  • Simple Architecture
  • Easy to understand

Disadvantages

  • Limited to one relation
  • Bi-directional relation embedded in one type

One to Many

Object 1

Object 2

Examples

  • posts to postmeta
  • terms to termmeta
  • users to usermeta
  • comments to commentmeta

Advantages

  • Quick Lookups if you know object 1's ID
  • Easy to get all data related to 1st object.

Disadvantages

  • VERY slow to query across
  • Bi-directional relation embedded in one type

Object 3

Many to Many

Object 3

Examples

  • term_relationship table
  • You can technically use this with users & comments too

Advantages

  • Allows you to relate many objects to many terms.
  • Fast to query across for filtering data.

Disadvantages

  • Designated table adds some complexity.
  • The data that you can relate is limited.

Object 4

Object 1

Object 2

Post

DB Schema

  • Schema lends itself to storing more feature rich entities
  • Includes things like published & edited date by default
  • Building on top of this model gives you a lot of nice things out of the box, like REST, CLI & caching support.

Internals

  • Class  
    WP_Post
  • Create function  
    wp_insert_post($args, $wp_error);
  • Read function  
    get_post($id, $output, $filter);
  • Update function 
    wp_update_post($args, $wp_error);
  • Delete function 
    wp_delete_post($id, $force);
  • Query posts 
    get_posts($args);
  • Query class 
    WP_Query

Associate front end templates

  • archive-$posttype.php
  • archive.php
  • date.php
  • search.php
  • index.php
  • home.php

Archive

  • single-$posttype.php
  • single-post.php
  • page-$slug.php
  • page-$id.php
  • page.php
  • attachment.php (& others)
  • single.php
  • singular.php
  • $custom.php

Single

Use cases

  • Best for primary entity types that have associated data, and have the need to be filtered.
  • Shouldn't be tightly coupled to another entity that conforms to the post model.
  • Querying across the dataset.
  • Needs to be supported in the REST api.

Term

DB Schema

  • Model was initially intended to just categorize posts.
  • Term meta introduced in 4.4 changed the game.
  • Not as feature rich as posts.
  • Building on top of this model gives you a lot of nice things out of the box, like REST, CLI & caching support.

Internals

  • Class  
    WP_Term
  • Create function  
    wp_insert_term($term, $tax, $args);
  • Read function  
    get_term($term, $tax, $output, $filter);
  • Update function  
    wp_update_term($term_id, $tax, $args);
  • Delete function  
    wp_delete_term($term, $tax, $args);
  • Query terms  
    get_terms($args);
  • Query class  
    WP_Term_Query

Associate front end templates

  • category-$slug.php
  • category-$id.php
  • category.php
  • tag-$slug.php
  • tag-$id.php
  • tag.php
  • taxonomy-$taxonomy-$term.php
  • taxonomy-$taxonomy.php
  • taxonomy.php
  • archive.php

Use cases

  • Best for data that needs to be related to other Entities using the Post model.
  • Slimmed down version of Post model. No revisions, dates, etc.
  • Term meta lets you extend this type pretty far.
  • Stop thinking about categories and tags.
  • Think about front end display and templates, where can you leverage fields on this type in templates?
  • If you are using a dropdown select UI for choosing meta, you can probably use a term for that.
  • Use instead of custom page templates.

user

DB Schema

Internals

  • Class  
    WP_User
  • Create function  
    wp_insert_user($userdata);
  • Read function  
    get_userdata($user_id);
  • Update function  
    wp_update_user($userdata);
  • Delete function  
    wp_delete_user($userid, $reassign);
  • Query users  
    get_users($args);
  • Query class  
    WP_User_Query

Associate front end templates

  • author-$nicename.php
  • author-$id.php
  • author.php
  • archive.php

Use cases

  • Useful for containing data about users on the site.
  • Leverage user roles & capabilities to customize the admin experience for different job functions.
  • Team pages.
  • Author boxes on posts & author archives.
  • Remember, connection from users to posts is one to one, so you can't have multiple authors for a single post out of the box. You need something like bylines or co-authors plus to do that.

Comment

DB Schema

  • Schema is fairly basic. Most stuff lives in the comments table.
  • Schema is very specific and closely tied to the core comments feature which makes extending it kind of difficult.
  • Meta is the only mechanism to extend the type.

Internals

  • Class  
    WP_Comment
  • Create function  
    wp_insert_comment($commentdata);
  • Read function  
    get_comment($comment, $output);
  • Update function  
    wp_update_comment($commentdata);
  • Delete function  
    wp_delete_comment($comment_id, $force);
  • Query comments  
    get_comments($args);
  • Query class  
    WP_Comment_Query

Use cases

  • Use cases are pretty limited outside of the core implementation.
  • Internal commenting like editorial comments.
  • Order comments like in an e-commerce store.
  • Product reviews.
  • Whatever implementation you are thinking of has to stick to the comment model pretty closely since a lot of the fields are on the core model instead of meta, and aren't very flexible.

Options

DB Schema

  • Dumping ground for anything & everything you can think of.
  • Used for global site settings and things like widgets & sometimes transients.
  • Not a scalable solution for many keys.
  • Take care with autoload = true.
  • Option keys limited to 64 characters. Becomes a real issue with dynamically created key names.

Internals

  • Create function  
    add_option($option, $value, $autolaod);
  • Read function  
    get_option($option, $default);
  • Update function  
    update_option($option, $value, $autoload);
  • Delete function  
    delete_option($comment_id, $force);

Use cases

  • Anything that is supposed to be accessed across different types, but doesn't have many entries.
  • Don't set autoload to true unless you are certain that the data needs to be accessed in all contexts (REST, RSS feeds, admin, frontend, etc.)
  • Can store all default scalars like arrays, objects, strings, ints etc. But will sanitize everything into a string on the way in.
  • Don't use if you need to make batched requests, no good API for that.

Custom tables

DB SCHEMA

¯\_(ツ)_/¯

Use cases

  • When you have outgrown the existing Models in WordPress.
  • When your queries start getting really complex and messy.
  • When most of the relevant data for your entity is stored in Meta, but that's what you need to query against.
  • Scalability starts becoming a concern, both vertically and horizontally.

Words of caution

  • You'll have to write your own caching layer for queries if you want performance to stay quick.
  • You'll have to write your own helper functions on top of direct DB queries. Especially if it's for an OSS plugin, this way you can change the DB schema, and ship changes to the helper function to support these.
  • You won't be able to take advantage of the free UI you get with the core types.
  • Plugins won't know that your data exists.

Let's review

comment post term user Option
class WP_Comment WP_Post WP_Term WP_User N/A
read get_comment() get_post() get_term() get_userdata() get_option
create wp_insert_comment() wp_insert_post() wp_insert_term() wp_insert_user() add_option()
update wp_update_comment() wp_update_post() wp_update_term() wp_update_user() update_option()
delete wp_delete_comment() wp_delete_post() wp_delete_term() wp_delete_user() delete_option()
querying get_comments() get_posts() get_terms() get_users() N/A
query class WP_Comment_Query WP_Post_Query WP_Term_Query WP_User_Query N/A
subtypes Kinda (pings) yes (post types) yes (taxonomies) no Kinda (widgets)
metadata yes yes yes yes No

Internals

Source:  https://slides.felix-arntz.me/types-subtypes-meta-options-data-structures-in-wordpress/#/29

Strengths & weaknesses

Comment Post Term User Option
Strength The built in connection to posts is nice. Most robust storage model. Supports many to many connections, Good for related data on posts. Does a good job modeling data related to a human being. Most freeform option. Easy to access the data from any context.
Weakness Model is very opinionated, core fields are very specific to public facing comments. Could be overkill in some cases. Heavily extending this type can lead to a poor data management experience in the admin. Tied to site access roles and capabilities. Not scalable for many entries.

Real world examples

Post Type Label

Term Archive

Term Description

List of terms in taxonomy

Grab queried term from main query, apply active class

Title

Term

Meta

the_content

Terms

meta / open repeater

meta / open repeater

meta / menu_order

add_action( 'updated_post_meta', 'my_calback', 10, 4 );

function my_callback( $meta_id, $object_id, $meta_key, $meta_value ) {
    if ( 'total_homes' !== $meta_key ) {
        return;
    }

    wp_update_post( [
        'ID' => absint( $object_id ),
        'menu_order' => absint( $meta_value ),
    ] );
}
// Slowwwww
$portfolio_items = new WP_Query( [
    'post_type' => 'portfolio',
    'meta_key' => 'total_homes',
    'orderby' => 'meta_value_num',
] );

// Fast!
$portfolio_items = new WP_Query( [
    'post_type' => 'portfolio',
    'orderby' => 'menu_order',
] );

Author box. Grabs data from the user profile for the author of the article.

CTA box that is changed out every few weeks/months. Also A/B tested. Pulled from a custom post type.

Related posts module that is customized per term. Managed on the term edit screen where you select a few posts to feature.

Managed as a menu, and added to the bottom of all posts in a few post types.

Static CTA that never changes.

Commenting policy stored in an option, managed in site settings.

Questions to ask

  • Does this need to be included across different types?
  • Are there any variances of this module on an entity by entity basis?
  • What's the most effective core admin UI to manage this data?
  • Where does it make sense to manage this data from a contextual point of view? Which entity is it most closely related to?
  • Are the needs of the data complex enough where they need to be their own Entity? There are implications to this decision.
  • How often does this data need to change?
  • How many of these things will there be?

This data is very closely tied to a user's profile. It's not relevant to anything else.

There will likely be a lot of these, and they need to be shared across different types. There are also a fair amount of settings needed here.

This will change pretty often, and are directly tied to the term the post is in.

This is just a list of links to existing entities. The built in menu management does this really well.

This never changes, let's make it static.

This needs to be a global setting, as it spans all types. Need one central spot for updates.

Working backwards

(what happens if you mess up)

Some options

  • Migrate with WP-CLI.
  • Asynchronous jobs or cron.
  • Direct SQL scripts (if you're a cowboy/cowgirl) please don't do this, but if you do, practice safe migrations and take a backup & test on stage.
  • Moving forward use hooks/filters to be backwards compatible.
  • Create a new method for managing the data, and deprecate the old one. Migrate by hand, or just ditch the old method.
add_action( 'updated_post_meta', 'my_calback', 10, 4 );

function my_callback( $meta_id, $object_id, $meta_key, $meta_value ) {
    if ( 'key_to_migrate' !== $meta_key ) {
        return;
    }

    wp_set_post_terms( $object_id, $meta_value, 'meta-tax', true );
}

Sync meta values over to a term

Re-map meta call for BC

add_filter( 'get_post_metadata', 'my_callback', 10, 4 );

function my_callback( $check, $object_id, $meta_key, $single ) {

    if ( 'key_to_migrate' !== $meta_key ) {
        return $check;
    }

    
    $terms = wp_get_object_terms( $object_id, 'meta-tax', [
        'fields' => 'names',
    ] );

    if ( true === $single ) {
        return $terms[0];
    }

    return $terms;
}

Wrap it up

  • Think about how the data needs to be presented.
  • Think about how the data needs to be managed.
  • Think about the pros and cons of the options you have available.
  • Map out your plan before you write any code, even if it's super rough.
  • Don't be afraid to experiment, and pivot quickly if something isn't working.

Questions?

Ryan Kanner (@CodeProKid)

slides.com/codeprokid/data-wp

Made with Slides.com