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.
FAQ about WordPress database schema
What is the WordPress database schema?
Well, the WordPress database schema basically consists of the structure and organization of the tables and data within a WordPress site’s database. It’s super important because it’s the backbone of your site’s content, user data, and other essential info.
Each WordPress installation has a default schema, which is designed to be compatible with a wide range of themes and plugins, ensuring smooth performance.
How many tables are there in the WordPress database?
Oh, that’s a good one! By default, there are 12 tables in a standard WordPress installation. These tables store info like posts, comments, users, and more.
Each table is responsible for different parts of your site, and they all work together to make sure everything runs smoothly. You might see additional tables if you’ve installed plugins or customized your site, but the core tables remain the same.
Can I modify the database schema?
Sure thing! You can modify the WordPress database schema, but I’d advise you to be extra careful when doing so. Modifying the schema can lead to compatibility issues with plugins, themes, or even future WordPress updates.
It’s best to make a backup of your database before making any changes. And if you’re not confident in your skills, it’s always better to consult a professional or use a plugin to make these changes.
What’s the purpose of the wp_posts table?
Ah, the wp_posts table is a biggie. It’s responsible for storing all your site’s content, like blog posts, pages, attachments, and even custom post types. Each post in the table has an associated ID, post type, status, and other metadata.
This table is pretty much the heart of your content on a WordPress site, so it’s essential to keep it in tip-top shape!
What does the wp_users table store?
The wp_users table is all about the people who make your site work – your users! This table stores info like usernames, passwords (hashed, of course), email addresses, and user roles.
It’s the backbone of your site’s user management, helping you keep track of everyone who has access to your WordPress admin area and their permissions.
How are comments stored in the database?
Comments have their own dedicated table, wp_comments. This table stores the comment data like author name, email, URL, comment content, and the associated post ID.
So, when someone leaves a comment on your site, all that information goes into the wp_comments table. It’s then displayed on your site, depending on your comment moderation settings.
What is the wp_options table for?
The wp_options table is your site’s settings hub. It stores all sorts of data related to your site’s configuration, like your site’s URL, active theme, and various plugin settings.
It’s a vital part of your WordPress site, as it helps ensure that your site runs smoothly and is customized to your preferences.
How do taxonomies work in the WordPress database?
In the WordPress database, taxonomies – like categories and tags – are stored in two tables: wp_terms and wp_term_taxonomy.
The wp_terms table stores the actual terms (category or tag names), while the wp_term_taxonomy table stores the taxonomy data, like term relationships and hierarchy. Together, they help you organize your content and make it easy for your users to find relevant posts.
Are custom fields stored in the database?
Absolutely! Custom fields are stored in the wp_postmeta table. This table stores all the metadata associated with a post, including custom fields.
Each custom field has a unique meta_key, and its value is stored as meta_value. This allows you to add extra information to your posts, making them more informative and engaging for your users.
What’s the relationship between tables in the WordPress database?
Great question! The tables in the WordPress database are interconnected through various relationships. For example, the wp_posts table is related to the wp_postmeta table through the post ID. Similarly, the wp_users table is connected to the wp_usermeta table via the user ID.
These relationships allow WordPress to retrieve and display relevant data efficiently. It’s this interconnected structure that makes the database schema so powerful and flexible, allowing you to create feature-rich and dynamic websites.
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.
