Understanding and Working with Data in WordPress

Share the joy

•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  

A WordPress website consists of three main elements:

1. The WordPress installation itself


2. The contents of the wp-content directory which includes the themes, plugins and uploads


3. 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:

1. Introduction


2. Relationships between data


3. Content types


4. User data


5. Metadata


6. Taxonomies, categories, tags and terms


7. Taxonomies vs post metadata


8. The options table


9. 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_posts table, 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_options table. 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_users and wp_comments tables 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.

Share the joy

•  
•  
•  
•  
•  
•  
•  
•  
•  
•  
•  

Understanding and Working with Data in WordPress