Databases
StackOverflow2013
Postsdbo
POHow can different groups of measurements be stored in a database table?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can different groups of measurements be stored in a database table?
    primarykey
    data
    text
    <p>This is a puzzle about storing a big mess of data in a database.</p> <p>Suppose I have a facility full of machines, each measuring time series of multiple quantities. I'll try to explain with a toy example.</p> <p><code>MachineA</code> measures four temperatures and a humidity. <code>MachineB</code> measures one temperature and five voltages. <code>MachineC</code> measures wind speed and direction. The measurements are happening all the time, automatically.</p> <p>Each machine makes all of its own measurements at the same time, but the time interval and offset varies among machines. That means a row can only contain measurements from one machine taken at one timestamp.</p> <p>In reality there are two thousand machines. That means using a separate table for every machine or type of machine is impractical.</p> <p>Different machines are being added all the time. A set of columns that spans all possible measurements is not practical because if I add a <code>MachineD</code> with a saltiness measurement, I will not have thought about saltiness ahead of time and I'd have to add a new column to a really big table just for that one machine to use.</p> <p>Typical queries:</p> <ul> <li>What was <code>MachineA</code>'s maximum temperature in the last 90 days?</li> <li>What were all of <code>MachineB</code>'s measurements for the last 7 days?</li> <li>Among all machines having temperature measurements, what was the minimum temperature measured yesterday?</li> <li>For each machine having a temperature measurement, what was the minimum temperature measured yesterday?</li> <li>Which machines having saltiness detectors also have measurements within the last year?</li> <li>What is the wind chill based on <code>MachineC</code>'s wind speed and <code>MachineB</code>'s temperature?</li> </ul> <p>How can this be organized within these constraints?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USArgentoSapiens
    1. USArgentoSapiens
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COIf everything is being measured as a numeric value, just store that value and then store which *unit of measurement* in a separate column. That column can be an ENUM or a string, with a foreign constraint on a *valid measurements* table.
      singulars
      1. POHow can different groups of measurements be stored in a database table?
      1. USMike Christensen
    2. CO@MikeChristensen, it's not enough to just store the unit; the name of the quantity must also be stored ("widget 4 temp"). Your suggestion can accommodate this, but when I get a million rows from this database, most of the transfer will be spent on labels and not measurements, right? Is there a way around this?
      singulars
      1. POHow can different groups of measurements be stored in a database table?
      1. USArgentoSapiens
    3. COYou probably need to think about what types of queries you need to support. If it's just a giant log of data, you can probably just serialize everything as strings or reconsider using a relational DB in the first place. If you need to query for certain types of measurements or certain machines or certain widgets, you'll need to normalize your data in a way that supports that.
      singulars
      1. POHow can different groups of measurements be stored in a database table?
      1. USMike Christensen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload