Master Data Management in WordPress

A WordPress website consists of three main elements:

1. WordPress installation itself
2. wp-content Contents of the directory, including themes, plugins and uploaded content
3. Database, stores all content.

Most WordPress users have never directly touched a database and may not even be aware that it is constantly populating their website. When WordPress serves any type of page, whether it’s a home page, a single post or page, or an archive, it accesses the database to display the content that editors and administrators have added to the site.

In this series of tutorials, I will detail different aspects of the WordPress database. The series will be divided into nine parts and will cover the following:

1. Introduction
2. Relationships between data
3. Content type
4. User data
5. Metadata
6. Taxonomies, categories, tags and terms
7. Taxonomy and Post Metadata
8. Option table
9. WordPress Multisite Data

In this introduction, I will provide an overview of database tables and how they relate to the content types you may be used to working with in WordPress, and identify where to store content.

Content Types in WordPress

Since database tables are used to store content, you need to understand the content before understanding them. There are many types of content in WordPress:

• Post
• page
• Custom post type
• appendix
• Link
• Navigation menu items (stored as separate posts)

These content types have data attached:

• category
• Label
• Custom taxonomies and terms
• Publish metadata

In addition to this, there are other types of content that are stored in different ways:

• small parts
• Options
• user
• Site (for multi-site installation)
• Hardcoded content (added to your theme or plugin)
• Content from elsewhere (third-party content accessed via feed, streaming or other technology)

All of these types of content are stored somewhere in the database (or occasionally in a theme or plugin file, as I'll show). They may have their own entry, or they may be part of another entry (such as streaming content encoded into a post). They can 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.

WordPress Database Structure

WordPress uses a series of database tables and the relationships between them to minimize the amount of data that must be stored - this creates a one-to-many relationship. This means that a user can have many posts related to their user record. It saves space - If WordPress stored all user data for every post written by each user, it would mean a lot of duplicate data and a lot of space.

The following image is taken from the WordPress codex and shows the database tables and how they are linked:

Most forms are linked to one or more other forms through a field. This field will be a unique identifier for each record, such as post_id. This table shows this in more detail:

There are several points worth noting:

• By default, database tables have the wp_ prefix. You can change this setting when configuring your site, but it's not of much value.
• The core table is the wp_posts table where most of the data will be stored. This ties (almost) everything else together.
• There is only one table that is not attached to any other table - the wp_options table. This table stores data about the website and WordPress installation that is independent of 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 must be registered to post comments, WordPress does not actually store data about comments for each user who posts them .
• Multi-site installations will have some additional tables. I have not included these here as it is beyond the scope of this tutorial.

Link content to database table

After looking at the content types in WordPress and the database tables used to store them, it might be helpful to match the two. The following table shows which database table is used to store each type of content.

You may have noticed that not all database tables are included in this table. This is 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 uses database structures to store different types of data. This series will explore all aspects of this issue in more detail.

In the next section, I will examine the relationships between data and look in more detail at how specific tables are linked and how some tables are used purely to store data about relationships.