Does Ruby On Rails support a table for every instance of a model database schema?

Question

I have a legacy database where there are objects called "notes". Each pad has an entry in the notes table with information about the note, such as the UUID, owner and date created. Each version of the note's actual content is stored in its own table in a separate database, where the table's name is the note's UUID. Each record in a particular note's table represents a new version of the content, to facilitate tracking of changes and what not.

Do you know of a way to implement this schema in Ruby on Rails while maintaining the encouraged best practices?

Answer 1

1. :pad has_one :note. :note belongs_to :pad
2. Use the vestal-versions plugin to version your notes. No need to store on a separate database.
3. Done.

Answer 2

I don't think we're in best practice territory here: the "one table per note" thing is really, really horrible. Start with the Note/Pad thing:

class Note < ActiveRecord::Base
  has_many :note_versions # don't see how this can work
end

We're already in trouble because (1) the version table isn't in the same database and (2) it's a different table for each Pad. Did I mention this is horrible?

Where can we start with note versions?

class NoteVersion
  establish_connection THE_OTHER_DB # we know how to connect at least, I  hope?
  set_table_name '... # er, tricky - it changes
end

At this point I think best practices go out the window. I'm thinking we're in find_by_sql country. This might work:

class Note < ActiveRecord::Base
  def note_versions
    NoteVersion.find_by_sql(['SELECT * FROM ?', self.uuid])
  end
end

class NoteVersion < ActiveRecord::Base
  establish_connection THE_OTHER_DB # as above
  # maybe override all the "normal" AR methods to stop it trying to work
  # with a non-existent "note_versions" table?
end

I've given up on associations (probably no big loss unless you want to update) but I think this might be a possible way forward. I'm stretching my ActiveRecord knowledge further than it's used to going here - maybe someone will have knowledge that I don't; that would be good.

Answer 3

I don't know what database you are using, but conceivably you could create a new view or table and insert into it and use a trigger to create and insert into the correct table per note.