What are design patterns to support custom fields in an application?

Question

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 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?

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?

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?

Thanks

======================

EDIT 07-27-2009:

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.

Answer 1

I do agree with posters below that Options 3, 4, or 5 are most likely to be appropriate. However, each of your suggested implementations has its benefits and costs. I'd suggest choosing one by matching it to your specific requirements. For example:

1. Option 1 pros: Fast to implement. Allows DB actions on custom fields (searching, sorting.)
   Option 1 cons: Custom fields are generic, so no strongly-typed fields. Database table is inefficient, size-wise with many extraneous fields that will never be used. Number of custom fields allowed needs to be anticipated.
2. Option 2 pros: Fast to implement. Flexible, allowing arbitrary number and type of custom fields.
   Option 2 cons: No DB actions possible on custom fields. This is best if all you need to do is display the custom fields, later, or do minor manipulations of the data only on a per-Customer basis.
3. Option 3 pros: Both flexible and efficient. DB actions can be performed, but the data is normalized somewhat to reduce wasted space. I agree with unknown (google)'s suggestion that you add an additional column that can be used to specify type or source information. Option 3 cons: Slight increase in development time and complexity of your queries, but there really aren't too many cons, here.
4. Option 4 is the same as Option 3, except that your typed data can be operated on at the DB level. The addition of type information to the link table in Option 3 allows you to do more operations at our application level, but the DB won't be able to do comparisons or sort, for example. The choice between 3 and 4 depends on this requirement.
5. Option 5 is the same as 3 or 4, but with even more flexibility to apply the solution to many different tables. The cost in this case will be that the size of this table will grow much larger. If you are doing many expensive join operations to get to your custom fields, this solution may not scale well.

P.S. As noted below, the term "design pattern" usually refers to object-oriented programming. You're looking for a solution to a database design problem, which means that most advice regarding design patterns won't be applicable.

Answer 2

As far as the application code is concerned I'm unsure. I do know that custom fields benefit greatly from a EAV model in the database.

Per the comments below, the most significant mistake you can make with this model is putting foreign keys into it. Never ever put something like FriendID or TypeID into this model. Use this model in conjunction with the typical relational model and keep foreign key fields in table columns as they should.

A second significant mistake is placing data in this model that needs to be reported with every element. For example putting something like Username in this model would mean that anytime you want to access a user and need to know their username you've committed yourself to a join at best or 2n queries where n is the number of users you're looking at. When you consider that you are usually going to need the Username property for every User element it becomes obvious this too should remain in the table columns.

However, if you're just using this model with custom user fields you'll be fine. I can't imagine many situations where a user would be entering in relational data and the EAV model is not too significantly detrimental to searches.

Lastly, don't try to join data from this and get a nice pretty recordset. Grab the original record and then grab the set of records for the entity. If you find yourself tempted to join the tables you've probably made the second mistake as mentioned above.