I am designing a new laboratory database. I want all the raw results (for all tests) in one table (RESULTS). However, the numerical values vary greatly in quantity and precision. For example, some results are simply a whole number count, while other results come from highly sensitive equipment.
Which case below is ideal (and why)?
Option 1: Store each result in a raw_result column as a NUMBER with the highest precision allowed.
Option 2: Create the RESULTS table and several child tables each representing results of a similar data type and a raw_result column as a NUMBER with a specific precision for that type. (ie: integer from 1 to 1000, decimal number to 6 decimal places, etc)