Dynamic Custom Fields for Data Model

Question

I am in the process of creating a dynamic database where user will be able to create resource type where he/she can add custom fields (multiple texts, strings, and files)

Each resource type will have the ability to display, import, export its data;

I've been thinking about it and here are my approaches. I would love to hear what do you guys think.

Ideas:

1. just hashing all the custom data in a data field (pro: writing is easier, con: reading back out may be harder);

2. children fields (the model will have multiple fields of strings, fields of text, and fields for file path);

3. fixed number of custom fields in the same table with a key mapping data hash stored in the same row;

4. Non-SQL approach, but then the problem would be generating/changing models on the fly to work with different custom fields;

Answer 1

Firstly you can create few models:
- StringData
- BooleanData
- TextData
- FileData
etc (all data and fields formats you need)

Each model will refferenced to some project, wich will contain information about fields

IE:

class Project < ActiveRecord::Base
  has_many :project_fields
  has_many :string_datas :through => project_fields
  has_many :file_datas :through => project_fields
  has_many :boolean_datas :through => project_fields
  etc ...
end

class ProjectField < ActiveRecord::Base
  # title:string field_type:string project_id:integer name:string
  belongs_to :project
  has_many :string_datas
  has_many :file_datas
  has_many :boolean_datas
  etc ...
end

class StringData < ActiveRecord::Base
  # data:string project_field_id:integer
  belongs_to :project_field, :conditions => { :field_type => 'String' }
end

class FileData < ActiveRecord::Base
  # data:file project_field_id:integer
  belongs_to :project_field, :conditions => { :field_type => 'File' }
end

project = Project.new
project.project_fields.new(:title => "Product title", :field_type => "String", :name => 'product_title')
project.project_fields.new(:title => "Product photo", :field_type => "File", :name => 'product_photo')
project.save

<% form_for project do |f| -%>
  <% project.project_fields.each do |field| -%>
    <%= field_setter field %>
    #=> field_setter is a helper method wich creates form element (text_field, text_area, file_field etc) for each type of prject_field
    #=> ie: if field.field_type == 'String' it will return
    #=> text_field_tag field.name => <input name='product_name' />
  <% end -%>
<% end -%>

And create (update) method

def create
  project = Project.new(params[:project])
  project.project_fields.each do |field|
    filed.set_field params[field.name]
    # where set_field is model method for setting value depending on field type
  end
  project.save
end

It is not tested and optimized but it just showing the way you can implement it.

UPDATE: I've updated code but It's only model, you have to think yourself a little :) and you can try to find out another implementation