Database tables of web applications can be roughly categorized into three sections, namely:

Based on the categories, we can clearly see that the user_activities table falls into the transaction table category. Next, we need to allow the followers to subscribe to developers. So, we need another transaction type table named subscribed_developers. We can assume that most of the transaction type tables will need their own custom tables. For now, we are going to stick with these two tables and additional custom tables will be added in later chapters when needed.

In typical scenarios, we create the database tables manually before moving into the implementation. With the WordPress plugin-based architecture, it's certain that we might need to create custom tables using plugins in the later stages of the projects. Creating custom tables through plugins involves certain predefined procedures recommended by WordPress. Since table creation is a one-time task, we can implement the process on plugin activation. So, let's get started by creating a new folder named wpwa_database_manager inside the /wp-content/plugins folder.

Then, create a PHP file inside the folder and save it as wpwa-database-manager.php. Now it's time to add the plugin definition as shown in the following code:

<?php
class WPWA_Database_Manager {

  public function __construct(){
    register_activation_hook( __FILE__, array( $this, 'create_custom_tables' ) );
  }

  public function create_custom_tables() {
    // Creating Database Tables
  }
}

Once the activation hook is defined inside the plugin constructor, we can implement the create_custom_tables function to create the necessary tables for our application. Basically, we can execute direct SQL queries using the $wpdb->query function to create all the tables we need. WordPress recommends using a built-in function called dbDelta for creating custom tables. This function is located in a file outside the default process and hence we need to load it manually within our plugins. Let's create two tables for our application using the dbDelta function.

public function create_custom_tables() {
  global $wpdb;
  $table_name = $wpdb->prefix.user_activities;

  require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

  $sql = "CREATE TABLE $table_name (
    id mediumint(9) NOT NULL AUTO_INCREMENT,
    time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
    user_id mediumint(9) NOT NULL,
    activity text NOT NULL,
    url VARCHAR(255) DEFAULT '' NOT NULL,
    UNIQUE KEY id (id)
  );";

  dbDelta($sql);

  // subscribed_developers will be created in a similar manner
}

Initially, we have to include the upgrade.php file to make use of the dbDelta function. The next most important thing is to use the prefix for database tables. By default, WordPress creates a prefix named wp_ for all the tables. It's important to use the existing prefix to keep consistency and avoid issues in multisite scenarios. Finally, you can define your table creation query and use the dBDelta function to implement it on the database.

We created the custom tables using the dbDelta function inside plugin activation. WordPress recommends the dbDelta function over direct SQL queries for table creation since it examines the current table structure, compares it with the desired table structure, and makes the necessary modifications without breaking the existing database tables. Apart from table creation, we can execute quite a few database-related tasks on plugin activation, such as altering tables, populating initial data to custom tables, and upgrading the plugin tables.

We looked at the necessity of custom tables for web applications. Even though custom tables offer you more flexibility within WordPress, there will be a considerable amount of limitations, as listed in the following section:

Therefore, you should avoid creating custom tables in all possible scenarios unless you have a distinct advantage from the perspective of your application.

Detailed exploration about the PODS framework will be provided in the next chapter, Chapter 4, The Building Blocks of Web Applications.