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.
2
-
6You might be interested in reading some of the responses to this question about designing a database for user-defined fields– RachelMay 14, 2012 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.– Steven EversMay 14, 2012 at 15:23
Add a comment
|
4 Answers
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:
answered May 14, 2012 at 15:37
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.
-
I've also heard of people using document-oriented databases as an alternative to EAV, but have no personal experiences with such an approach. May 14, 2012 at 14:59
-
@FrustratedWithFormsDesigner - That's what I was hinting at, but as yourself, no personal experience.– OdedMay 14, 2012 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.
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.
