Dynamically creating new Active Record models and database tables

Question

I am not sure exactly what I should name this question. I just started server-side programming and I need some help.

All the tutorials I have read so far on RoR deal with creating a pre-defined table and with pre-defined fields (id, name, email, etc etc). They use ActiveRecord as base class and saving to db is handled automatically by superclass.

What I am trying to program is something that allows user-defined table with fields. So think of this way. The web UI will have an empty table, the user will name the table, and add columns (field), and after that, add rows, and then later save it. How would I implement this? I am not asking for details, just an overview of it. As I said, all the tutorials I have read so far deal with pre-defined tables with fields where the ActiveRecord subclass is predefined.

So in a nutshell, I am asking, how to create tables in db on runtime, and add fields to the tables.

Hope I was clear, if not, please let me know and i will try to elaborate a bit more. Thanks.

Answer 1

Unless you're building a DB administration tool (and even maybe then), allowing the user direct access to the database layer in the way you're suggesting is probably a bad idea. Apart from issues of stability and security, it'll get really slow if your users are creating lots of tables.

For instance, if you wanted to search for a certain value across 100 of your users' tables, you'd have to run 100 separate queries. The site would get exponentially slower the more user tables that were created.

A saner way to do it might be to have a Table model like this

class Table < ActiveRecord::Base
  has_many :fields
  has_many :rows
end

Every table would have fields attached to it, and rows to store the corresponding data (which would be encoded somehow).

However, as @Aditya rightly points out, this is not really beginner stuff!

Answer 2

I agree with previous answers generally speaking. It's not clear from your question why you want to create a table at runtime. It's not really obvious what the advantage of doing this would be. If you are just trying to store data that seems to fit into a table with rows and columns, why not just store it as an array in a field of your user table. If your user is allowed to create many tables, then you could have something like

class User < ActiveRecord::Base
     has_many :tables
  end

and then each table might have a field to store a serialized array. Or you could go with Alex's suggestion - the best choice really depends on what you are going to do with the data, how often it changes, whether you need to search it and so on ...