Exploring the role of existing tables
Assuming that most of you are existing WordPress developers, you will have a solid understanding of the existing database table structure. However, I suggest you to continue with this section, as web applications can have a different perspective in using these tables. Based on the functionality, we are going to categorize the existing tables into four sections, named:
- User-related tables
- Post-related tables
- Term-related tables
- Other tables
Let's look at how each table fits into these categories and their role in web applications.
User-related tables
This section consists of two tables for maintaining the user-related information of your application. Let's take a look at the relationship between user-related tables before moving on to explanations. The following diagram will give a brief idea on this:
The following is the explanation of these user-related tables:
wp_users: All the registered users will be stored in this table with their basic details, such as name, e-mail, username, and password.wp_usermeta: This table is used to store additional information about the users as key-value pairs. User roles and capabilities can be considered as the most important user-specific data of this table. Also, we have the freedom of adding any user-related information as new key-value pairs.
Tip
Throughout this chapter, we'll be referring to the WordPress tables with its default prefix wp_. You can change the prefix through the installation process or by manually changing the config file.
Post-related tables
This section consists of two tables for keeping the website's post- and page-related information. Let's take a look at the relationship between post-related tables before moving on to the explanations. The following diagram will give a brief idea on this:
The following is the explanation of these post-related tables:
wp_posts: This table is used to keep all the posts and pages of your website with their details, such as post name, author, content, status, and post type.wp_postmeta: This table is used to keep all the additional details for each post as key-value pairs. By default, it will contain the details, such as page template, attachments, and edit locks. Also, we can store any post-related information as new key-value pairs.
Term-related tables
WordPress terms can be simply described as categories and tags. This section consists of three tables for post-, category-, and tag-related information. Let's take a look at the relationship among term-related tables:
The following is the explanation of these term-related tables:
wp_terms: This table contains the master data for all the new categories and tags, including custom taxonomies.wp_term_taxonomy: This table is used to define the type of terms and the number of posts or pages available for each term. Basically, all the terms will be categorized as category, post-tags, or any other custom terms created through plugins.wp_term_relationships: This table is used to associate all the terms with their respective posts.
Other tables
I have categorized all the remaining four tables in this section as they play a less important or independent role in web applications:
wp_comments: This table is used to keep the user's feedback for posts and pages. Comment-specific details such as author, e-mail, content, and status are saved in this table.wp_commentmeta: This table is used to keep additional details about each comment. By default, this table will not contain much data as we are not associating advanced comment types in typical situations.
The following diagram previews the relationship between the comment-related tables:
The following is the explanation of these comment-related tables:
wp_links: This table is used to keep the necessary internal or external links. This feature is rarely used in content management systems.wp_options: This table acts as the one and only independent table in the database. In general, this is used to save the application-specific settings that don't change often.
Note
You can take a look at the complete entity-relationship diagram of WordPress at http://codex.wordpress.org/images/9/9e/WP3.0-ERD.png.
Now, you should have a clear idea of the role of existing tables and the reasons for their existence in the CMS perspective. Most importantly, our goal is to figure out how these tables work in advanced web applications, and the next section will completely focus on the web application perspective.