title: SQL Server/Table Design, table for data snapshots where hundreds of columns possible
body: <p>We have a business process that requires taking a "snapshot" of portions of a client's data at a point in time, and being able to regurgitate it later.  The data set has some oddities though that make the problem interesting:</p>
  
  <ul>
  <li>The data is pulled from several databases, some of which are not ours.</li>
  <li>The list of fields that could possibly be pulled are somewhere between 150 and 200</li>
  <li>The list of fields that are typically pulled are somewhere between 10 and 20.</li>
  <li>Each client can pull a custom set of fields for storage, this set is pre-determined ahead of time.</li>
  </ul>
  
  <p>For example (and I have vastly oversimplified these):</p>
  
  <ul>
  <li>Client A decides on Fridays to take a snapshot of customer addresses (1 record per customer address).</li>
  <li>Client B decides on alternate Tuesdays to take a snapshot of summary invoice information (1 record per type of invoice).</li>
  <li>Client C monthly summarizes hours worked by each department (1 record per department).</li>
  </ul>
  
  <p>When each of these periods happen, a process goes out and fetches the appropriate information for each of these clients... and does something with them.</p>
  
  <p>Sounds like an historical reporting system, right?  It kind of is.  The data is later parsed up and regurgitated in a variety of formats (xml, cvs, excel, text files, etc..) depending on the client's needs.</p>
  
  <p>I get to rewrite this.</p>
  
  <p>Since we don't own all of the databases, I can't just keep references to the data around.  Some of that data is overwritten periodically anyway.  I actually need to find the appropriate data and set it aside.</p>
  
  <p>I'm hoping someone has a clever way of approaching the table design for such a beast.  The methods that come to mind, all with their own drawbacks:</p>
  
  <ol>
  <li><p>A dataset table (data set id, date captured, etc...); 
  A data table (data set id, row number, "data as a blob of crap")</p></li>
  <li><p>A dataset table (data set id, date captured, etc....); 
  A data table (data set id, row number, possible field 1, possible field 2, possible field 3, ...., possible field x (where x > 150)</p></li>
  <li><p>A dataset table (data set id, date captured, etc...); A field table (1 row per all possible field types); A selected field table (1 row for each field the client has selected); One table for each primitive data type possible (varchar, decimal, integer) (keyed on selected field, data set id, row, position, data is the single field value).</p></li>
  </ol>
  
  <p>The first being the easiest to implement, but the "blob of crap" would have to be engineered to be parseable to break it down into reportable fields.  Not very database friendly either, not reportable, etc..  Doesn't feel right.</p>
  
  <p>The second is a horror show of columns.  <em>shudder</em></p>
  
  <p>The third sounds right, but kind of doesn't.  It's 3NF (yes, I'm old) so feels right that way.  However reporting on the table screams of "rows that should have been columns" problems -- fairly useless to try to select on outside of a program.</p>
  
  <p>What are your thoughts?</p>