Separate table or usermeta

Question

We are creating custom fields for users like annual income, gender, city, country, etc. There are about 10 odd new fields. We will be importing approx. 30,000 records for each user in a month through a CSV import function. There will be new as well as update to old records. The process will continue for a year. In this scenario, please suggest which option to choose from

Scenario 1: Create a new WordPress table with these new fields

Scenario 2: Use wp_usermeta to add custom fields

Apart from CSV import, we will be regularly extracting reports of users. This will keep the wp_usermeta table busy.

Please suggest which scenario we should go far and why? My recommendation is to go for Scenario 1 as here we can use WordPress functions quite efficiently though the database load will be high.

Answer 1

The obvious advantage of user meta is that you can use the WordPress API to record and retrieve these extra columns, without writing extra PHP classes or SQL queries. The wp_usermeta table is pretty well indexed, in fact, it uses one row per field (rather than one column if you use a custom table), and you don't have to worry about performance. Using the user meta API typically means faster development if you only want to record extra fields.

But ultimately, it depends on how you'll be retrieving the data. For a custom table, you can do:

SELECT `appname`.* FROM `appname` WHERE `appname`.`income` >= 100000;

However, it'll be a bit more difficult with user meta, and you have to do a join:

SELECT `wp_users`.* FROM `wp_usermeta`
LEFT JOIN `wp_users` ON (`wp_usermeta`.`user_id` = `wp_users`.`ID`)
WHERE `wp_usermeta`.`meta_key` = 'appname_user_income'
AND `wp_usermeta`.`meta_value` >= 100000;

As you can see, this will give you a row in wp_users, to fetch other meta value, you have to call the API based on the user_id you retrieved. (Note, you don't need a left join just to get the user id).

Therefore, if you only want to run simple queries on your meta fields, you can use user meta to save you a lot of development time. But if writing those queries will be complicated, it's possible that using your own table would be a better choice.