Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel