Generally speaking, a database usually acts as the primary storage location for keeping your web application data accessible from frontend interfaces or any third-party systems. Planning and designing the database should be one of the highest priority tasks in the initial stages of a project.
As developers, we have the chance to design the database from scratch in many web applications. WordPress comes with a prestructured database and hence the task of planning the table structure and adapting to existing tables becomes much more complex than everyone thinks. In this post, we are going to focus on the basics of planning and accessing the database for web applications.
Typical full stack web development frameworks don’t come with a preplanned database structure. Instead, these frameworks focus on the core foundation of an application while allowing the developers to focus on application-specific features.
On the other hand, WordPress provides a preplanned database structure with a fixed set of tables. WordPress is built to function as a content management system and hence it can be classified as a product rather than a pure development framework.
A WordPress core database is designed to power the generic functionalities of a CMS. So, it’s our responsibility to use our skills to make it work as an application development framework.
Our WordPress database is intended to work with MySQL or MariaDB and hence we need to have a MySQL database installed and setup before installing WordPress.
On successful installation, WordPress will create 11 database tables to cater to core functionality with the default MySQL table engine.
The WordPress core functionality will always be limited to these 11 tables, and it’s quite surprising to see the flexibility of building a wide range of applications with such a limited number of tables.
Both WordPress and framework developers need to have a thorough understanding of the existing tables in order to associate them with web applications.
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 continue with this section, as web applications can have a different perspective in using these tables. Based on the functionality, we are going to categorise 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 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.”
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 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
The WordPress taxonomy system 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 grouped 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 categorised 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 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.
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.
