Question: 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.

OR

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)

Also, in a related post [General Oracle Data Collection Storage](https://stackoverflow.com/questions/1040977/general-oracle-data-collection-storage), I asked about the ideal way to store raw signals in this database.

Answer: 
Use option 1. A NUMBER is a NUMBER internally to Oracle, regardless of precision and scale.

Option 2 appears to be a horrible maintenance nightmare from the get-go, and what do you do if you decide to change the precision of one of your values?

EDIT: to illustrate, consider:

```
    SQL> CREATE TABLE testing (n1 NUMBER(38,12), n2 NUMBER(5));

    Table created
    SQL> INSERT INTO testing VALUES (1, 1);

    1 row inserted
    SQL> INSERT INTO testing VALUES (990, 9900);

    1 row inserted
    SQL> INSERT INTO testing VALUES (1999999.999999, 1);

    1 row inserted
    SQL> SELECT n1, DUMP(n1) n1d, n2, DUMP(n2) n2d FROM testing;

                   N1 N1D                                N2 N2D
--------------------- ------------------------------ ------ --------------------
       1.000000000000 Typ=2 Len=2: 193,2                  1 Typ=2 Len=2: 193,2
     990.000000000000 Typ=2 Len=3: 194,10,91           9900 Typ=2 Len=2: 194,100
 1999999.999999000000 Typ=2 Len=8: 196,2,100,100,100      1 Typ=2 Len=2: 193,2
                      ,100,100,100                          
    SQL> 

```

No difference in storage. The length stored is data dependent, not the precision/scale of the column definition.

[This link](http://www.ixora.com.au/notes/number_representation.htm) gives a pretty good explanation of how the NUMBER type is stored. It's not as simple as comparing the values to get an idea of how much storage it will take for a given number. For example, it takes 3 bytes to store the number 990, but only 2 bytes to store 9900.
I would say it depends how you're going to use the data later on. Option 1 will be easier to set up initially, but if you're then going to always be trying to separate the data for processing/display etc later on, then Option 2 might be easier in the long run.

DCookie has done a good job in explaining that Option 2 has no technical benefits for storage. It therefore boils down to whatever processing you're going to be doing with it.

> 
> I want all the raw results (for all tests) in one table (`RESULTS`). 
> 
> 
> 

If your feeling is that it makes most sense to put it all in one table, then do that.