WordPress and most plugins store their settings in server databases. Data stored there is arranged in tables.
It is similar to an Excel sheet with one header row and values in the row below.
This article will explain which table handles the content on a WordPress website.
Get ready for learning about the WordPress database schema.
Lead-In
The database is an indispensable part of a WordPress site. WordPress utilizes the MySQL database management system version 5.6 or greater.
It can also use MariaDB version 10.1 or higher. MariaDB is a fully GPL licensed fork of MySQL that works as a substitute for MySQL.
A good comprehension of the structure of the table is essential. It allows users to know which table to include or exclude when syncing or moving data from a staging site to the live site using WP Staging.
This is true for the reverse process too. It’s also useful for updating the staging site.
WordPress and the database interface automatically. This means that end-users don’t need to know about its structure.
However, for those writing a plugin, it may be beneficial to have more information. This may include how WordPress stores its data and relationships.
You may have tried to use the existing WordPress API to access the required data. It is, however, impossible without a direct connection to the database.
For that reason, WordPress provides the wpdb class.
A popular way to access the WordPress database is to use an online admin tool called phpMyAdmin. It is often pre-installed on many web hosts (via cPanel).
It is also available in local development environments such as MAMP.
Remember though that accessing the database comes with a risk. Any wrong move may break your site.
So before doing anything, it’s best to make a back-up.
Overview of the WordPress Database Schema and Tables
A brand new WordPress website has 12 tables. Those are:
- wp_posts
- wp_postmeta
- wp_options
- wp_users
- wp_usermeta
- wp_term_taxonomy
- wp_terms
- wp_term_relationships
- wp_links
- wp_comments
- wp_commentmeta
- wp_termmeta
Tables are often associated with one another since various pieces of data may be related.
For example, a blog post may be linked to categories and tags. So the table storing blog posts will relate to the table storing categories and tags.
wp_post
The wp_post table stores all data related to the content of a WordPress website. This includes things such as posts, pages, and page revisions.
Also stored here are navigation menu items, media files, images, and attachments. Content data used by plugins also sit in this table.
Post_type is a table column within wp_post that categorizes the above-mentioned data. It allows users to request specific types of data using a database query.
This makes post_type the most important column in the wp_post table.
The post_type column shows the type of each row – a post, page, attachment, nav_menu_item, or another type. This variety of content gives the table a flexible nature.
wp_postmeta
Every post contains a set of data called metadata. Storage of this information takes place in the wp_postmeta table.
Certain plugins may also add their data to this table.
wp_postmeta is an adjunct to the wp_post table. It is a vertical table that stores the data using key/value pairs.
This is a technique many WordPress tables use. It enables WordPress core, plugins, and themes to store unlimited data.
Mashshare, a social sharing plugin, also uses this table to share counts of specific posts.
Note: Many database tables allow WordPress core, plugins, or themes to store additional information.
wp_options
wp_options is one of the most important WordPress tables. This table stores all the options set under Administration > Settings panel.
The settings stored here include the URL, the title, installed plugins, and more. The majority of plugins store their settings here, too.
Also stored in this table are all the WordPress dashboard settings. Unlike other tables, the wp_options table does not relate to any other table.
wp_users
This table holds the list of all registered users on your WordPress site together with their basic information. This includes login, password (encrypted), email address, registration time, display name, status, and activation key (when necessary).
wp_usermeta
Metadata is a set of additional data about other data. As noted, the wp_user table stores the basic information about the site users.
The wp_usermeta table holds additional data that is not stored in the wp_uset table. This includes, for instance, the users’ last names.
wp_terms
The wp_terms table stores the categories for posts and links and the tags for posts.
Terms are items of the system used to classify objects. For instance, it’s possible to classify posts and custom posts in different ways.
When creating a post there is the option to add a category and tags to it. Those are examples of taxonomy – a system of classifying and grouping things.
The wp_term table contains a column called “slug.” A slug is a tag of a specific post.
In WordPress, you can connect posts, pages, and links using tags.
wp_term_taxonomy
As the name indicates, this table describes the taxonomy for the entries in the wp_term table. The taxonomy could be a category, link, or tag.
The structure of this table allows you to use the same term for different taxonomies. For instance, the term “database” could be a category for posts and also a custom taxonomy for a custom post type.
So the term “database” would occur once in the wp_terms table, but twice (two rows) in the wp_term_taxonomy table.
wp_term_relationships
This is where WordPress stores the critical data as regards using taxonomies.
The wp_post table stores the post data. The wp_term_relationship table records the set category and tags.
It defines a relationship between a post (object) in the wp_post table and a term of a specific taxonomy in the wp_term_taxonomy table.
For example, this post is associated with a category and several tags. The wp_term_relationship table is responsible for maintaining that association.
wp_links
Links are like letters of recommendation.
A blogroll is a list of all external links, most often displayed in a website’s sidebar. Blogrolls were very popular up until a few years ago.
The idea was for website owners to make smart decisions when linking to another website. However, some site owners abused the blogroll feature and so WordPress removed it.
The wp_links table is the storage area for all data related to blogrolls and links. It’s now quite unusual to find the wp_links table since blogrolls are not used anymore.
wp_comments
Wp_comments stores all the comments left on your WordPress posts or pages. It also holds information about the author of the comment.
This information may include name, email address, and comment type (simple, pingback, or trackback).
If using a third-party service (e.g. Disqus), the storage of comments will occur on that system’s server.
wp_commentmeta
Wp_commentmeta contains additional information about each comment. Comment metadata includes:
- meta_id – a unique number assigned to each row
- comment_id – the ID of the post the data relates to (a reference to the wp_comments table)
- meta_value – the piece of data
- meta_key – an identifying key to the piece of data
wp_termmeta
This is a new table introduced in WordPress 4.4. It stores additional information about terms (data not stored in the wp_terms table).
It’s possible to access the term meta using the following functions:
add_term_meta, get_term_meta, update_term_meta, and delete_term_meta.
Developers use term meta to hold custom data about terms in a standard way. For instance, to attach an image against a post category to be shown on the category archive page.
Hey, did you know data can be beautiful too?
wpDataTables can make it that way. There’s a good reason why it’s the #1 WordPress plugin for creating responsive tables and charts.
And it’s really easy to do something like this:
- You provide the table data
- Configure and customize it
- Publish it in a post or page
And it’s not just pretty, but also practical. You can make large tables with up to millions of rows, or you can use advanced filters and search, or you can go wild and make it editable.
“Yeah, but I just like Excel too much and there’s nothing like that on websites”. Yeah, there is. You can use conditional formatting like in Excel or Google Sheets.
Did I tell you you can create charts too with your data? And that’s only a small part. There are lots of other features for you.
Ending thoughts on the WordPress database schema
It is clear that WordPress or plugin developers need to know how the database functions.
But WordPress users should also have some basic knowledge of the database. After all, it is an important part of each website.
The database expands with each new piece of information added to the website. To support certain functions of the website, it’s necessary to add new tables to the database.
For example, when installed, wpDataTables creates its own WordPress database table. Not all plugins do that though, many use the tables that already exist.
The goal of this article is to outline clearly the structure of the database. It also gives insight into how WordPress works in the background.
It is true that there are plugins for virtually everything. But don’t rush to install them as there may be an easier and faster alternative available.
If you enjoyed reading this article on WordPress database schema, you should check out this one about how to do a WordPress database reset.
We also wrote about a few related subjects like how to find and replace url in WordPress database, WordPress database plugins, WordPress Excel spreadsheet plugins, how to do a WordPress database cleanup and how to scan WordPress database for malware.
