This is the companion blog post to my Introduction to the WordPress Database presentation.

View Slides

When I first started working with database driven websites, I was afraid of the database. One of the biggest things that helped me was when someone described a database like a spreadsheet (and each table is like a tab in the spreadsheet). I’ve created a spreadsheet version of the WordPress Database to help visualize the database.

Below I’ll go through the 12 tables in the WordPress database. You may also want to checkout the Official WordPress Database Description.

WordPress Database Tables

I’ve grouped the database tables by their functionality.

• Options (settings)
  • wp_options
• Users
  • wp_users
  • wp_usermeta
• Posts
  • wp_posts
  • wp_postmeta
• Comments
  • wp_comments
  • wp_commentmeta
• Terms and Taxonomies
  • wp_terms
  • wp_term_taxonomy
  • wp_term_relationships
  • wp_termmeta
• No longer used by WordPress core
  • wp_links

Note: All of the database tables listed here start with wp_, which is the standard WordPress database table prefix. This prefix can vary on different installations. You can read more about the WordPress database prefix.

wp_options

• option_id auto-incrementing unique identifier
• option_name key to be used when looking up this option
• option_value value set for this option
• autoload used as part of the author URL

Option Reference

wp_users

• ID auto-incrementing unique identifier
• user_login used when logging into the site
• user_pass hash of the user’s password
• user_nicename used as part of the author URL (sanitized version of user_login)
• user_email the email address of the user
• user_url the URL of the user (optional)
• user_registered when the user registered on the website
• user_activation_key used when a password reset is performed (blank the rest of the time)
• user_status this field is no longer used
• display_name the name to display (e.g. when displaying the author name)

wp_posts

• ID auto-incrementing unique identifier
• post_title title of the post
• post_name post slug (used in the permalink)
• post_type the type of post (e.g. post, page, attachment, nav_menu_item, a custom post type), see Post Type
• post_content the content body of the post
• post_status the status of the post (e.g. publish, draft), see Post Status
• post_mime_type media type, only used when post_type is ‘attachment’ (e.g. image/jpeg) see MIME Types
• post_author user ID of the post author (from wp_users ID)
• post_date when the post was published (local time)
• post_date_gmt when the post was published (GMT)
• post_excerpt short summary of the post (optional) see Excerpt
• comment_status are comments allowed on this post (open or closed)
• ping_status are pings and trackbacks (remote notifications from other websites) allowed (open or closed)
• post_password password for viewing the post (optional) see Password Protected Posts
• to_ping a list of URLs WordPress should send pingbacks to
• pinged a list of URLs WordPress has sent pingbacks to
• post_modified when the post was last modified (local time)
• post_modified_gmt when the post was last modified (GMT)
• post_content_filtered this field is no longer used
• post_parent the ID of the parent post (e.g. parent page of this page or post associated with this attachment) (optional)
• guid global unique identifier string for this post (the permalink originally used when this post was published)
• menu_order used to manage display order (e.g. of pages or menu items) (default 0)

wp_comments

• comment_ID auto-incrementing unique identifier
• comment_post_ID the ID of the post this comment is associated with (from wp_posts)
• comment_content the content of the comment
• comment_author the name of the comment author (from User display_name or as typed in by the commenter)
• comment_author_email the email of the comment author (from User user_email or as typed in by the commenter)
• comment_author_url the URL of the comment author (from User user_url or as typed in by the commenter)
• comment_approved status of the comment ("1", "trash", "spam", "pending")
• comment_parent the comment_ID of the parent comment (the comment this comment is replying to) (optional)
• comment_author_IP the IP address of the computer used to make the comment
• comment_date when the comment was made (local time)
• comment_date_gmt when the comment was made (GMT)
• comment_karma unused by WordPress core
• comment_agent the user agent of the browser used to make the comment
• comment_type comment type (pingback, trackback, or blank to indicate a regular comment)
• user_id the user ID of the comment author (from wp_users ID) or 0 for an unregistered user

Meta Information

Sometimes we want to store additional information that doesn’t fit in one of the existing tables. For example, if we want to store:

• the favorite breakfast food of a user
• a subtitle for a post
• a comment author twitter handle for a comment

These are all reasonable ways to want to extend WordPress but the relevant tables do not have appropriate columns for any of these values. Fortunately, WordPress is designed to be extended and provides a way to store values like these. We refer to this extra data we want to store as “meta data”. Favorite breakfast food is meta data for a user. A subtitle is meta data for a post. A comment author twitter handle is meta data for a comment.

