Dynamic Fields in Ruby On Rails Web App

I am developing a Ruby On Rails App for collecting data from users for products via web forms. A database table contains all the ProductTypes, another table contains ProductAttributes, and ProductTypes_ProductAttributes is a relationship table mapping the attributes that are assigned to certain product types. The data collected for each specific product is then stored in a Products table (with a column/field for each attribute - which may be empty if that attribute is not relevant to the product).

I want to add a feature to allow Administrator users to add new ProductAttributes to the system and then assign them to certain ProductTypes. No problem except when it comes to persisting the specific products collected. This will require dynamically adding a new attribute field to the Products table at runtime. This is where I'm a little stumped as the best way to do this...

1) Store the products in an XML doc... This will save having a bunch of empty fields in the DB for attributes that don't belong to the P开发者_JAVA百科roductType for that product, and means new attribute types can be saved no problems. I'm a little weary of storing data in XML files though, I've always regarded them as a means to transmit and move data, not store it.

2) Continue to use a RDBMS, but include a BLOB field to store the attributes which can be dynamic for a product in XML, while traditional fields remain for the product code, description etc etc.

3) Finally, is there a RDBMS solution for the above? I've seen some discussion about MongoDB but I'll admit I'm really not familiar with it having a background in more traditional DB's.

Was wondering if anyone has any experience and tips regarding this they would like to pass on? I'd like something that integrates with Ruby on Rails, and allows dynamic adding of persistence fields without requiring any code changes when a field is added to the DB table?

Cheers,

Steve

This type of problem is actually quite common for e-commerce application. Products belong to different product types and as such need dynamic, user-defined attributes.

First off, you can take a look at some existing, open-source Rails e-commerce platforms. It might be easier to tie into them rather than re-inventing the wheel. I think the most popular at the moment are:

• Spree
• http://ror-e.com/

Regarding your question, though, you seem to be already on the right track with creating separate tables for product_types and product_attributes and then a table that joins them.

Not sure whether you're open to changing your current database design, but if you did, you might not need to worry about dynamically adding new fields to the product table.

It seems to me that what you want to do can be accomplished with a slightly different database design. So this is what your schema could look like:

create_table "products" do |t|
  t.string "name"
  t.integer "product_type_id"
  # ... other fields
end

create_table "product_types" do |t|
  t.string "name"
  # ... other fields
end

create_table "product_attributes" do |t|
  t.string "name"
  # ... other fields
end

create_table "product_attributes_product_types" do |t|
  t.integer "product_attribute_id"
  t.integer "product_type_id"
end

create_table "product_product_attributes" do |t|
  t.integer "product_id"
  t.integer "product_attribute_id"
  t.string "value"
end

So instead of the products table holding the data for the actual products, you can store the data in a separate product_product_attributes table.

When an admin user fills out the form for each product, then he/she will first pick the right product types that the product belongs to. Once the product type is set, only the attributes that are associated with that product type will be displayed in the admin form. Assigning product attributes to the different product types is a separate admin task.

Hope this helps.

~ Andrea