StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat are design patterns to support custom fields in an application?
text
Body
copied!<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 "Favorite Payment Method" field to the Customer. Payment methods are defined elsewhere in the system. That brings the subject of "foreign keys" in the picture. Should I try to create constraints to ensure that values stored in the custom field tables are valid values?</p> <p>Thanks</p> <p>======================</p> <p><strong>EDIT 07-27-2009:</strong></p> <p>Thank you for your answers. It seems like the list of approaches is now quite comprehensive. I have selected the option 2 (a single XML column). It was the easiest to implement for now. I will probably have to refractor to a more strongly-defined approach as my requirements will get more complex and as the number of custom fields to support will get larger.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload