Search code examples
mysqldatabase-designentity-attribute-value

Database design: split column with multiple data types

I need to seggregate an old database field called AMOUNT, 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.

The values stored in AMOUNT can represent four different data formats:

  • Amounts expressed in a local currency (e.g. values between 100,000,000.00 to 00.00 in euros, dollars, yen...)
  • Integer amounts (e.g. integers between 0 and 12)
  • Percentage amounts (e.g. values between 1 and 0.01)

Example:

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    |

I have come up with the following design suggestions:

  1. Create a new column called TYPE_ID, 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.

    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

The problem I see with this is, however, that the amounts in the column AMOUNT still represent different formats (large integers and small decimals) and thus are not uniform.

  1. Create a new column for each data type (i.e. AMOUNT_LOCAL_CURRENCY, AMOUNT_PERCENTAGE, AMOUNT_INTEGER) and store values of AMOUNT into corresponding columns.

    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

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.

Local currencies have an own identifier table already in place.

What would be a suitable and scalable design to distinguish the various types of data in the AMOUNT column?

Thank you in advance!

Markus

Solution

  • The idea of a database table is to store and retrieve different (read unrelated) values in different columns (fields) so that processing them would be easier and you don't get a crapload of IF statements every time you need to manipulate it.

    It's almost always best to create a new column for each type of input. Handle display issue at display time, not in SQL.

    From experience, Option 2 is best