title: Database design: split column with multiple data types
body: <p>I need to seggregate an old database field called <code>AMOUNT</code>, which allows users to store multiple types of numeric data (eg. decimals, integers, percentages) into the same column. The values can range from 100,000,000.00 to 0.00.</p>
  
  <p>The values stored in <code>AMOUNT</code> can represent four different data formats:</p>
  
  <ul>
  <li>Amounts expressed in a local currency (e.g. values between 100,000,000.00 to 00.00 in euros, dollars, yen...)</li>
  <li>Integer amounts (e.g. integers between 0 and 12)</li>
  <li>Percentage amounts (e.g. values between 1 and 0.01)</li>
  </ul>
  
  <p>Example:</p>
  
  <pre><code>CURRENCY_ID |   AMOUNT  |   TYPE      |
       1      |  1000000  |  Currency   |
       2      |  43234.33 |  Currency   | 
     NULL     |    12     |  Integer    |
     NULL     |   0.67    |  Percentage |
     NULL     |   0.58    |  Percentage |
     NULL     |    10     |  Integer    |
  </code></pre>
  
  <p>I have come up with the following design suggestions:</p>
  
  <ol>
  <li><p>Create a new column called <code>TYPE_ID</code>, which indicates the data type ID (i.e. 1 = Amount expressed in local currency, 2 = integer amount, 3 = percentage amount) and link it to a separate table that containing all possible type IDs.</p>
  
  <pre><code>CURRENCY_ID |  TYPE_ID |  AMOUNT
       1      |     1    |  1000000 
       2      |     1    |  43234.33
     NULL     |     2    |    12
     NULL     |     3    |    0.67
     NULL     |     3    |    0.58
     NULL     |     2    |    10
  </code></pre></li>
  </ol>
  
  <p>The problem I see with this is, however, that the amounts in the column <code>AMOUNT</code> still represent different formats (large integers and small decimals) and thus are not uniform.</p>
  
  <ol start="2">
  <li><p>Create a new column for each data type (i.e. <code>AMOUNT_LOCAL_CURRENCY</code>, <code>AMOUNT_PERCENTAGE</code>, <code>AMOUNT_INTEGER</code>) and store values of <code>AMOUNT</code> into corresponding columns.</p>
  
  <pre><code>CURRENCY_ID |  AMOUNT_LOCAL_CURRENCY | AMOUNT_INTEGER | AMOUNT_PERCENTAGE
       1      |       1000000          |     NULL       |      NULL
       2      |       43234.33         |     NULL       |      NULL
     NULL     |         NULL           |      12        |      NULL
     NULL     |         NULL           |     NULL       |      0.67
     NULL     |         NULL           |     NULL       |      0.58
     NULL     |         NULL           |      10        |      NULL
  </code></pre></li>
  </ol>
  
  <p>The problem with this is that the number of data formats can increase, which would mean adding a new column each time a new format is created.</p>
  
  <p>Local currencies have an own identifier table already in place. </p>
  
  <p>What would be a suitable and scalable design to distinguish the various types of data in the AMOUNT column? </p>
  
  <p>Thank you in advance!</p>
  
  <p>Markus  </p>