Hostingheaderbarlogoj
Join InMotion Hosting for $3.49/mo & get a year on Tuts+ FREE (worth $180). Start today.
Advertisement

Understanding and Working with Data in WordPress - Multisite

by
Gift

Want a free year on Tuts+ (worth $180)? Start an InMotion Hosting plan for $3.49/mo.

This post is part of a series called Understanding and Working with Data in WordPress .
Understanding and Working with The WordPress Options Table

In the earlier parts of this series, we've worked through the different tables in the WordPress database and examined what data they store, how they interact with each other and what functions you use to interact with them.

In this final part, I'll move on to look at Multisite. The database for a WordPress Multisite installation will contain extra tables: a set of tables for each site running on the network, and some extra tables for the network itself, as well as the tables for the core site. 

This means there are three elements to understanding how the database works in Multisite:

  • Database tables for the core site
  • Database tables for the network
  • Database tables for each additional site.

I'll start by looking at how WordPress stores data for the core site.

Database Tables for the Core Site in Multisite

For the core site in a network, WordPress uses the same 11 database tables as in a single site installation:

  • wp_posts
  • wp_postmeta
  • wp_comments
  • wp_commentmeta
  • wp_users
  • wp_usermeta
  • wp_links
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_terms
  • wp_options

These will contain data relating to the main site. Two of them will also contain data relating to the rest of the network. These are:

  • wp_users
  • wp_usermeta

The other nine tables work in the same way as they do for a single site - they store data about the main site's content. However those last two tables will also store user data that is relevant to the whole network, as well as user data which is only used by the core site. I will examine this in a moment.

Database Tables for the Network

As well as storing user data for the whole network in two of the core tables, WordPress also creates additional tables in a Multisite installation which hold data relating to the network and its sites. 

These are:

  • wp_blogs
  • wp_blog_versions
  • wp_registration_log
  • wp_signups
  • wp_site
  • wp_sitemeta
  • wp_sitecategories (optional)

WordPress uses these seven (or six) tables plus the two user tables to store all of the data about the Multisite network. Here's what each of them stores:

TableDataNotes
wp_blogsThis field stores details each site in the network, so it only has one record for each site.Fields include blog_id, domain, registered (the data on which the site was created) and last_updated (again, a date).
wp_blog_versionsThe current database version for each site, updated when you update the network.Three fields: blog_id, db_version and last_updated
wp_registration_logThe admin user created when each site is registeredFor each site (identified by blog_id), the table stores the user_id for the administrator, their email address and the date they registered.
wp_signupsStores data on sites which have been signed up for but not activatedFields include a unique signup_id for each record, the domain signed up for, the title, the user login and email address. Once a site is activated this record is deleted and a record is created in wp_blogs.
wp_siteStores the URL for the main siteThis table has only one record with three fields: the id of the main site (which will be 1), its domain and the path (normally /)
wp_sitemetaStores metadata for the networkThis table is the equivalent of wp_options for the entire network. It contains all of the metadata relating to the network settings plus smaller amounts of metadata for individual sites. It has four fields:  a unique meta_id, site_id (which links to wp_blogs), meta_key and meta_value.
wp_sitecategoriesOptional table only created if global terms are enabled for a site.Enabling global terms allows you to use terms across multiple sites in the network. The table (if it is created), will have four fields: cat_ID, cat_name, category_nicename and last_updated. Note that this tables stores terms, not just categories, despite the field names.
wp_usersData on all users is stored here rather than for each site, as individual users can have access to multiple sites on the network.In a Multiste installtion WordPress creates two extra fields in the wp_users table: spam and deleted, both of which are Boolean values defaulting to NO.
wp_usermetaThe wp_usermeta tables stores all metadata for all site usersThe table is used in the same way as for a single site installation.

Relationships Between the Tables

Most of these tables are related to the wp_blogs table via the blog_ID field, as all of the data needed for the sites in the network will need to be linked to the core site record. The excepts are:

  • wp_sitecategories, which links to posts and other content
  • wp_signups, as these aren't registered sites yet
  • wp_usermeta, which has an indirect link to wp_blogs via wp_users

For more detail on each of these tables and their fields, see the Codex page on the database.

Database Tables for Sites in a Network

The way in which WordPress stores the data for each of the sites in your network is fairly straightforward: it creates multiple copies of each of the database tables, one for each site. However it doesn't create additional copies of wp_users and wp_usermeta as these are all stored in the main table.

To differentiate between the tables for each site, WordPress adds the site ID to the table name, so for example for site 2, wp_posts becomes wp_2_posts.

Each site will have the following tables:

  • wp_xx_posts
  • wp_xx_postmeta
  • wp_xx_comments
  • wp_xx_commentmeta
  • wp_xx_links
  • wp_xx_term_relationships
  • wp_xx_term_taxonomy
  • wp_xx_terms
  • wp_xx_options

The xx above will be replaced by the numeric site ID. All of these tables store data in the same way for each site as they would if the site was a single site installation.

Summary

If you're manipulating, retrieving or moving data from a WordPress Multisite installation, it's important to understand the ways in which Multisite stores data differently from a Multisite installation. 

As I've shown, there are three main elements to this: tables used for the core site; tables used to store database about the network as a whole; and tables created for each new site. The structure of these is similar to a single site installation as long as you remember that the two tables storing data on users are not duplicated for each site.

Advertisement