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.

rewritten · Accepted Answer · 2012-11-06 09:28:35Z

2

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.

answered Nov 6, 2012 at 9:28

rewritten

16.5k2 gold badges49 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sameera207 Over a year ago

agreed with @rewritten, and if you want more examples have a look at "locomotive CMS" (locomotivecms.com), they do almost the same thing that you are trying to do here :)

rewritten Over a year ago

@sameera207 thanks for the example link (that's using Mongo as database). If you want to see an interesting EAV implementation, check SpreeCommerce: github.com/spree/spree

taro · Accepted Answer · 2013-08-07 15:18:37Z

1

NoSQL database(Document Database Mongodb,CouchDB) can be best fit for this or use redis. As per my thoughts you can use Vertical Table concept Try to run Rails 2.x Demo of application for MySQL. You can try with Mongodb, check if this is needed.

edited Aug 7, 2013 at 15:18

taro

5,8502 gold badges33 silver badges34 bronze badges

answered Nov 6, 2012 at 9:07

Amar

6,9524 gold badges27 silver badges23 bronze badges

1 Comment

xaxa Over a year ago

don't know why they gave you -1 :) thnx for an answer

Collectives™ on Stack Overflow

rails create table in db dynamically

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related