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.
What are the known design patterns to store the field values and the meta-data about the fields?
I see these options for now:
Option 1: Add Field1, Field2, Field3, Field4 columns of type varchar to my Customer table.
Option 2: Add a single column of type XML in the customer table and store the custom fields' values in xml.
Option 3: 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.
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'
CustomFieldID would be an ID from another table called CustomField with these columns: CustomFieldID, FieldName, FieldValueTypeID.
Option 4: 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.
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
Option 5: 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 multipl