Have Geek Brain

Dynamic Search Fields, Best Practice?

I have a Lexicon model and I want the user to be able to create a dynamic function for each lexicon.

And I have a sophisticated search interface that allows you to search for a user by every single function (including dynamic ones) that belongs to the Lexicon model.

I could use a serialized textbox to store all dynamic information if they are not searchable.

In case I want to allow user searches in all fields, I created a DynamicField Model to hold all dynamically created functions.

But imagine that I have 1,000,000,000 lexicons, and if you create a dynamic function for each lexicon, it will create 1,000,000,000 rows in the DynamicField model.

So the sql lookup function will become rather ineffective when a lot of dynamic functions are created.

Is there a better solution for this situation?

In what form can I take?

  • finding the best db design for dynamic fields

  • try to setup mysql (add cache fields, add index ...) with the current db design

+2


a source to share


2 answers


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



+2


a source


I think the best way to do this is to use a name / value pair 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 will create a 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 executing my code into an html file to get the idea.)

+1


a source






More articles:


All Articles