title: dimensional and unit analysis in SQL database
body: <p>Problem:</p>
  
  <p>A relational database (Postgres) storing timeseries data of various measurement values. Each measurement value can have a specific "measurement type" (e.g. temperature, dissolved oxygen, etc) and can have specific "measurement units" (e.g. Fahrenheit/Celsius/Kelvin, percent/milligrams per liter, etc).</p>
  
  <p>Question:</p>
  
  <p><strong><em>Has anyone built a similar database such that dimensional integrity is conserved? Have any suggestions?</em></strong></p>
  
  <p>I'm considering building a measurement_type and a measurement_unit table, both of these would have text two columns, ID and text. Then I would create foreign keys to these tables in the measured_value table. Text worries me somewhat because there's the possibility for non-unique duplicates (e.g. 'ug/l' vs 'µg/l' for micrograms per liter).</p>
  
  <p>The purpose of this would be so that I can both convert and verify units on queries, or via programming externally. Ideally, I would have the ability later to include strict dimensional analysis (e.g. linking µg/l to the value 'M/V' (mass divided by volume)).</p>
  
  <p><em>Is there a more elegant way to accomplish this?</em></p>