CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
bassandguitar
bassandguitar

Reputation: 199

Best Method for Storing Many Measurements in Database

So I have many data measurement values for different kinds of measurements.

For simplicity sake let's say they're height and weight values for two people.

Which do you think (and why) is the best method for storing the data? In actuality we're talking about 1000s of patients and a lot of data.

Method 1

TableNums
name id
height 1
weight 2

height
id value
1 140
2 130

weight
id value
1 70
2 60

In this method I have a separate table for each measurement type. This one seems good for readability and adding new measurements in the future. But it would also make for a lot of tables.

or

Method 2

TableNums
name id
height 1
weight 2

attributes
id type_id value unique_id
1 1 140 1
2 1 130 2
1 2 70 3
2 2 60 4

This method seems less readable but would require only one table for the measurements.

Which do you guys think is better practice?

Thanks, Ben

Upvotes: 0

Views: 849

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20804

I recommend something like this:

 Table Meausurent:
 MeasurementId PK 
 MeasurementType varchar -- height, weight, etc
 MeasurementUnit varchar -- kg, cm, etc

Table patientMeasurement:
PatientId  -- FK to patient
MeasurementId -- FK to measurement
value float
MeasurementDateTime datetime
other fields.

The PK of patientMeasurement could be composite (patientID, MeasurementId, MeasurementDateTime) or a separate field

Upvotes: 1

Related Questions