Adapting existing tables into web applications
Unlike content management systems, web applications have the possibility of scaling infinitely as they become popular and stable. Such systems can contain hundreds of database tables to cater to various aspects. Here, we are trying to build such applications using this popular CMS framework. Therefore, we need to figure out the features that we can build using the existing tables and the features that should need their own table structures.
We should try to maximize the use of existing tables in every possible scenario to get the most out of WordPress. Built-in database access functions are optimized to work directly with the existing tables, allowing us to minimize the time for implementation. On the other hand, we need to write custom queries from scratch to work with newly created tables. Let's find out the possible ways of adapting the existing tables using the four categories we discussed in the previous section.
User-related tables
The role of user tables in web applications is similar to that in normal CMS. Therefore, we don't have to worry about changing the default functionality. Any other user-related functionalities should be associated with the wp_usermeta table. Let's recall the user activation feature we implemented in Chapter 2, Implementing Membership Roles, Permissions, and Features. We had an additional requirement for activating users before login. We made use of the new wp_usermeta field named wpwa_activate_status to build this functionality. Now, open your database explorer, and take a look at the fields of the wp_users table. You will find a column named user_activation_key with an empty value. This field could have been easily used for the activation functionality. Table columns such as user_activation_key and user_status are used by WordPress for providing the core functionality. There is every chance that other plugin developers are using these fields with a different meaning and hence creating the possibility of loss of data and conflicts.
Tip
It's a good rule of thumb to use metatables or custom tables for advanced functionalities with your own unique keys by using a prefix, instead of relying on existing columns of core tables.
Therefore, we choose the wp_usermeta table to keep the activation status of all the users. Other plugin developers can also implement similar functionalities with the unique keys inside the wp_usermeta table. In short, the wp_usermata table can be used effectively to create advanced user-related functionalities in web applications as long as it doesn't involve a one-to-many relationship. It's possible to use multiple meta fields with the same name. However, most developers will prefer the use of custom tables for features that require multiple data rows to be associated with the single user, allowing additional flexibility in data filtering.
Post-related tables
Usually, the wp_posts and wp_postmeta tables will act as the main data storage tables for any web application. With the introduction of custom posts, we have the capability of matching most of our application data with these two tables. In web applications, we can go beyond normal posts by creating various custom post types. Let's take a look at a few practical scenarios for identifying the role of the wp_posts and wp_postmeta tables.
Scenario 1 – an online shopping cart
Assume that we are building an online shopping cart application to sell books. In this scenario, books can be matched to a custom post type to be saved in the wp_posts table. We can match the post title as book title, post content as book description, and post type as book. Then, all the other book-related information such as price, author, pages, and dimensions can be stored in the wp_postmeta table with the associated book from the wp_posts table.
Scenario 2 – hotel reservation system
In this scenario, we need to provide the ability to book hotel rooms through the system. We can use a custom post type called rooms to keep the details of various types of rooms inside the wp_posts table. All the additional room-specific data, such as room type, check in/out dates, and number of people, can be created using additional fields in the wp_postmeta table.
Scenario 3 – project management application
Let's consider a much more advanced scenario in creating relationships among post types. Assume that we have been assigned to build a project management application with WordPress. We can match projects as a custom post type. Project-specific details such as project manager, duration, and cost will be stored in the wp_postmeta table. It's not ideal to use the wp_postmeta table to store project tasks since each project contains multiple tasks, and a single project task can contain its own attributes and values. Therefore, we create another custom post type to store project tasks and all the task-related data is stored inside the wp_postmeta table. Finally, we can associate projects with the tasks using taxonomies or a separate custom table.
Up until now, we discussed three completely different scenarios in real-world applications, and we were able to match all the requirements with the custom post types. Now, you should be able to understand the importance of these two tables in web application development. In Chapter 4, The Building Blocks of Web Applications, we will be continuing our exploration on the custom post types and the usage of these two tables.
Term-related tables
Even though they're not as important as posts, terms will play a vital part in supporting post functionalities. Let's see how we can effectively use the terms in the previous three scenarios:
- Scenario 1: In the book store, we can use terms to store book categories or book types, such as e-books, Kindle editions, and printed books
- Scenario 2: In the hotel reservation system, we can use terms to select services and facilities required for rooms
- Scenario 3: In the project management system, we can associate terms for defining the complexity of the given task
It's important to keep in mind that multiple terms can be associated with a single post. So, it's not the wisest thing to use terms for a feature such as project status.
Other tables
In this section, we are going to discuss the practical usage of the wp_comments, wp_comment_meta, and wp_options tables. The wp_links table is skipped on purpose as we don't generally require it in web application development.
Note
Link manager is hidden by default for new WordPress installations since Version 3.5, proving that links are not considered a major aspect in WordPress.
Comments might not indicate a significant value with its default usage. However, we can certainly think of many ways of incorporating comments into actual web applications. In the previous section, we talked about custom post types. So what about custom comment types? Definitely, we can implement custom comment types in web applications. The only difference is that custom post types are defined in the posts table, while custom comment types will have to be handled manually as they're not supported yet in WordPress.
Let's recall the example in Chapter 1, WordPress As a Web Application Framework, where we created the question-answer interface using posts and comments. Answers were considered as the custom comment type. Similarly, we can match things such as bids in auctions, reviews in books, and ratings for movies as custom comment types to be stored in the wp_comment_meta table. Since the column named comment_type is not available, we have to use a meta key named wpwa_comment_type to filter different comments from each other.
Finally, we are going to take a look at the wp_options table for system-wide configurations. By default, this table is populated with the settings to run the website. WordPress theme settings will also be stored in this table. In web applications, we will definitely have a considerable number of plugins. So, we can use this table to store the settings of all our plugins.
Note
Most of the existing WordPress plugins use a single field to store all the settings as a serialized array. It's considered a good practice that increases the performance due to a limited number of table records.
Up until this point, we explored the role of the existing tables and how we can adapt them in real-world web applications. Complex web applications will always come up with requirements for pushing the boundaries of these tables. In such cases, we have no option other than going with custom tables. So, we will be looking at the importance of custom tables and their usage in the next section.