I’ve built an enterprise WordPress site and we are currently designing an automated import of company user data directly into the WordPress database, so it’s not manual. This user data has more data points than the default WP user fields (user_login, first name, last name, email, url, etc…) can store. So I was wondering what the recommended architecture is for storing additional user fields?
My first thought was to add additional columns to the custom OpenID plugin I forked. It’s main purpose is to use the OpenID framework to leverage our corporate OpenID identifier to authenticate associates. This plugin creates a simple wp_openid_identities table in the database on activation:
uurl_id | user_id | url | hash | phone | state | country
------------------------------------------------------------------
1 | 1 | http://idp.example.com/user/9999 | 1^f3d5g7e9W!2 | 555-555-5555 | WA | USA
Or use the user_meta table and custom rows:
umeta_id | user_id | meta_key | meta_value
1 | 1 | first_name | Bill
2 | 1 | last_name | Gates
3 | 1 | nickname | bg000001
4 | 1 | rich_editing | true
5 | 1 | comment_shortcuts | false
6 | 1 | admin_color | fresh
7 | 1 | use_ssl | 0
8 | 1 | show_admin_bar_front | true
9 | 1 | wp_capabilities | a:1:{s:13:"administrator";b:1;}
10 | 1 | phone | 555-555-5555
11 | 1 | state | WA
12 | 1 | country | USA
etc…
I would guess that the user_meta table is the recommended place but the user base is currently 25,000+ users and growing with 12 additional custom user data points. That’s 19 default usermeta keys + 12 more (31). 25,000 * 31 = 7,750,000 rows!
Reporting: Join wp_users, wp_usermeta, and wp_custom_table (user submitted form data) where user_id is constant. This needs to be exported or echoed in a table format One row with all user info and form data.