Types, Subtypes, Meta, Options
-
An Abstract View on
Data Structures in WordPress

A presentation by @felixarntz

The WordPress Database Schema

Regular Tables

  • wp_commentmeta
  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_termmeta
  • wp_terms
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users

Additional Multisite Tables

  • wp_blogmeta (coming in WordPress 5.1)
  • wp_blogs
  • wp_blog_versions
  • wp_registration_log
  • wp_signups
  • wp_sitemeta
  • wp_site
Regular Tables
  • wp_commentmeta
  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_termmeta
  • wp_terms
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users
Additional Multisite Tables
  • wp_blogmeta (coming in WordPress 5.1)
  • wp_blogs
  • wp_blog_versions
  • wp_registration_log
  • wp_signups
  • wp_sitemeta
  • wp_site
Legend
  • Meta Table

Metadata Tables

wp_commentmeta → wp_comments
wp_postmeta → wp_posts
wp_termmeta → wp_terms
wp_usermeta → wp_users
wp_blogmeta → wp_blogs
wp_sitemeta → wp_site
Regular Tables
  • wp_commentmeta
  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_termmeta
  • wp_terms
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users
Additional Multisite Tables
  • wp_blogmeta (coming in WordPress 5.1)
  • wp_blogs
  • wp_blog_versions
  • wp_registration_log
  • wp_signups
  • wp_sitemeta
  • wp_site
Legend
  • Meta Table
  • ?
wp_commentmeta
  • meta_id bigint(20) unsigned auto_increment primary
  • comment_id bigint(20) unsigned
  • meta_key varchar(255)
  • meta_value longtext
wp_posts
  • ID bigint(20) unsigned auto_increment primary
  • post_author bigint(20) unsigned
  • post_date datetime
  • ...
wp_links
  • link_id bigint(20) unsigned auto_increment primary
  • link_url varchar(255)
  • link_name varchar(255)
  • ...
wp_signups
  • signup_id bigint(20) unsigned auto_increment primary
  • domain varchar(200)
  • path varchar(100)
  • ...

→ Entries from all tables are solely identified by a
unique numeric auto-incremented ID, except...

wp_options
  • option_id bigint(20) unsigned auto_increment primary
  • option_name varchar(191) unique
  • option_value longtext
  • autoload varchar(20)

Relationship Tables

wp_term_relationships
  • object_id bigint(20) unsigned primary
  • term_taxonomy_id bigint(20) unsigned primary
  • term_order int(11)

→ many-to-many relationship

Aside: Other Relationships

While wp_term_relationships is the only case of a many-to-many relationship in core, here are examples for other relationships:

wp_comments
  • ...
  • comment_post_ID → entries from wp_posts
  • ...
  • user_id → entries from wp_users
wp_posts
  • ...
  • post_author → entries from wp_users
  • ...
  • post_parent → entries from wp_posts
  • ...
wp_termmeta
  • ...
  • term_id → entries from wp_terms
  • ...
wp_blogs
  • ...
  • site_id → entries from wp_site
  • ...

→ one-to-many relationships

wp_term_taxonomy
  • term_taxonomy_id bigint(20) unsigned auto_increment primary
  • term_id bigint(20) unsigned unique
  • taxonomy varchar(32) unique
  • description longtext
  • parent bigint(20) unsigned
  • count bigint(20)
wp_terms
  • term_id bigint(20) unsigned auto_increment primary
  • name varchar(200)
  • slug varchar(200)
  • term_group bigint(10)
wp_term_taxonomy
  • term_taxonomy_id bigint(20) unsigned auto_increment primary
  • term_id bigint(20) unsigned unique
  • taxonomy varchar(32) unique
  • description longtext
  • parent bigint(20) unsigned
  • count bigint(20)
wp_terms
  • term_id bigint(20) unsigned auto_increment primary
  • name varchar(200)
  • slug varchar(200)
  • term_group bigint(10)
Regular Tables
  • wp_commentmeta
  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_termmeta
  • wp_terms
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users
Additional Multisite Tables
  • wp_blogmeta (coming in WordPress 5.1)
  • wp_blogs
  • wp_blog_versions
  • wp_registration_log
  • wp_signups
  • wp_sitemeta
  • wp_site
Legend
  • Meta Table
  • ?
  • Options Table
  • Relationships Table
  • Table with sole unique numeric
    auto-incremented identifier
Regular Tables
  • wp_commentmeta
  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_termmeta
  • wp_terms
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users
Additional Multisite Tables
  • wp_blogmeta (coming in WordPress 5.1)
  • wp_blogs
  • wp_blog_versions
  • wp_registration_log
  • wp_signups
  • wp_sitemeta
  • wp_site
Legend
  • Meta Table
  • ?
  • Options Table
  • Relationships Table
  • Table with sole unique numeric
    auto-incremented identifier

How WordPress Uses its Database Tables

The Post ≠ Post Dilemma

wp_posts
  • ID bigint(20) unsigned auto_increment primary
  • post_author bigint(20) unsigned
  • post_date datetime
  • post_date_gmt datetime
  • post_content longtext
  • post_title text
  • ...
  • post_type varchar(20)
  • ...

