Adding a custom table to the WordPress database

Many times in a WordPress plugin you want to store data in an easy accessible way without bloating the default WP tables. That’s when a custom database table comes in handy.

This isn’t something new just a way to do it clean and some warnings from troubles encountered in live environments.

The code

This is just a simple example of a database class which I include in custom plugins:

class M_Db {

    private static $db_version = '1.0.0';
    const DB_VERSION_NAME = 'm_db_version';
    const POSTS_TABLE = 'm_info';

    public static function install() {
        global $wpdb;

        $installed_ver = get_option(self::DB_VERSION_NAME);

        if ( $installed_ver != self::$db_version ) {

            $table_name = $wpdb->prefix . self::POSTS_TABLE;

            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) DEFAULT 0 NOT NULL,
            region VARCHAR(50) NOT NULL,
               UNIQUE KEY id (id)
           ) $charset_collate;";

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

            update_option( self::DB_VERSION_NAME, self::$db_version );
        }
    }

}

This code will allow you to create a custom table and also easily update its structure using the version check. After a change is made ( like adding a new column ) you just need to change the static version and the table will be updated.

Of course, you don’t want to run this code each time the website is loaded so you’ll have to call the “install” function from the proper hook.

In a plugin ( and hopefully you won’t be creating custom tables with a theme! ) you should run this on plugin activation by adding this code to the main plugin file:

register_activation_hook( __FILE__, array( 'M_Db', 'install' ) )

Warnings

if you have a column for user_id – make sure you match its format to the WordPress wp_user ID format. You wouldn’t want user ids to get saved as the maximum value of an integer for all the users with a larger id number – this applies for any relation column.
make sure you consider adding a case for plugin deactivation if that’s necessary using register_deactivation_hook.
on the same note above – if the plugin will be present in a repository, make sure you handle updates properly.