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_authorattribute in the
wp_poststable is a “reference” to the “ID” attribute in the
- Most of the tables use a single column primary key. They are named either simply “ID” (in the
umeta_id(in the meta tables:
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_relationshipstable, where the primary key consists of the two attributes:
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
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_userstable, 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
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_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
The columns in this table are as follows:
meta_id– the primary key of the table.
post_id– a reference to the
meta_key– a description of a
meta_value– the actual value stored.
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.
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 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
term_group– used to group terms together.
Here are the contents of our example site’s
The terms are assigned to taxonomies with the help of the
The attributes in the table are:
term_taxonomy_id– the primary key of the table.
term_id– the reference to the
taxonomy– the taxonomy name.
description– a description of a term in that particular taxonomy.
parent– a reference to the parent term in the
count– the number of objects in the
wp_poststable that use this term in this taxonomy.
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
Note that this is the only table in the model that has a key composed of more than one attribute.
wp_term_relationships table has the following attributes:
object_id– a reference to the
term_taxonomy_id– a reference to the
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.
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
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
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.
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_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
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.