Adding custom fields in my application

Question

I have a SAAS product, which is build by Spring MVC and Hibernate. Generally SAAS products allow user's to customize the product like adding extra fields to the table.
So i want to give the flexibility to users, to create custom fields in the tables for themselves.
Please provide all the viable solutions to achieve it.
Thank you so much for your help.

Answer 1

I'm guessing your trying to back this to a Relational database. The primary problem is that relational databases store things in tables, and tables don't really handle free form data well.

So one solution is to use a document structure that is flexible, like XML (and perhaps ditch the database) but databases have features which are nice, so let's also consider the database-using approaches.

You could create a "custom field" table which would have columns (composite primary key) for

ExtendedTable ColumnName

but you'd also have to store the data somewhere

(ExtendedKey) DataItem

And now we get into the really nasty bits. How would you apply constraints to this data? I mean, what would the type be of a DataItem? A general solution would be quite complex (being a type of free form database). Hopefully you could limit the solution to solve only the problems you require solved.

Another approach is to use a single "extra" column that contains an XML record which embeds it's own "column and value" extensions, but if you wanted to display a table of the efficiently, you'd have to parse out every XML document in every field, which is not ideal.

Neither one of these approaches will work well with the existing SQL query language, so you'll then start building your own query language.

I suggest you go back and look at real data requirements, instead of sweeping them under the table with a "and anything else one might want" set of columns on your table.

Answer 2

Your requirement is best suited use case for NoSQL databases (like MongoDB).

Dynamically creating relational database tables & columns (modifying schemas) upon user requests in an application is not a best practice as these involve DDL operations, which are very powerful and in case if you don't handle them carefully, the whole application's database goes to the inconsistent state.