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

by

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.

More Related Contents:

  1. How to design a product table for many kinds of product where each product has many parameters
  2. Recommended SQL database design for tags or tagging [closed]
  3. Database design – articles, blog posts, photos, stories
  4. How to version control a record in a database [closed]
  5. Sql – Indirect Foreign Key
  6. Defining multiple foreign keys in one table to many tables
  7. What mysql database tables and relationships would support a Q&A survey with conditional questions? [closed]
  8. single fixed table with multiple columns vs flexible abstract tables
  9. What are the design criteria for primary keys?
  10. What are the lengths of Location Coordinates, latitude and longitude? [closed]
  11. Are nulls in a relational database okay? [closed]
  12. DB design to use sub-type or not?
  13. What is the most efficient way to store tags in a database?
  14. Database schema which can support specialized properties
  15. Relational database design question – Surrogate-key or Natural-key?
  16. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  17. How do you deal with polymorphism in a database?
  18. Facebook like notifications tracking (DB Design)
  19. How do I design a database to store properties, selecting attributes by synonyms
  20. Composite primary key or not?
  21. Hash Collision – what are the chances? [closed]
  22. What are the principles behind, and benefits of, the “party model”?
  23. Best way to model Customer Address
  24. Should I have a dedicated primary key field?
  25. What is the best database schema to support values that are only appropriate to specific rows?
  26. Database design for audit logging [closed]
  27. Should I use a single or multiple database setup for a multi-client application? [closed]
  28. Database Design Best Practices [closed]
  29. Bill of materials database model
  30. mongodb schema design for blogs

Leave a Comment