2.1.1   Initial Schema Examples¶

Suppose that schema for some Original Format EFD table looks like:

CREATE TABLE original_EFD_Table (
    date_time DATETIME(6),
    float value1,
    float value2,
    PRIMARY KEY (date_time)
);

1. If data in this table is fast-changing and each record in the table can only be related to a single exposure or no exposure then association to exposures can be implemented for this table as a foreign key column (assuming Exposure table exists with primary key column named exposureId):

   CREATE TABLE transformed_EFD_Table (
       date_time DATETIME(6),
       exposureId BIGINT NULL DEFAULT NULL,
       float value1,
       float value2,
       PRIMARY KEY (date_time),
       FOREIGN KEY (exposureId) REFERENCES Exposure (exposureId)
   );
   

   In case association to more than one type of data is needed this will result in multiple columns and foreign keys added to transformed table schema. Note that MySQL automatically indexes foreign key only in InnoDB tables, if MyISAM is used then index on exposureId will have to be added.

2. If record in original table can be associated with more than one exposure then one option is to duplicate records and assign different records to different exposures. This will also require change in primary key as individual date_time become non-unique. Additional complication in this case is that foreign key becomes a part of primary key and cannot be null (meaning that each record must be associated to some exposure):

   CREATE TABLE transformed_EFD_Table (
       date_time DATETIME(6),
       exposureId BIGINT NOT NULL,
       float value1,
       float value2,
       PRIMARY KEY (date_time, exposureId),
       FOREIGN KEY (exposureId) REFERENCES Exposure (exposureId)
   );
   

3. Alternative to that which still keeps foreign key nullable is to use synthetic primary key:

   CREATE TABLE transformed_EFD_Table (
       date_time DATETIME(6),
       serial BIGINT NOT NULL AUTO_INCREMENT,
       exposureId BIGINT NULL DEFAULT = NULL,
       float value1,
       float value2,
       PRIMARY KEY (date_time, serial),
       FOREIGN KEY (exposureId) REFERENCES Exposure (exposureId)
   );
   

4. And most generic implementation allowing many-to-many association between exposure and EFD tables using separate table:

   CREATE TABLE transformed_EFD_Table_Exposure_Match (
       exposureId BIGINT NOT NULL,
       efd_date_time DATETIME(6) NOT NULL,
       PRIMARY KEY (exposureId, efd_date_time),
       INDEX (efd_date_time),
       FOREIGN KEY (exposureId) REFERENCES Exposure (exposureId),
       FOREIGN KEY (efd_date_time) REFERENCES transformed_EFD_Table (date_time)
   );
   

   One attractive feature of this schema is that it does not require any changes to the original EFD schema for transformed tables (foreign key above can actually point to an original_EFD_Table), this can be a benefit for schema management operations such as schema migration.

5. It is also possible to completely avoid adding additional schema elements by simply matching exposure time (or any other time) with the set of records from original EFD tables. If original tables are indexed on timestamp this matching can be done efficiently by using two classes of queries:

   1. Finding a single telemetry record that matches exactly or precedes given timestamp (e.g. exposureBegin). This query should return a single record, or no records if there was no telemetry recorded before given time:

      SELECT *
          FROM original_EFD_Table
          WHERE date_time <= exposureBegin
          ORDER BY date_time DESC
          LIMIT 1;
      

   2. Finding a range of records contained withing exposure window (this intentionally excludes exposureBegin as one should use previous query for finding that):

      SELECT *
          FROM original_EFD_Table
          WHERE date_time > exposureBegin AND date_time < exposureEnd;
      

2.2.1   Future Schema Examples¶

One possible option to support schema changes in original EFD without significant changes in summary tables is to make summary table schemas flexible enough to support addition or removal of columns at arbitrary time. This implies some departure from regular relational model which also means that NoSQL options for summary data storage may be a reasonable approach.

In a context of relational database such flexibility can be implemented via storing all related attributes of the same type in a single table. One of the columns in this table would be an original column name or, for efficiency, corresponding integer index. Summary values will likely be associated with a particular type of the time window such as exposure or visit so it is also natural to have a foreign key of the corresponding item in the table schema too. In the minimal approach schema for table which stores summary data for the above original_EFD_Table may look like:

-- Table which maps column names to unique indices
CREATE TABLE summary_column_names (
    columnId INT NOT NULL,
    columnName CHAR(64) NOT NULL,
    PRIMARY KEY (columnId),
    INDEX (columnName)
);

-- Per-exposure summary table for floating point columns in original_EFD_Table
CREATE TABLE summary_EFD_Table_per_exposure (
    exposureId BIGINT NOT NULL,
    columnId BIGINT NOT NULL,
    double summaryValue,
    PRIMARY KEY (exposureId, columnId),
    FOREIGN KEY (exposureId) REFERENCES Exposure (exposureId),
    FOREIGN KEY (columnId) REFERENCES summary_column_names (columnId)
 );

Each individual data type of the resulting summary table will likely need a separate summary table, unless they can be stored without loss of precision as a double precision type. If summary data for some column is represented as a set of values (set of integer status values) then above schema needs to be extended for those columns to support multiple values.

Supporting different versions of summary algorithms can mean different things:

• Having only one active algorithm at a time but recording algorithm being in use at the moment. That could be achieved via some sort of provenance mechanism by storing algorithm specification in a separate provenance table and adding provenance identifier column to the summary table.
• Having multiple active algorithms for the same time window (either during the same processing or re-processing at a later time). To store multiple summary values they need to be distinguished by algorithm identifier.

In latter case schema may look like:

-- Table which maps algorithm names to unique indices
CREATE TABLE summary_algo_names (
    algoId INT NOT NULL,
    algoName CHAR(64) NOT NULL,
    PRIMARY KEY (algoId),
    UNIQUE INDEX (algoName)
);

-- Per-exposure summary table for floating point columns in original_EFD_Table
CREATE TABLE summary_EFD_Table_per_exposure (
    exposureId BIGINT NOT NULL,
    columnId BIGINT NOT NULL,
    algoId BIGINT NOT NULL,
    double summaryValue,
    provenanceId BIGINT NOT NULL,
    PRIMARY KEY (exposureId, columnId, algoId),
    -- additional indices may be needed to support efficient search
    FOREIGN KEY (exposureId) REFERENCES Exposure (exposureId),
    FOREIGN KEY (columnId) REFERENCES summary_column_names (columnId),
    FOREIGN KEY (algoId) REFERENCES summary_algo_names (algoId),
    FOREIGN KEY (provenanceId) REFERENCES summary_provenance (provenanceId)
 );

This is of course only one of many possibles ways to implement limited versioning support and it certainly does not cover all possible schema evolution options.