WordPress provides the following tables where we can store this meta data (wp_usermeta, wp_postmeta, and wp_commentmeta). Each row in these tables includes a reference to the entry the data is associated with (e.g. the wp_usermeta table has a column called user_id that refers to a row in the wp_users table).

wp_usermeta

• umeta_id auto-incrementing unique identifier
• user_id the user ID from wp_users
• meta_key the label for the value stored (e.g. favorite_breakfast_food)
• meta_value the value stored (e.g. waffles)

wp_postmeta

• meta_id auto-incrementing unique identifier
• post_id the ID of the post this information is associated with (from wp_posts)
• meta_key the label for the value stored (e.g. subtitle)
• meta_value the value stored (e.g. Hi Mom)

When developing with WordPress post meta comes up a lot. I’ve written another blog post specifically on WordPress post meta and I’ve given a talk, Introduction to Post Meta.

wp_commentmeta

• meta_id auto-incrementing unique identifier
• comment_id the ID of the comment this information is associated with (from wp_comments)
• meta_key the label for the value stored (e.g. twitter)
• meta_value the value stored (e.g. WapuuEU)

Terms and Taxonomies

WordPress comes with two taxonomies by default, Categories and Tags, both of which apply to Posts. Each value within those taxonomies (e.g. a specific category like Programming) is called a “term”.

wp_terms

• term_id auto-incrementing unique identifier
• name the name of the term (e.g. “Programming”)
• slug the slug for the term used in url (e.g. “programming”)
• term_group unused by WordPress core (see this WordPress Stackexchange entry on term_group)

You’ll notice in the table above, the term “Spicy” appears twice, this occurs because “Spicy” is being used in two different taxonomies (which can be determined by looking at the wp_term_taxonomy table below)

wp_term_taxonomy

This table associates a term with a specific taxonomy. In wp_terms the term name Spicy comes up twice (term_id 1 and 2) but here you can see the first is in the fe_recipe_tag taxonomy and the second in the post_tag taxonomy.

• term_taxonomy_id auto-incrementing unique identifier
• term_id the term_id of the Term from the wp_terms table
• taxonomy the taxonomy to which the term belongs (e.g. ‘category’, ‘post_tag’, custom taxonomy)
• description the description of the term
• parent the parent of the term (e.g. the term_id of the parent category from the wp_terms table)
• count number of posts assigned this term (because this is programmatically expensive to calculate, WordPress calculates this number and stores it here)

wp_term_relationships

This table associates terms (by their term_taxonomy_id from wp_term_taxonomy) with specific posts.

Note: this table does not include a auto-incrementing unique identifier column (this table is exclusively for relating terms to their object/post)

• object_id the object id (almost always post_id from wp_posts) of the object the term is associated with
• term_taxonomy_id the term_taxonomy_id from the wp_term_taxonomy table of the term (for a specific) taxonomy that is associated with the object
• term_order allows an ordering of terms for an object (unused in WordPress core)

For example the first row, indicates that post Hello World (object_id 1) has the post tag Spicy (term_taxonomy_id 2).

A cool SQL query to see all your terms, the taxonomy they are part of, and the post they are associated with is

SELECT 
    t.name as 'Term',
    t.term_id,
    tt.term_taxonomy_id,
    tt.taxonomy,
    p.post_title,
    p.post_type,
    tr.object_id AS 'Post ID'
FROM wp_term_relationships tr
INNER JOIN wp_posts p ON tr.object_id = p.ID
INNER JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
INNER JOIN wp_terms t ON t.term_id = tt.term_id
ORDER BY t.term_id;

Thanks to Brad Williams for his original version of this SQL query for WordPress taxonomy relationships.

wp_termmeta

This table is similar to the other meta tables (wp_usermeta, wp_postmeta, and wp_commentmeta). It allows us to store additional information with a term.

• meta_id auto-incrementing unique identifier
• term_id the term_id of the term from the wp_terms table
• meta_key the label for the value stored
• meta_value the value stored

wp_links

This table is no longer used by WordPress core. You can read about the Links Manager functionality that has been removed and if you would like to restore this functionality in WordPress you can use the Link Manager plugin.

Photo Credit

Pexels Architecture Black Cave Corridor

Sal Ferrarello (@salcode)

Sal is a PHP developer with a focus on the WordPress platform. He is a conference speaker with a background including Piano Player, Radio DJ, Magician/Juggler, Beach Photographer, and High School Math Teacher. Sal can be found professionally at WebDevStudios, where he works as a senior backend engineer.