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:
- 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.
- 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.
- 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.
- 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.
- 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.