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 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

I think it's not exactly what you want, but this http://github.com/LeonB/has_magic_columns_fork but apparently this does something similar and you may get some idea to get started.

Answer 2

It should be possible to generate database tables by sending DDL-statements directly to the server or by dynamical generating a migration. Then you can generate the corresponding ActiveRecord models using Class.new(ActiveRecord::Base) do ... end. In principle this should work, but it has to be done with some care. But this definitely no job for a beginner.

A second solution could be to use MongoMapper and MongoDB. My idea is to use a collection to store the rows of your table and since MongoDB is schema less you can simply add attributes.

Answer 3

Using a document store like mongodb or couchdb would be the best way forward, as they are schema-less.

Answer 4

Using EntryAttributeValue allows you to store any schema data in a set amount of tables, however the performance implications and maintenance issues this creates may very well not be worth it.

Alternately you could store your data in XML and generate an XML schema to validate against.

All "generic" solutions will have issues with foreign keys or other constraints, uless you do all of that validation in memory before storage.

Answer 5

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.