Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Are there any common strategies or design patterns for designing applications that have either the ability to add custom fields to data objects, or for creating your own custom definition of objects. For example, I am thinking of products such as SalesForce, where you can have your own types of information,frameworks such as Expression Engine and the way it handles channels and channel field groups (Example), or How CMSes Like wordpress have the ability to add fields to custom post types.

share|improve this question
5  
You might be interested in reading some of the responses to this question about designing a database for user-defined fields –  Rachel May 14 '12 at 14:17
    
Of note: Oracle is going around suing the pants off of everyone that implements this in a particular (from what I understand, common) way. I'd take a peek at that as well. –  Steve Evers May 14 '12 at 15:23

4 Answers 4

The EAV model is normally used for unstructured schemas as you describe.

It suffers in performance and the ability to query such dynamic properties in an ad-hoc manner... and as such is considered by many to be an anti-pattern.

Other approaches are to use a dynamic format such as XML or Json to hold such properties, possibly with dedicated storage for each property to help with search.

share|improve this answer
    
I've also heard of people using document-oriented databases as an alternative to EAV, but have no personal experiences with such an approach. –  FrustratedWithFormsDesigner May 14 '12 at 14:59
    
@FrustratedWithFormsDesigner - That's what I was hinting at, but as yourself, no personal experience. –  Oded May 14 '12 at 15:10

In addtion to the EAV table that @Oded describes, people use nosql datbase for this type of information. Remember there is no reason why your application can't use a relational database for the parts that make sense with the relational model and a nosql database for the information that doesn't.

A third possibility is to add several columns for the customer-added fields (Customerfield1, customerfield2, etc) and then have the customer define what they mean. This only works for the number of customer comnfiguarble fields you add though, so it is fine if you only expect they will need two or three but won't work at all if you will need hundreds.

share|improve this answer

Martin Fowler gave a nice description how to model dynamic properties (which is essentially what you are asking for) in his book "Analysis patterns". Most of the content is available online for free as PDF articles, the one you are looking for is this one:

http://martinfowler.com/apsupp/properties.pdf

share|improve this answer

You wouldn't have the first application that had a table with: UDF1, UDF2, UDF3... The other suggestions (EVA or NoSQL) are much better.

Depending on the RDBMS (SQL Server offers this), you could break out of normalization and have a field that holds the data in an XML format or just plain text. You'll have to rely on code to manage this.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.