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_postswp_postmetawp_commentswp_commentmetawp_userswp_usermeta-
wp_links
wp_term_relationshipswp_term_taxonomywp_termswp_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_userswp_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_blogswp_blog_versionswp_registration_log-
wp_signups
wp_sitewp_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:
| Table | Data | Notes |
|---|---|---|
wp_blogs |
This 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_versions |
The current database version for each site, updated when you update the network. | Three fields: blog_id, db_version and last_updated
|
wp_registration_log |
The admin user created when each site is registered | For each site (identified by blog_id), the table stores the user_id for the administrator, their email address and the date they registered. |
wp_signups |
Stores data on sites which have been signed up for but not activated | Fields 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_site |
Stores the URL for the main site | This 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_sitemeta |
Stores metadata for the network | This 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_sitecategories |
Optional 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_users |
Data 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_usermeta |
The wp_usermeta tables stores all metadata for all site users |
The 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 towp_blogsviawp_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_postswp_xx_postmetawp_xx_commentswp_xx_commentmeta-
wp_xx_links
wp_xx_term_relationshipswp_xx_term_taxonomywp_xx_termswp_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.
Comments