When speaking about posts, please clarify what you're referring to:

  • The overall concept of posts in WordPress?
  • Posts of the post type "post"?

How Posts and Terms Relate

wp_term_relationships
  • object_id bigint(20) unsigned primary
  • term_taxonomy_id bigint(20) unsigned primary
  • term_order int(11)

→ In addition to "post types", there are "object types". And they are not the same.

What are Object Types?

Object type "post" Object type "term"
Class WP_Post Class WP_Term
Function get_post() Function get_term()
Function wp_insert_post() Function wp_insert_term()
Function wp_update_post() Function wp_update_term()
Function wp_delete_post() Function wp_delete_term()
Function get_posts() Function get_terms()
Class WP_Post_Query Class WP_Term_Query

→ If WordPress had used interfaces from the beginning,
there likely would be one for all of these.

So why should we suddenly care about object types?

Aside: What is Metadata?

wp_commentmeta
  • meta_id bigint(20) unsigned auto_increment primary
  • comment_id bigint(20) unsigned
  • meta_key varchar(255)
  • meta_value longtext
wp_usermeta
  • umeta_id bigint(20) unsigned auto_increment primary
  • user_id bigint(20) unsigned
  • meta_key varchar(255)
  • meta_value longtext
wp_postmeta
  • meta_id bigint(20) unsigned auto_increment primary
  • post_id bigint(20) unsigned
  • meta_key varchar(255)
  • meta_value longtext
wp_blogmeta
  • meta_id bigint(20) (unsigned) auto_increment primary
  • blog_id bigint(20) (unsigned)
  • meta_key varchar(255)
  • meta_value longtext
wp_termmeta
  • meta_id bigint(20) unsigned auto_increment primary
  • term_id bigint(20) unsigned
  • meta_key varchar(255)
  • meta_value longtext
wp_sitemeta
  • meta_id bigint(20) (unsigned) auto_increment primary
  • site_id bigint(20) (unsigned)
  • meta_key varchar(255)
  • meta_value longtext

The Abstract Meta API

  • get_metadata( string $object_type, integer $object_id, string $meta_key, boolean $single )
  • add_metadata( string $object_type, integer $object_id, string $meta_key, mixed $meta_value )
  • update_metadata( string $object_type, integer $object_id, string $meta_key, mixed $meta_value, mixed $prev_value )
  • delete_metadata( string $object_type, integer $object_id, string $meta_key, boolean $delete_all )

register_meta( string $object_type, string $meta_key, array $args )
(since WordPress 4.6)

An optional string $object_subtype argument can be passed as argument
(since WordPress 4.9.8)

Definition of an Object Type

  • group of entities of similar nature
  • APIs follow a common structure, even in case they are technically independent
  • entities are identified by a numeric ID that is set via an auto-incremented database column
  • object types may or may not support subtypes, and they may or may not support metadata

Reminder

The term "object type" is not a synonym of the term "post type". Instead, "post type" is a synonym for the subtype of the "post" object type.

Overview about Core Object Types

comment post term user site network
class WP_Comment WP_Post WP_Term WP_User WP_Site WP_Network
retrieval get_comment() get_post() get_term() get_userdata() get_site() get_network()
addition wp_insert_comment() wp_insert_post() wp_insert_term() wp_insert_user() wp_insert_site() 1 wp_insert_network() 2
modification wp_update_comment() wp_update_post() wp_update_term() wp_update_user() wp_update_site() 1 wp_update_network() 2
deletion wp_delete_comment() wp_delete_post() wp_delete_term() wp_delete_user() wp_delete_site() 1 wp_delete_network() 2
querying get_comments() get_posts() get_terms() get_users() get_sites() get_networks()
query class WP_Comment_Query WP_Post_Query WP_Term_Query WP_User_Query WP_Site_Query WP_Network_Query
subtypes kinda yes (post types) yes (taxonomies) no no no
metadata yes yes yes yes yes 1 kinda
Wanna look at this on your own screen?
https://felix-arntz.me?p=1435#core-object-types
  • 1 coming in WordPress 5.1
  • 2 coming in the future

Comparing Options and Site Metadata

wp_options
  • option_id bigint(20) unsigned auto_increment primary
  • option_name varchar(191) unique
  • option_value longtext
  • autoload varchar(20)
wp_blogmeta
  • meta_id bigint(20) unsigned auto_increment primary
  • blog_id bigint(20)
  • meta_key varchar(255)
  • meta_value longtext

Dashed lines indicate that part is rather unclear at the moment.

Thank you!

Felix Arntz

Open-Source Contributor / WordPress Core Committer
Developer Programs Engineer at Google

Further Resources

Blog post version of this session
https://felix-arntz.me/?p=1435
Metadata registration dev note on WordPress 4.6
https://make.wordpress.org/core/?p=19000
Metadata registration dev note on WordPress 4.9.8
https://make.wordpress.org/core/?p=34207
REST API global search controller ticket
https://core.trac.wordpress.org/ticket/39965
Get Involved!
https://make.wordpress.org/meetings/