I have to add a "custom fields" feature to an existing J2EE App.
Basic requirements are:
- to be able to add custom fields to some entities ("Customers", "Items" etc...)
- to be able to choose where those custom fields appear in each form entity.
- to be able to make SQL queries on those new fields
- to be able "easily enough" to set programmaticaly the custom values from other tools (not written in the same technology).
The modeling of themeta data is pretty straight forward and doesn't bother me too much.
My main issue is the storage of each custom field value for each instance of the customized entities.
Basically, I can see two main options:
- store all the custom field instance values in a new "blob" like column added to the each customized entity table, and use Oracle XML feature, to be able to make requests on those custom fields
-store each custom field instance value as a distinct record in a big CUSTOMFIELD_VALUE table (I have seen this pattern many times, in the "MANTIS" project for example). A way to make queries on those fields easy would be to dynamically generate views on the customized entities (hiding all the details of inner joins with CUSTOMFIELD_VALUE and presenting a traditionnal table to report developers). The could be one table per entity or one single table for all customized entities.
I know a third option but it is not feasible in my case, since it implies too much refactoring: using a dynamic persistance layer (based on Hibernate for example) and have this layer generate dynamic SQL clauses depending on the metadata.
What are the pros and cons of those solutions in terms of:
- ease of development
- ease of maintenance
- ease of administration
Thank you all in advance.