rails create table in db dynamically

Question

Normally to create/alter a table in database I use migrations (manually run rake db:migrate) and then in my code I use ActiveRecord. This is very cool as I don't have to worry about representation of the data in db and about a specific kind of db (sqlserver, pg or other).

But now a customer wants to be able to create "things" on-fly himself like, say, he starts selling computers, so he wants to an interface where he can dynamically create an object "computer" with properties like "Name, RAM, HD, ...". It seems to be quite natural to create a separate table in db with all these fields. But how can I do that in RoR and keep all these nice things about ActiveRecord?

Please suggest.

Answer 1

The usual way is to do exactly the opposite:

• Have a table for object types
• Have a table for field names for each object type
• Have a very big table with all the custom attributes for each object of any type

This is called EAV (Entity-attribute-value model, see http://en.wikipedia.org/wiki/Entity-attribute-value_model). And it scales pretty bad.

Alternatively, you can use a store text column instead of the big EAV table (see http://api.rubyonrails.org/classes/ActiveRecord/Store.html) so you don't have to make those difficult attribute retrievals, typical of EAV. You still need to store somewhere the "object types" definitions, so the expected fields etc are available when building forms and tables.

The problem with this approach is that you are not able to query (where/join/select) on those attributes because they are not columns. There are a number of solutions to that:

• Don't do filtering on those attributes (meh...)
• Have an external search server that's able to do faceted search
• (as @Amar correctly says) Use a document database
• Use postgreSQL and use hstore instead of a simple serialized column.