Storing Waveforms in Oracle

Question

I am designing a new laboratory database. I want to store the raw results for all tests together. However, results can either be a single measurement or a raw waveform. (repost see footer)

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

TEST
  test_id*
  (other TEST fields)

OPTION 1: Separate single values and waveform results

TEST (1 -- many) MEASUREMENT
     ( \-- many) RAW_HEADER 1 -- many RAW_POINT

MEASUREMENT             RAW_HEADER
  measurement_id*         raw_header_id*
  test_id (FK)            test_id (FK)
  rec_time as DATE        start_time as DATE
  measurement             sample_rate

                        RAW_POINT
                          raw_header_id* (FK)
                          point_index*
                          raw_measurement

OPTION 2: Overlap common fields from option 1

TEST (1 -- many) MEASUREMENT (1 -- many) RECORDING_POINT

Same as OPTION 1 except:
    MEASUREMENT: measurement for signals is the sample_rate
     RAW_POINT: raw_header* (FK) changed to measurement_id* (FK)
     RAW_HEADER: not needed

OPTION 3: Encode signals into BLOB TEST (1 -- many) MEASUREMENT

MEASUREMENT
  measurement_id*
  test_id (FK)
  rec_time as DATE
  measurement
  signal as BLOB

For a single value measurement is the measurement and signal is NULL
For signals measurement is the sample_rate and signal stores the encoded data points

OPTION 4: Overlap common fields, but use BLOBS

TEST (1 -- many) MEASUREMENT (1 -- many) RAW_DATA

MEASUREMENT             
  measurement_id*      
  test_id (FK)         
  rec_time as DATE     
  measurement

RAW_DATA        
  measurement_id* (FK)
  raw_data as BLOB

For a single value measurement is the measurement
For signals measurement is the sample_rate

Legend:

• __*_ Fields followed by an asterisk are the complete primary key.
• (FK) Fields followed by (FK) indicate field has foreign key constraint.
• ____ All fields are of type NUMBER unless otherwise specified.

Other Info: The raw waveform data will be used in several ways: - plotted (does the plot look right) - filtered / smoothed (for better presentation) - obtain values (time at max, min value, fall time, etc)

Also, data will be acquired simultaneously over several channels. It would be useful to know what the value of Channel 2 is when Channel 1 first reaches 500 (of some unit).

A waveform typically has about 4,000 - 25,000 data points.

Other Thoughts/Questions: Can part of a BLOB be returned? In other words, could I pull out 4 bytes starting at byte 4000?

Can the blob be of a packed Oracle type, so that the values could be read by Oracle itself (ie: to obtain max, min, etc)

NOTE: This is a repost of General Oracle Data Collection Storage with better developed options.

Answer 1

Believe it or not, I don't think there's really enough information here to say for sure. For example, how will the results typically be processed? Will they be plotted, or perhaps have some sort of numerical analysis performed on them? How big are your waveforms?

Unless your raw results are typically small, I don't like option 1 for this application. There's a lot of storage overhead to store one data point.

To a lesser extent, the same objection would apply to option 2.

Option 3 is the way I would likely go. I once developed an app for viewing waveforms from lightning strikes - millions of points per waveform. The only viable option is a BLOB for that kind of a dataset. And (again, depending on some of the unknowns) I'd seriously consider storing the data as binary floats or doubles and processing them with java or external C programs. Seriously, do you need keyed access to your individual data points? Isn't loading an array up and indexing into it far simpler, more compact and efficient?

Answer 2

How will you analyse those data later?

Assuming you are doing FFT or alike later, just go option 3 -- it would be easier and faster.