title: Building a MySQL database that can take an infinite number of fields
body: <p>I am building a MySQL-driven website that will analyze customer surveys distributed by a variety of clients. Generally, these surveys are structured fairly consistently, and most of our clients' data can be reduced to the same normalized database structure.</p>
  
  <p>However, every client inevitably ends up including highly specific demographic questions for their customers that are irrelevant to every other one of our clients. For instance, although all of our clients will ask about customer satisfaction, only our auto clients will ask whether the customers know how to drive manual transmissions.</p>
  
  <p>Up to now, I have been adding columns to a <code>respondents</code> table for all general demographic information, with a lot of <code>default null</code>'s mixed in. However, as we add more clients, it's clear that this will end up with a massive number of columns which are almost always null.</p>
  
  <p>Is there a way to do this consistently? I would rather keep as much of the standardized data as possible in the <code>respondents</code> table since our import script is already written for that table. One thought of mine is to build a <code>respondent_supplemental_demographic_info</code> table that has the columns response_id, demographic_field, demographic_value (so the manual transmissions example might become: 'ID999', 'can_drive_manual_indicator', true). This could hold an infinite number of demographic_fields, but would be incredible painful to work with from both a processing and programming perspective. Any ideas?</p>