Advertisement
  1. Code
  2. WordPress
Code

Understanding and Working with Data in WordPress

by
Difficulty:IntermediateLength:ShortLanguages:
This post is part of a series called Understanding and Working with Data in WordPress .
Understanding and Working with Relationships Between Data in WordPress

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:

TableData storedLinked to
wp_postsPosts, pages, attachments, revisions and navigation menu itemswp_postmeta (via post_id)
wp_term_relationships(via post_id)
wp_postmetaMetadata for each postwp_posts (via post_id)
wp_commentsCommentswp_posts (via post_id)
wp_commentmetaMetadata for each commentwp_comments (via comment_id)
wp_term_relationshipsRelationships between posts and taxonomieswp_posts (via post_id)
wp_term_taxonomy (via term_taxonomy_id)
wp_term_taxonomyTaxonomies (including categories and tags)wp_term_relationships (via term_taxonomy_id)
wp_termsYour categories and tags and the terms assigned to custom taxonomieswp_term_taxonomy (via term_id)
wp_linksThe links in your blogroll (if you still have one)wp_term_relationships (via link_id)
wp_usersUserswp_posts (via post_author)
wp_user_metaMetadata for each userwp_users (via user_id)
wp_optionsSite 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 TypeTable(s)
postswp_posts
pageswp_posts
custom post typeswp_posts
attachmentswp_posts
linkswp_links
navigation menu itemswp_posts
categorieswp_terms
tagswp_terms
custom taxonomieswp_term_taxonomy
taxonomy termswp_terms
post metadatawp_post_meta
widgetswp_options
optionswp_options
userswp_users
hardcoded contentwp_posts (if added to posts)
wp_options (if added to widgets)
Theme and plugin files (if hardcoded)
third party contentwp_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.

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.