Simple DB design question -- sets of measurements

Question

Hi all,

I have multiple sets of measurements. Each set has multiple values in it. The data is currently in a spreadsheet, and I'd like to move it to a real database.

In the spreadsheet, each set of measurements is in its own column, i.e.,

 1  |  2  |  3  | ...
0.5 | 0.7 | 0.2 | ...
0.3 | 0.6 | 0.4 | ...

and so on. If I have, say, 10 data sets and each has 8 measurements, I wind up with an 8x10 table with 80 values.

To put the database in normal form, I know that it should be designed to add rows, not columns, for each new set. I presume that means the tables should be arranged something like (MySQL syntax):

create table Measurement(
    int id not null auto_increment,
    primary key(id)
);

create table Measurement_Data(
    int id not null auto_increment,
    double value not null,
    int measurement,
    primary key(id),
    constraint fk_data foreign key(measurement) references Measurement(id)
}

Is that correct? The result is a table with only one autogenerated column (though of course I could add more to it later) and another table that has each data set stored "lengthwise", meaning that table will have 800 rows given the values above. Is that the right way to handle this problem? It also means that I need to add data, I need to insert a row into the Measurement table, get its assigned pk, and then add all the new rows to the Measurement_Data table, right?

I appreciate any advice,

Ken

Answer 1

What about a simple one-table solution for this?

CREATE TABLE measurements
(
    id INT AUTO_INCREMENT,
    dataSetId INT,
    measurementNumber INT,
    measurementValue DOUBLE,
    CONSTRAINT UNIQUE (dataSetId, measurementNumber)
);

Unless your problem is more complicated than you describe, this should be adequate.

Answer 2

First I would change 'measurement' column name to "measurement-id", becase this name is not intuitive. You could also add ordinal number to Measurement-Data table to know order in which measurements were taken and add unique on measurement-id and ordinal-numer. By adding ordinal-numer You can also limit number of measurements in every test with check constraint.

Answer 3

In general, your plan sounds good as far as it goes. I'd tweak it a bit differently from the other posts:

create table Measurement(
    int  MeasurementId  not null  auto_increment,
    primary key(id)
);

create table MeasurementData(
    int  MeasurementId  not null,
    int  MeasurementDataEntry  not null,
    double  value  not null,
    primary key(MeasurementId, MeasurementDataEntry),
    constraint fk_data foreign key(MeasurementId) references Measurement(MeasurementId)
)

As per LukLed, MeasrementDataEnry would be an ordinal value, starting at 1 and incrementing by one for each "data entry" for that MeasurementId. The compound primary key should be sufficient, as with the probelm stated I see no real need for an extra surrogate key on MeasurementData.

Be sure that your solution is sufficient for your problem set. Are other columns required in either table (type of measurement, when it occured, who dunnit)? Are there always the same number of data readings for each Measurement, or can it vary? If so, do you need the number of data measurements stored in the parent table (for quicker retrieval, ad the cost of redundancy?) Will you ever need to update data entries, and will that mess up order? It just seems a bit sparse, and there might be other business rules you'll need to factor in down the road.