title: Good DB schema for measurement units with php + zend?
body: <p>I am working on a web application that store a lot of measurement data.</p>
  
  <p>These measures have different types like temperature, flow, weight, concentration...</p>
  
  <p>I am currently trying to implement a <strong>measurement units manager</strong> to let the users of the application choose which unit they want for each measure.</p>
  
  <p>The data is in multiple tables :</p>
  
  <blockquote>
    <p>Table1 : weightMeasure1 lengthMeasure1 TemperatureMeasure1</p>
    
    <p>Table2 : weightMeasure2 lengthMeasure2 FlowMeasure2</p>
    
    <p>Table3 : flowMeasure3</p>
  </blockquote>
  
  <p>The users will be able to choose the unit that they want for each measure independently. So it will not be a <strong>metric/imperial</strong> choice.</p>
  
  <p>All the data will be saved in there <strong>SI or SI derived</strong> unit in the Database. Because I am using <strong>PHP</strong> for my application I decided to use <strong><a href="http://framework.zend.com/manual/en/zend.measure.edit.html" rel="nofollow noreferrer">Zend_Measure</a></strong> to handle the conversion (after asking this <a href="https://stackoverflow.com/questions/3753897/how-to-manage-measurement-units-in-a-php-web-application">question</a>).</p>
  
  <hr>
  
  <h2>How to define the unit of each measures in my database?</h2>
  
  <h2>How to manage the user unit preferences?</h2>
  
  <hr>
  
  <p><em>This is the best solution that I have so far :</em></p>
  
  <p>I will create a <strong>property</strong> table :</p>
  
  <blockquote>
    <p>property : property_id name column unit</p>
  </blockquote>
  
  <ul>
  <li><strong>property_id</strong> is a unique incremented number (PK)</li>
  <li><strong>name</strong> is a human readable name (optional)</li>
  <li><strong>column</strong> is a concatenation of the table name and the column name (Unique constraint). For example : <code>table2_lengthMeasure2</code></li>
  <li><strong>unit</strong> is the Zend constant string defining the unit of the property in the database. For example : <code>SQUARE_METER</code>, <code>KILOGRAM_PER_HOUR</code>, ... more details available in the <a href="http://framework.zend.com/apidoc/1.11/" rel="nofollow noreferrer">Zend Framework API</a>.</li>
  </ul>
  
  <p>I will create a <strong>user_property_unit</strong> table to manage the user preferences :</p>
  
  <blockquote>
    <p>user_property_unit : user_id property_id unit</p>
  </blockquote>
  
  <ul>
  <li><strong>unit</strong> is the Zend constant string defining the unit of the property when it will be display in the UI for the user. For example it can be <code>SQUARE_METER</code> in the unit column of the <code>property</code> table and <code>SQUARE_FOOT</code> in the unit column of the <code>user_property_unit</code></li>
  </ul>
  
  <p>The problems that I see with this solution are:</p>
  
  <ul>
  <li>Using the concat of <code>table name + column name</code> seems not right to me...</li>
  <li>If I change the name of one of my columns it will not be reflected in the <code>property</code> table.</li>
  </ul>
  
  <p>Thanks for your Help!!</p>
  
  <hr>
  
  <p>UPDATE
   Is there any one why can help me even with some tips?</p>