General Oracle Data Collection Storage

Question

I am designing a new laboratory database. I want to store the raw results for all tests together.

In some cases, the result is a single value. However, the result is sometimes a raw waveform or signal.

Which case below is ideal (and why)? ... or provide your own ideal option.

Option 1: Store each individual data point as a separate result row (with an offset from t=0).

OR

Option 2: Create a signal_header table (start time, sample rate, units, etc) which links to a raw_signal_value table (signal, offset index, value).

OR

Option 3: Store the raw data in an external file to retrieve when necessary.

Also, in a related post General Oracle Results Table, I asked about whether or not to fork a general results table into child tables based on precision and scale (data type).

Answer 1

I would avoid option 3 - if you're going to use a database to store raw results, you might as well use it to store all the results.

Option 1 sounds like you'll end up duplicating a lot of data for each result row, and have only two values (time offset and value) change.

Of the three options you suggest, I would go with Option 2. You'll be able to store a single result row for each result, and have the details of the result available in the DB as well, without cluttering up the result table itself.

Depending on how you expect to use the data, and how many data points you have per result waveform, I might even be tempted to store the waveform/signal as a single string (e.g. comma-separated values).