WordPress – Behind the Scenes, Part 2

In Part 1 of this series, I demonstrated how to install WordPress locally and how to import a WordPress database into Vertabelo. In this article, we’ll take a closer look at the tables in the WordPress database.

A Quick Look at the WordPress Database Model and the Dashboard

In the previous part, I imported the WordPress database into our online database modeling tool. For the record, the structure of the database is as follows:

There are a couple of important facts about the WordPress database model you should understand before we get started:

• Each WordPress site uses exactly the same database structure. It contains 11 tables and every WordPress site uses them in the background. Most WordPress plugins also use the database without any changes in the database model. The model has to be flexible enough to accommodate all the different plugins. Of course, plugin creators can add custom tables for specific plugins if the data structure is significantly different or if their plugin stores large amounts of data.
• The WordPress database lacks foreign key constraints. This is due to fact that WordPress uses the MyISAM storage engine, which does not support foreign keys. Tables work around this by having attributes that store unconnected “foreign key”-like values, so the foreign key constraint won’t be checked by the database. For example, the post_author attribute in the wp_posts table is a “reference” to the “ID” attribute in the wp_users table.
• Most of the tables use a single column primary key. They are named either simply “ID” (in the wp_users and the wp_posts table), or meta_id/umeta_id (in the meta tables: wp_postmeta, wp_commentmeta and wp_usermeta), or a combination of the table name and the suffix “_id” (all other tables). The only exception to these rules is the wp_term_relationships table, where the primary key consists of the two attributes: object_id and term_taxonomy_id. Attributes that are primary key or part of a primary key are of the bigint(20) type. Single-attribute primary keys also have the auto-increment property set to “Yes”.

Posts and Pages

The main reason to use WordPress is to create and manipulate content and present it to the public. To that end, WordPress provides us with two content types: Pages and Posts.

Pages are used to display static content. They don’t use tags or categories and are not listed by date. Also they don’t allow comments or social media sharing. Pages can have subpages. About Us pages are good examples of this type.

On the other hand, Posts are listed by date and can be organized using categories and tags. Posts can be used in RSS feeds, thanks to their chronological order. Posts cannot have “subposts“, but comments and social media shares are possible. Posts are essentially blog posts. This is understandable, since WordPress evolved from a blogging platform.

The primary table behind content on any WordPress page is called wp_posts:

WordPress uses the wp_posts table to store pages, posts, and attachments. We can look at this table as the core of our page, the place where most of the content is stored. It’s important to point out that attachments are actually stored on disk and the record in the wp_posts table keeps more information about it (who uploaded it and when, etc.).

The fields in the wp_posts table are:

• post_author – a reference to the wp_users table, denoting the author of the post.
• post_date – the date and time when the record was inserted into table.
• post_date_gmt – the GMT/UTC date and time when a record was inserted into the table.
• post_content – the actual content of the post.
• post_title – the title of the post.
• post_excerpt – a summary of the content.
• post_status – the current post status. WordPress uses 8 default statuses: “Publish”, “Future”, “Draft”, “Pending”, “Private”, “Trash”, “Auto-Draft” and “Inherit”.
• comment_status – turns comments on and off on a single post or on an entire page. There are two possible values: “open” and “closed”.
• ping_status – identifies if a post allows pingbacks and trackbacks. Like comment_status, it can contain only the values “open” and “closed”.
• post_password – the password used to view the post (optional).
• post_name – the human-readable url of a post_title.
• to_ping – a list of URLs WordPress should send pingbacks to, delimited by “\n”.
• pinged – a list of URLs WordPress has sent pingbacks to, delimited by “\n”.
• post_modified – the most recent date and time a post was modified.
• post_modified_gmt – the GMT/UTC date for post_modified.
• post_content_filtered – used by plugins to cache expensive post content transformations.
• post_parent – references the parent post.
• guid – Global Unique Identifier for a post; its permanent URL.
• menu_order – used for content ordering.
• post_type – the type of record. It can contain the values “post”, “page”, “attachment”, or user-defined custom types.
• post_mime_type – the type of uploaded files defined only for posts with post_type = attachment. It can contain values like “image”, “application/pdf” and “application/msword”.
• comment_count – the post’s number of comments, pingbacks, and trackbacks.

Here is a snapshot of the wp_posts table after I added the “About Nikola Tesla” page:

When we take a look at the wp_posts table, we can see a few versions of our page. The record with the ID = 1 has post_status = publish, meaning that the post is visible to everyone. The comment_status = closed and ping_status = closed denotes that comments and pings are disabled for this post.

Any additional information on posts and pages is kept in the wp_postmeta table:

The columns in this table are as follows:

• meta_id – the primary key of the table.
• post_id – a reference to the wp_posts table.
• meta_key – a description of a meta_value attribute.
• meta_value – the actual value stored.

The wp_postmeta table is where all information that can’t be saved in the wp_posts table is stored. It is stored as key-value pairs, a technique that’s often called entity-attribute-value (EAV). The table can be used by plugins for custom needs.

