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

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

• The big rub here is that user profiles are tied to access to the site.
• Things get complicated when you need to remove a user from a site, but retain the data.
• Users can **TECHNICALLY** have taxonomies.
• USERNAMES ARE PUBLIC!
• Meta is the best way to extend this model.

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

Internals

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

Strengths & weaknesses

Real world examples

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?

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.

Sync meta values over to a term

Re-map meta call for BC