title: What is the preferred way to store custom fields in a SQL database?
body: <p>My friend is building a product to be used by different independent medical units.</p>
  
  <p>The database stores a vast collection of measurements taken at different times, like the temperature, blood pressure, etc...</p>
  
  <p>Let us assume these are held in a table called <code>exams</code> with columns <code>temperature</code>, <code>pressure</code>, etc... (as well as <code>id</code>, <code>patient_id</code> and <code>timestamp</code>). Most of the measurements are stored as floats, but some are of other types (strings, integers...)</p>
  
  <p>While many of these measurements are handled by their product, it needs to allow the different medical units to record and process other custom measurements. A very nifty UI allows the administrator to edit these customs fields, specify their name, type, possible range of values, etc...</p>
  
  <p>He is unsure as to how to store these custom fields.</p>
  
  <p>He is leaning towards a separate table (say a table <code>custom_exam_data</code> with fields like <code>exam_id</code>, <code>custom_field_id</code>, <code>float_value</code>, <code>string_value</code>, ...)</p>
  
  <p>I worry that this will make searching both more difficult to achieve and less efficient.</p>
  
  <p>I am leaning towards modifying the exam table directly (while avoiding conflicts on column names with some scheme like prefixing all custom fields with an underscore or naming them custom_1, ...)</p>
  
  <p>He worries about modifying the database dynamically and having different schemas for each medical unit.</p>
  
  <p>Hopefully some people which more experience can weigh in on this issue.</p>
  
  <p><strong>Notes:</strong></p>
  
  <ul>
  <li><p>he is using Ruby on Rails but I think this question is pretty much framework agnostic, except from the fact that he is only looking for solutions in SQL databases only. </p></li>
  <li><p>I simplified the problem a bit since the custom fields need to be available for more than one table, but I believe this doesn`t really impact the direction to take.</p></li>
  <li><p>(added) A very generic reporting module will need to search, sort, generate stats, etc.. of this data, so it is required that this data be stored in the columns of the appropriate type</p></li>
  <li><p>(added) User inputs will be filtered, for the standard fields as well as for the custom fields. For example, numbers will be checked within a given range (can't have a temperature of -12 or +444), etc... Thus, conversion to the appropriate SQL type is not a problem.</p></li>
  </ul>