WordPress Taxonomies

Taxonomy is a fancy word that basically refers to grouping things together. WordPress has a couple of built-in taxonomies for grouping posts. For example, categories and tags are built-in WordPress taxonomies. You can also add your own custom taxonomies to WordPress.

The taxonomies and their terms are kept in tables called wp_terms, wp_term_taxonomy, and wp_term_relationships.

The wp_terms table stores a list of terms used to classify objects in your WordPress site:

This table holds all tag and category names, as well as terms from our custom taxonomies. The attributes are as follows:

• term_id – the primary key of the table.
• name – the name of the term.
• slug – the URL of name.
• term_group – used to group terms together.

Here are the contents of our example site’s wp_terms table:

The terms are assigned to taxonomies with the help of the wp_term_taxonomy table:

The attributes in the table are:

• term_taxonomy_id – the primary key of the table.
• term_id – the reference to the wp_terms table.
• taxonomy – the taxonomy name.
• description – a description of a term in that particular taxonomy.
• parent – a reference to the parent term in the wp_terms table.
• count – the number of objects in the wp_posts table that use this term in this taxonomy.

The wp_term_taxonomy table enables us to reuse the same term across different taxonomies. Notice that the record where term_id = 1 has taxonomy = category, while the other records have taxonomy = post_tag.

To relate objects saved in the wp_posts and the wp_term_taxonomy tables, WordPress uses the wp_term_relationships table:

Note that this is the only table in the model that has a key composed of more than one attribute.

The wp_term_relationships table has the following attributes:

• object_id – a reference to the wp_posts table.
• term_taxonomy_id – a reference to the wp_term_taxonomy table.
• term_order – the term order for a specific object.

We have six records here that connect six records from the wp_term_taxonomy table with the post (object_id = 6).

Comments and WordPress Data Modeling

We managed to place some content on our WordPress page. That’s nice, but in most cases we want to get feedback from the public. And that is the role of the comment feature.

To view comments on posts, we can simply use “Leave a comment” or click on “X Comment” (where X stands for the number of comments for the post).

Our first post already has one comment. When we click it, we’ll see it is an automatic comment caused by pingback. We’ll add one more comment to that post:

We now see two comments for our post, but what lies behind it all in the database?

You can guess from the table name that the wp_comments table is used to store comments on our WordPress page:

The attributes are mostly self-explanatory, but we’ll still take a closer look at some of them.

The comment_post_ID is a reference to the wp_posts table; it denotes which post has received comments. For the first comment, we can see that it was actually pingback and that the “author” is another post. For the second comment, we can see that I’m the author. Notice also the comment_agent contains some basic info about the system and computer used to post a comment.

The main idea behind all three meta tables in the model is to store data that we don’t want to store in our primary table. The wp_commentmeta is related to the wp_comments table in the same manner that the wp_postmeta table is related to the the wp_posts table.

Seeing WordPress Users

After our page is online anyone can see it. WordPress users are the ones who, according to their permission status, can make changes on our site and its content.

Now we’ll peek into the wp_users and the wp_usermeta tables in the MySQL database.

As expected, the wp_users table shown above stores basic data for all users registered on our WordPress site. Note that the user_pass is encrypted and that NewUser has the user_activation_key attribute filled while edrkusic has that field empty.

While attributes listed in the wp_users table are what we would expect in any WordPress site, the wp_usermeta table is used to store values that might be specific to a certain project:

For instance, notice that the record with umeta_id = 25 contains the value “some biographical info”, the same text that we typed in the dashboard while editing NewUser. The user_id attribute in that record has value 2, which corresponds the NewUser’s ID in the wp_users table. Obviously, the user_id is a reference to the wp_users table.

Links and Options in WordPress

The idea behind the wp_links table is to store links to other sites:

Having links to other sites was very popular in the beginning of the blogging era; nowadays it is used less and less. From WordPress version 3.5 on, link administration was even removed from admin interface. Still, this table is kept to provide compatibility with older versions.

The wp_options table stores data about WordPress installation, site configuration, theme, plugins, and widgets:

It’s also used to store temporary cached data. The EAV logic is also present in this table, as it is in wp_usermeta, wp_postmeta and wp_commentmeta. The attribute option_name plays the role of the key, while the attribute option_value is its corresponding value. The other two attributes in the table are the primary key attribute option_id and autoload, which controls if an option automatically loaded from the database.

Evaluating WordPress’ Database Model

The database model behind WordPress doesn’t follow several good database design rules and conventions. When we’re designing a database for a specific purpose, knowing all its desired functionalities in advance, we can follow all of those rules. But WordPress needs to cover anything that anyone could have in mind, so sacrificing foreign keys and using EAV is something that must be done. I would name the ID attribute the same across all the tables, and do the same with the “foreign keys”. For example, I wouldn’t use post_author in the wp_posts table, but I’d stick with users_id. Other than this, I must agree that the WordPress database is really a great model for its purpose.

What do you think? Let us know in the comments section.