Using ActiveRecord to achieve complex relations in Rails

Question

From another question of mine:

What I need to achieve is this: Create multiple categories with their own designated information fields (ie. Cars have different fields from Pets) and once such a category is created, the commands I need will be invoked and a new table for each category will be made. I know I could store all fields as some sort of string and then process it to display it properly, but I need a advanced search function for my web app and creating separate tables for each category seems the best way to achieve it. I would really like to hear alternatives for this

So I have this situation where I need categories to hold all input fields needed for that certain category. So in administration I'd have this form where I'd be able to add the category name, some other relevant information to the category itself, and then these fields that would collect information on what HTML fields to present to the user when making an entry to this certain category.

For example this would be a Dog category:

• Category name: Dog
• Category enabled: 1
• Category parent: Pets
• Fields:
  • Title - text field (will be auto added to each category)
  • Breed - select field
  • Age - number field
  • Color - text field
  • Price - number field (will be auto added to each category)
  • Description - text area field (will be auto added to each category)

So now at this stage when the user created all these certain fields for the Dog category, Im having trouble figuring what would happen when the user hits the submit button to save this category. I thought of these two solutions:

• Create a new model/table for each new category (Read linked question above) with all the HTML fields as columns and also store a row on the categories table with some basic info about this category
• Store everything in the categories table and have a fields_json column which will store all HTML field information (I wont be actually storing HTML, but basic info what the fields are about then create the HTML form fields dynamically in a controller) as a JSON string. I would be able to present the fields nicely on create, but on update it would be a hassle to populate those fields (maybe) and a search function would not be very efficient with this alternative.

So what I'm looking for is a third alternative so I can fix my problem. What would be an efficient way to solve this problem and be able to have categories with different input fields and also be able to efficiently perform searches on these categories?

The project I'm working on is being created in Ruby on Rails 4 and the database is in MySQL.

Answer 1

For given scenario I would:

1. create table categories to store each category
2. create table category_fields to store each category field
3. create table collected_categories to store all collected data from category fields in serialized hash

Collected data can be easily (de)serialized into text column (no matter of db engine you will use).

Check those sources which utilize your problem: dynamic forms