A WordPress website consists of three main elements:
- The WordPress installation itself
- The contents of the
wp-contentdirectory which includes the themes, plugins and uploads - The database, where all the content is stored.
Most WordPress users never come into direct contact with the database and may not even be aware that it's constantly working to populate their site. When WordPress serves up any kind of page, be that the home page, a single post or page or an archive, it's accessing the database to bring up content that editors and administrators have added to the site.
In this series of tutorials I'll look in detail at different aspects of the WordPress database. The series will have nine parts, covering the following:
- Introduction
- Relationships between data
- Content types
- User data
- Metadata
- Taxonomies, categories, tags and terms
- Taxonomies vs post metadata
- The options table
- WordPress Multisite data
In this introduction, I'll give an overview of the database tables and how they relate to the content types you may be used to working with in WordPress, and identify what's stored where.
Content Types in WordPress
As the database tables are used to store content, before you can understand them you need to understand content. There are a number of types of content in WordPress:
- posts
- pages
- custom post types
- attachments
- links
- navigation menu items (which are stored as individual posts)
These content types then have data attached to them:
- categories
- tags
- custom taxonomies and terms
- post metadata
In addition to these, there are other types of content which are stored differently:
- widgets
- options
- users
- sites (for a multisite installation)
- hardcoded content (added to your theme or plugins)
- content sourced from elsewhere (third party content accessed via feeds, streaming or other techniques)
All of these types of content are stored somewhere in the database (or occasionally in themes or plugin files as I'll show). They may have an entry of their own or they may be part of another entry (for example streamed content coded into a post). They may also be linked to data in other tables. For example, data about posts will be linked to data about users so that WordPress knows who authored which posts.
The WordPress Database Structure
WordPress uses a range of database tables with relationships between them to minimize the amount of data that has to be stored - this creates one-to-many relationships. This means that, say, one user can have many posts that they authored related to their user record. It saves space - if WordPress stored all of the user data for each users against every post their authored, that would mean a lot of repeated data and a lot of space.
The diagram below is taken from the WordPress codex and shows the database tables and how they are linked:
Most of the tables are linked to one or more other tables via one field. This field will be a unique identifier for each record such as post_id. This is shown in more detail in this table:
| Table | Data stored | Linked to |
|---|---|---|
wp_posts |
Posts, pages, attachments, revisions and navigation menu items |
wp_postmeta (via post_id)wp_term_relationships(via post_id) |
wp_postmeta |
Metadata for each post |
wp_posts (via post_id) |
wp_comments |
Comments |
wp_posts (via post_id) |
wp_commentmeta |
Metadata for each comment |
wp_comments (via comment_id) |
wp_term_relationships |
Relationships between posts and taxonomies |
wp_posts (via post_id)wp_term_taxonomy (via term_taxonomy_id) |
wp_term_taxonomy |
Taxonomies (including categories and tags) |
wp_term_relationships (via term_taxonomy_id) |
wp_terms |
Your categories and tags and the terms assigned to custom taxonomies |
wp_term_taxonomy (via term_id) |
wp_links |
The links in your blogroll (if you still have one) |
wp_term_relationships (via link_id) |
wp_users |
Users |
wp_posts (via post_author) |
wp_user_meta |
Metadata for each user |
wp_users (via user_id) |
wp_options |
Site settings and options (set via the Settings screens and via plugins and themes) | n/a |
A few things are worth noting:
- Database tables have the
wp_prefix by default. You can change this when you configure your site but there isn't much value to it. - The core table is the
wp_poststable, where most of your data will be stored. This holds (almost) everything else together. - Only one table isn't attached to any others - the
wp_optionstable. This tables stores data about the site and the WordPress installation, which isn't related to data about posts or users. - Two tables are used to store data about taxonomies - these will be explained in more detail later in this series.
- The
wp_usersandwp_commentstables are not linked - although it is possible to specify that users have to be registered to comment, WordPress doesn't actually store data about comments against each user who has posted them. - A multisite installation will have some extra tables. I haven't included those here as that's outside the scope of this tutorial.
Linking Content to Database Tables
Having looked at the content types in WordPress and the database tables used to store them, it can be helpful to match the two up. The table below shows which database table is used to store each type of content.
| Content Type | Table(s) |
|---|---|
| posts | wp_posts |
| pages |
wp_posts |
| custom post types |
wp_posts |
| attachments |
wp_posts |
| links |
wp_links |
| navigation menu items |
wp_posts |
| categories | wp_terms |
| tags |
wp_terms |
| custom taxonomies | wp_term_taxonomy |
| taxonomy terms | wp_terms |
| post metadata | wp_post_meta |
| widgets | wp_options |
| options | wp_options |
| users | wp_users |
| hardcoded content |
wp_posts (if added to posts)wp_options (if added to widgets)Theme and plugin files (if hardcoded) |
| third party content |
wp_posts (if added to posts)wp_options (if added via widgets or plugins)Theme and plugin files (if hardcoded) |
You may have noticed that not all of the database tables are included in that table. That's because some of them are used to store metadata and others are used to store relationships both of which will be covered in more detail later in this series.
Summary
Hopefully you now have a better understanding of how and where WordPress stores different types of data using the database structure. This series will look at all of the aspects of this in more detail.
In the next part, I'll examine relationships between data, and look in more detail at how specific tables are linked and how some are used purely to store data about relationships.
Comments