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!

Why not WordPress instead of a commercial AMS?

I’ve spent a number of posts discussing how WordPress could be used in place of an association management system for small associations but when you get right down to it, I don’t believe for a minute that the approach would work for a large association.  In the past, I have discussed how I think future software could be written for the large association but for now I think they are stuck with the commercial options.

Here are a few reasons:

  1. Large associations want what they want and they have the money to pay for it. Lip service is paid to keeping customization costs down but seldom have I seen the large association follow through with this and not cave in to the powerful membership or marketing director who is dead sure that their complex approach is what holds the entire organization together.  No one wants to abandon their complex pricing and discounting schemes.
  2. Software support is vital to a large organization. Depending on a mix of software from small independent vendors is a dangerous thing when you are protecting the crown jewels of the organization.  Though I have found support for many plugins to be good, I have also seen some where it is not so good.  What’s more, once you commit to a specific plugin, it is difficult to swap it out for something else if something no longer works. Then again, support from the large AMS vendors isn’t as responsive as we would like but at least you have somewhere to go to exert pressure where necessary.
  3. Commercial association management software for large associations is not something that small companies can effectively deal with.  Large associations understand this.  Creating the software is expensive and requires a large install base and large staff just to make the ROI work.
  4. Association staff may pay with their job if their decision on what software to buy is not correct.  Remember when companies bought IBM machines and software because they could never be criticized if they did so?  The same is still true today.  Large associations buy software from well-known established companies.  That doesn’t necessarily mean that the implementation will run smoothly but makes it easier to justify later.
  5. Reporting is vital to large associations.  There are a number of aspects to this.  Not only do the large associations need a large set of “standard reports”, to be successful, they need the ability to do effective ad hoc queries and reporting to adequately analyze the business particularly trends in membership and sales.  Database structures that support good ad hoc reporting are by nature complex and the tools must be able to deal and hide (where possible) that complexity.  Next week I’ll do a post on complex reporting from simple database structures.  There is no free lunch!

So…though I have been impressed with what you can do with open source software for smaller associations.   I just can’t believe that it will work for the large group.