5

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

1
  • I think this is a good question but I'd suggest focusing it. At present you have 2 question; database/hibernate and the view part. I would suggest asking about only one (and asking a separate question if necessary). Personally the hibernate part seems the more challenging part of this Jan 6, 2017 at 22:17

5 Answers 5

0

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.

0

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.

0

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.

0

You could have a normal entities like Car, Truck, Van that all have some of their own attributes/properties. Then a custom value entity (table) such as CustomValue.
It would have a key value pair with other tracking meta data. Then each normal entity could have a join entity/table like CarCustomValue (car_custom_value table) or TruckCustomValue or VanCustomValue which links each 'normal' table/entity to a collection of custom values.

The CustomValue entity/table can have your required columns/properties/attributes. You could set some kind of unique constraints or limits with functional constraints.

If you wanted to get fancy, you could limit which keys go for which joined types.

JSON is more free form, but this answer will perhaps get you closer to what your organization will permit.

0

I find that one detailed answer could be overwhelming so here are some hints to set a possible approach.

Follow entity-attribute-value model to design the database implementing the description from wikipedia page...

Data is recorded as three columns:

The entity: the item being described.

The attribute or parameter: typically implemented as a foreign key into a table of attribute definitions. The attribute definitions table might contain the following columns: an attribute ID, attribute name, description, data type, and columns assisting input validation, e.g., maximum string length and regular expression, set of permissible values, etc.

The value of the attribute.

...follow JPA inheritance mapping with single table strategy...

In this strategy, all the classes in a hierarchy are mapped to a single table. The table has a column that serves as a discriminator column. Each subclass that adds additional state maps to this new state only in this single table. Such columns are only used by that subclass.

... and the frontend implementation would be usual business the dynamic parts being drawn iterating over collection of entity objects.

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