title: How to design a database for User Defined Fields?
body: <p>My requirements are:</p>
  
  <ul>
  <li>Need to be able to dynamically add User-Defined fields of any data type</li>
  <li>Need to be able to query UDFs quickly</li>
  <li>Need to be able to do calculations on UDFs based on datatype </li>
  <li>Need to be able to sort UDFs based on datatype</li>
  </ul>
  
  <p>Other Information:</p>
  
  <ul>
  <li>I'm looking for performance primarily</li>
  <li>There are a few million Master records which can have UDF data attached</li>
  <li>When I last checked, there were over 50mil UDF records in our current database</li>
  <li>Most of the time, a UDF is only attached to a few thousand of the Master records, not all of them</li>
  <li>UDFs are not joined or used as keys. They're just data used for queries or reports</li>
  </ul>
  
  <p>Options:</p>
  
  <ol>
  <li><p>Create a big table with StringValue1, StringValue2... IntValue1, IntValue2,... etc. I hate this idea, but will consider it if someone can tell me it is better than other ideas and why.</p></li>
  <li><p>Create a dynamic table which adds a new column on demand as needed. I also don't like this idea since I feel performance would be slow unless you indexed every column.</p></li>
  <li><p>Create a single table containing UDFName, UDFDataType, and Value. When a new UDF gets added, generate a View which pulls just that data and parses it into whatever type is specified. Items which don't meet the parsing criteria return NULL.</p></li>
  <li><p>Create multiple UDF tables, one per data type. So we'd have tables for UDFStrings, UDFDates, etc. Probably would do the same as #2 and auto-generate a View anytime a new field gets added</p></li>
  <li><p>XML DataTypes? I haven't worked with these before but have seen them mentioned. Not sure if they'd give me the results I want, especially with performance.</p></li>
  <li><p>Something else? </p></li>
  </ol>