Simple Database Structures Lead to Complex Reporting

It is interesting to look under the covers of WordPress to the database architecture see how people are creating plug-ins to deal with so many different functions.  Take a look at the table wp_usermeta that stores information about users.  The structure is simple but perhaps too simple.  Ultimate flexibility often leads to ultimate complexity in reporting.  Looking at the structure and how some plug-ins use it will reveal another major reason why the WordPress approach would not work for large organizations.  The structure of wp_usermeta:

  • umeta_id
  • user_id
  • meta_key
  • meta_value

This is the ultimate in versatile database structure.  You have a unique key (umeta_id) for the table and then a field to link to a specific user (user_id).  Beyond that, you can define anything you want as a meta_key and then provide one or many values within meta_value.  This can be as simple as:

meta_key=’lastname’ and meta_value = ‘Brantigan’

In a typical relational database structure, you would define a “customer” table with specific fields for first name, last name, and other demographics.  For data elements that are less specific to every customer or one to many relationships like specific product orders for a given customer you might create a separate child table.  When this is the case, you have relatively easy reporting – but not with wp_usermeta.

What happens in WordPress for some plugins, the vendors use the wp_usermeta table for everything and then structure their data within the one meta_value field.  Here is a rather complex example used by Magic Members plugin:

meta_key = ‘mgm_member_options’ and meta_value = ‘a:2333:{s:2:”id”;s:1:”6″;s:13:”custom_fields”;a:14:{s:8:”username”;s:0:””;s:8:”password”;s:10:””;s:10:”password_conf”;s:1:””;s:10:”first_name”;s:3:”Tom”;s:9:”last_name”;s:9:”Brantigan”;s:5:”email”;s:25:””;s:9:”boat_name”;s:14:”Toccata in Sea”;s:10:”boat_model”;s:4:”C387″;s:11:”hull_number”;s:2:”96″;s:9:”home_port”;s:16:”Middle River, MD”;s:12:”display_name”;s:13:”idealuser”;s:8:”jib_size”;s:0:””;s:23:”sail_configuration_main”;s:3:”Std”;s:9:”keel_type”;s:3:”Fin”;}s:22:”other_membership_types”;a:0:{}s:12:”payment_info”;a:0:{}s:6:”coupon”;a:0:{}s:7:”upgrade”;a:0:{}s:6:”extend”;a:0:{}s:4:”code”;s:10:”mgm_member”;s:4:”name”;s:10:”Member Lib”;s:11:”description”;s:10:”Member Lib”;s:7:”setting”;a:0:{}s:6:”saving”;s:1:”1″;s:8:”trial_on”;s:1:”0″;s:10:”trial_cost”;s:1:”0″;s:14:”trial_duration”;s:1:”0″;s:19:”trial_duration_type”;s:1:”d”;s:16:”trial_num_cycles”;s:1:”0″;s:8:”duration”;s:1:”1″;s:13:”duration_type”;s:1:”l”;s:6:”amount”;s:1:”0″;s:8:”currency”;s:3:”USD”;s:9:”join_date”;s:10:”1327096345″;s:13:”last_pay_date”;s:10:”2012-01-27″;s:11:”expire_date”;s:0:””;s:15:”membership_type”;s:6:”member”;s:6:”status”;s:6:”Active”;s:12:”payment_type”;s:0:””;s:13:”user_password”;s:16:”6cf89Y9K8AX3W8TNT”;s:7:”pack_id”;s:1:”3″;s:17:”active_num_cycles”;s:0:””;s:12:”account_desc”;s:12:”Free Account”;s:10:”status_str”;s:27:”Last payment was successful”;s:16:”hide_old_content”;s:0:””;}’

Though you can do almost anything with such a generic table structure, trying to do a simple SQL query against it to create analytic reports is really tough!

Leave a Reply