title: What are design patterns to support custom fields in an application?
body: <p>We develop a commercial application. Our customers are asking for custom fields support. For instance, they want to add a field to the Customer form.</p>
  <p>What are the known design patterns to store the field values and the meta-data about the fields?</p>
  <p>I see these options for now:</p>
  <p><strong>Option 1</strong>: Add Field1, Field2, Field3, Field4 columns of type varchar to my Customer table.</p>
  <p><strong>Option 2</strong>: Add a single column of type XML in the customer table and store the custom fields' values in xml.</p>
  <p><strong>Option 3</strong>: Add a CustomerCustomFieldValue table with a column of type varchar and store values in that column. That table would also have a CustomerID, a CustomFieldID.</p>
  <pre><code>CustomerID,  CustomFieldID, Value
  10001,       1001,          '02/12/2009 8:00 AM'
  10001,       1002,          '18.26'
  10002,       1001,          '01/12/2009 8:00 AM'
  10002,       1002,          '50.26'
  </code></pre>
  <p>CustomFieldID would be an ID from another table called CustomField with these columns: CustomFieldID, FieldName, FieldValueTypeID.</p>
  <p><strong>Option 4</strong>: Add a CustomerCustomFieldValue table with a column of each possible value type and store values in the right column. Similar to #3 but field values are stored using a strongly-type column.</p>
  <pre><code>CustomerID,  CustomFieldID, DateValue,           StringValue,       NumericValue                 
  10001,       1001,          02/12/2009 8:00 AM,  null,              null
  10001,       1002,          null,                null,              18.26
  10002,       1001,          01/12/2009 8:00 AM,  null,              null
  10002,       1002,          null,                null,              50.26
  </code></pre>
  <p><strong>Option 5</strong>: Options 3 and 4 use a table specific to a single concept (Customer). Our clients are asking for custom field in other forms as well. Should we instead have a system-wide custom field storage system? So instead of having multiple tables such as CustomerCustomFieldValue, EmployeeCustomFieldValue, InvoiceCustomFieldValue, we would have a single table named CustomFieldValue? Although it seems more elegant to me, wouldn't that cause a performance bottleneck?</p>
  <p>Have you used any of those approaches? Were you successful? What approach would you select?
  Do you know any other approach that I should consider?</p>
  <p>Also, my clients want the custom field to be able to refer to data in other tables. For instance a client might want to add a &quot;Favorite Payment Method&quot; field to the Customer. Payment methods are defined elsewhere in the system. That brings the subject of &quot;foreign keys&quot; in the picture. Should I try to create constraints to ensure that values stored in the custom field tables are valid values?</p>