How to create a user customizable database (like Zoho creator) in Rails?

Question

I'm learning Rails, and the target of my experiments is to realize something similar to Zoho Creator, Flexlist or Mytaskhelper, i.e. an app where the user can create his own database schema and views. What's the best strategy to pursue this?

I saw something about the Entity-Attribute-Value (EAV) but I'm not sure whether it's the best strategy or if there is some support in Rails for it.

If there was any tutorial in Rails about a similar project it would be great.

Probably it's not the easiest star for learning a new language and framework, but it would be something I really plan to do since a long time.

Answer 1

Your best bet will be MongoDB. It is easy to learn (because the query language is JavaScript) and it provides a schema-less data store. I would create a document for each form that defines the structure of the form. Then, whenever a user submits the data, you can put the data into a generic structure and store it in a collection based on the name of the form. In MongoDB collections are like tables, but you can create them on the fly. You can also create indexes on the fly to speed searches.

The problem you are trying to solve is one of the primary use cases for document oriented databases which MongoDB is. There are several other document oriented databases out there, but in my opinion MongoDB has the best API at the moment.

Give the MongoDB Ruby tutorial a read and I am sure you will want to give it a try.

Do NOT use a relational database to do this. Creating tables on the fly will be miserable and is a security hazard, not just for your system, but for the data of your users as well. You can avoid creating tables on the fly by creating a complex schema that tracks the form structures and each field type would require its own table. Rails makes this less painful with polymorphic associations, but it definitely is not pretty.