CODEWITHSUNDEEP
wordpresswoocommerce
Itergator
Itergator

Reputation: 299

Wordpress table key relationships

I am writing a Wordpress plugin extending Woocommerce that requires to store additional information about a user.

I understand that there is a separate wp_user_meta table specific to this, but I would rather use a custom table as I plan to use Wordpress/Woocommerce as an initial platform then hopefully expand to others. The data will also be queried often.

This custom table requires a single entry per user, defined by the ID in wp_user, therefore essentially being a one-to-one relationship. An example schema for the custom table is:

CREATE TABLE {$wpdb->prefix}custom_table (
  custom_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  custom_data varchar(200) NULL,
  FOREIGN KEY (user_id) REFERENCES wp_users(ID),
  PRIMARY KEY (custom_id)
)

My question is using Wordpress, how do you ensure that the one-to-one relationship is maintained?

I know I will have to run an initial setup to create the entries in the custom table for existing users.

Other than this, are users allowed to change their ID at all in the user lifecycle? Is it a case of using registration/deletion hooks to ensure that the data in the custom table is up to date? (Or would the deletion cascade through to other tables when it happens in the wp_user table)

Upvotes: 1

Views: 1476

Answers (2)

cabrerahector
cabrerahector

Reputation: 3948

AFAIK, users can't change their IDs nor their usernames (because, well, that leads to a whole lot of issues) so you're fine in that regard.

Also, there are a few "gotchas!" you need to be aware of:

  1. Your query will fail because user_id isn't being defined exactly the same way as the ID column in wp_users. You have: user_id BIGINT UNSIGNED NOT NULL. It should be: user_id BIGINT(20) UNSIGNED NOT NULL.

  2. To make user_id sure is unique -and maintain the one-to-one relationship- you'll need to add UNIQUE(user_id) to your query.

  3. Your table has to use the exact same storage engine. In your query, you're not defining a storage engine for it so MySQL will fallback to the default one, which might or might not be the same one used by the wp_users table. If it's not the same one, the query will -again- fail and your table won't be created.

Here's the revised query, assuming wp_users uses the MyISAM storage engine:

CREATE TABLE {$wpdb->prefix}custom_table (
  custom_id bigint unsigned NOT NULL AUTO_INCREMENT,
  user_id bigint(20) unsigned NOT NULL,
  custom_data varchar(200) NULL,
  FOREIGN KEY (user_id) REFERENCES wp_users(ID) ON DELETE CASCADE,
  PRIMARY KEY (custom_id),
  UNIQUE (user_id)
) ENGINE=MyISAM

If you want to make sure your table uses the exact same storage engine as the wp_users table -and you do- running this query before creating your table will tell you which one you need:

SELECT ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA='{$wpdb->dbname}' AND TABLE_NAME='{$wpdb->users}';

Upvotes: 4

Itergator
Itergator

Reputation: 299

For reference the code snippet - note that checks should be in place for checking the array size etc. but this gets me in the right direction. Thank you @cabrarahector

require_once ABSPATH . 'wp-admin/includes/upgrade.php';
global $wpdb;

$query = "SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='{$wpdb->dbname}' AND TABLE_NAME='{$wpdb->users}';";
$engine = $wpdb->get_results( $query )[0]->ENGINE;

$sql = "CREATE TABLE {$wpdb->prefix}custom_table (
    custom_id bigint unsigned NOT NULL AUTO_INCREMENT,
    user_id bigint(20) unsigned NOT NULL,
    custom_data varchar(200) NULL,
    FOREIGN KEY (user_id) REFERENCES wp_users(ID) ON DELETE CASCADE,
    PRIMARY KEY (custom_id),
    UNIQUE (user_id)
) ENGINE={$engine};";

dbDelta($sql);

this creates the custom table in the database

Upvotes: 1

Related Questions