dynamic searchable fields, best practice?

Question

I have a Lexicon model, and I want user to be able to create dynamic feature to every lexicon.

And I have a complicate search interface that let user search on every single feature (including the dynamic ones) belonged to Lexicon model.

I could have used a serialized text field to save all the dynamic information if they are not for searching.

In case I want to let user search on all fields, I have created a DynamicField Model to hold all dynamically created features.

But imagine I have 1,000,000,000 lexicon, and if one create a dynamic feature for every lexicon, this will result creating 1,000,000,000 rows in DynamicField model.

So the sql search function will become quite inefficient while a lot of dynamic features created.

Is there a better solution for this situation?

Which way should I take?

1. searching for a better db design for dynamic fields

2. try to tuning mysql(add cache fields, add index ...) with current db design

Answer 1

I think the best way to do this is to use a name/value pairing instead of dynamic fields. Let me explain using the EAV design pattern

So instead of having something like this:

Table: MedicalRecords
<table>
  <tr>
    <th>Temperature in degrees Fahrenheit</th>
    <th>Presence of Cough</th>
    <th>Type of Cough</th>
    <th>Heart Rate in beats per minute</th>
    <th>Column X</th>
    <th>Column X + 1</th>
    <th>... Column N</th>
  </tr>
  <tr>
    <td>102</td>
    <td>True</td>
    <td>With phlegm, yellowish, streaks of blood</td>
    <td>98</td>
    <td>????</td>
    <td>????</td>
    <td>????</td>
  </tr>
</table>

You would design your table like this:

Table: MedicalRecords
<table>
  <tr>
    <th>Name</th>
    <th>Value</th>
  </tr>
  <tr>
    <td>Temperature in degrees Fahrenheit</td>
    <td>102</td>
  </tr>
  <tr>
    <td>Presence of Cough</td>
    <td>True</td>
  </tr>
  <tr>
    <td>Type of Cough</td>
    <td>With phlegm, yellowish, streaks of blood</td>
  </tr>
  <tr>
    <td>Heart Rate in beats per minute</td>
    <td>98</td>
  </tr>
  <tr>
    <td>Column X</td>
    <td>????</td>
  </tr>
  <tr>
    <td>Column X + 1</td>
    <td>????</td>
  </tr>
  <tr>
    <td>... Column N</td>
    <td>????</td>
  </tr>
</table>

(Tried to get the table tags to work but couldn't Try coping my code into an html file to get the idea.)

Answer 2

Another idea might be to use MongoDB and MongoMapper, Thinking Sphinx or Solr. Here is Railscast on how to use Mongo: http://railscasts.com/episodes/194-mongodb-and-mongomapper