Allowing users to add their own custom fields in a Spring MVC Hibernate application - What's an ideal approach?

Question

We all may have seen applications like JIRA, or many CRM or other applications that allow its users to define their own custom fields to an entity, and do a variety of stuff with it, like making them mandatory, validate their values and so on.

I want to do just that in the Product we are creating.

Let's assume our product allows a user to create his/her own Project. A project has pre-defined attributes such as

• Name (String)
• Description (CLOB)
• Type (String)
• Owner (String)
• Status (String)

Now, as a user, I would like to add the following custom field to my project

• Due Date (Date)

Ideally he should be able to create a custom field in my product which would capture the following details:

• Name of the field
• Type of the field
• Default Value
• List of values (if the field is to be a drop down list)
• Mandatory or not

Similarly, I would like to allow this feature of adding custom attributes not only to a project, but to a few other entities as well.

This is the technology stack we're using and so far we're pretty ok with it.

• Spring MVC, JSP and jQuery as the Web Framework and for the Views
• JPA with Hibernate for persistence
• Oracle, MS SQL, MySQL - Currently our product works on these databases.

How do I approach this requirement? I would like to be educated on the following:

• How to I decide the best data model for this? Do I add a separate table for custom field definitions, and another one for their values, and associate them to my entity by means of a foreign key?
• What should I do in my JSP/JS Layer to dynamically paint a screen with whatever custom fields that are defined?
• How do I let Spring MVC and Hibernate handle all this data model and the views?

I'm extremely sorry if my question is not framed or worded properly. I'm relatively new to these technologies, and would like to learn with each challenge.

Thanks, Sriram

Answer 1

From the backend side, if you can give up some of the requirements there is a simple solution using JSON column in your table to store dynamic fields. In this case, you would have a map in your Project entity, and using Hibernate Types you can map them to JSON data automatically.

This solution is enough to store, retrieve and filter by dynamic fields and most of the DBMS engines now support JSON/document columns.

However, if the other requirements are mandatory you need to implement your own solution.

You can extend the JSON column approach by adding two entities: Field and Template. The Field represents fields names, types, and constraints whereas the Template represents customer project types. The relation between Project and Template is many-to-one since each template can have many project records. The template should be used from the application side to validate user inputs based on the template. After that, the data will be stored as JSON data in the same Project Table.

If the above solution is not enough you can depend more on the Database and let each template data be stored on a new table that has the template name. However, this solution is DMBS specific since (to my knowledge) Hibernate doesn't support creating tables at runtime.

Answer 2

I am not familiar with hibernate or spring but I am giving a general idea.

Create a table to store your new field. It must have a key to filter for different purposes. And a template field which will be a JSON string with all the values you specified( Name, type etc). Make it a CLOB column.

EntityRelation  | FieldInJson(CLOB)
--------------------------------------------------------
New Entity    | { json string}

Next add a field in your model which will be a reference to the json array of fields.

Model
    |
    --Entity1
    |
    --........
    |
    --JSONArray()

Then you can pass the json to frontend to create fields out of the json. But the main point is, you have to save your validations as well to your json and handle it accordingly.

You can use XML instead of JSON if you want.

This way, the custom fields will be stored as a json or an xml and everytime these will be created dynamically.

P.s - There may be performance or maintenance problems with this solution.

Answer 3

You may find that external factors limit your technical choices. I've never worked in an organisation that allowed an application to create tables on the fly in a production database. The connection privileges always prohibited DDL statements.

Your experience may differ, but do you really want to give a a rogue user the capability of creating hundreds of new tables?

As others have noted, Hibernate does not support table creation at runtime. My advice is to use a name/value pair table or possibly a JSON field.