Should MySQL database tables only have a single unit of information per row?

Question

I run an automated data collection service at work, which records measurements (temperatures, voltages, etc) from about a dozen sensors at a rate of about once a second. The MySQL database table looks like this fictitious example:

TABLE: recorded_measurements
**************************************
id sensor       measurement timestamp
1  thermometer  75          1478360216
2  voltmeter    2           1478360216
3  ohmmeter     101         1478360216
4  thermometer  74          1478360217
5  voltmeter    2           1478360217
6  ohmmeter     103         1478360217

^{The real table is obviously more complicated, taking units and other details into account, but this MWE is enough to demonstrate my question.}

Users query mostly by sensor and time window, e.g., Show me all voltages in the last 10 minutes or Get all temperatures and resistances from yesterday. Since there are hundreds of millions of rows and the table is queried frequently, we have indexed the timestamp and sensor columns.

A new group member is using my work to do something similar in another place, and has inherited most of my code. However, they have set their database table up differently:

TABLE: recorded_measurements2
****************************************
timestamp   thermometer voltage ohmmeter
1478360216  75          2       101
1478360217  74          2       103

His reasoning is this makes querying by time window easier, since you just request all data within the desired bounds and filter out the columns wanted later (he actually has about 30 sensors). I feel like this is going to make querying more difficult in the long run, as well as incredibly slow once the table is populated enough, plus using the UNIX timestamp as "primary key" just seems horrible, although I'm having trouble expressing why.

My question is: is my coworker breaking any widely accept database storage principle, or am I just being one-track minded and adverse to an equally viable data storage solution? If it's the former, are there any seminal resources (papers, case studies, landmark blog posts) I can share to support my claim?

Answer 1

Your coworker is actually right about this. If you're taking measurements from all your sensors at the same interval, and the set of sensors you're recording from remains constant or mostly constant, what you should be recording is the measurements from all your sensors each interval. What you're doing right now, recording one element per row with a tag explaining what type of value the element represents, is a well-known anti-pattern known as EAV (Entity-Attribute-Value.) The entity, in this case, is your reading time, with each sensor representing an attribute that has a specific value.

EAV feels like it offers a lot of flexibility, and it does, but it does so by throwing out many important advantages of the relational database model. For example:

• you can't use your value column as a FK now, because you've got many different types of values mixed in there
• JOINs become much more painful if not impossible
• All searches for a type of value involve a WHERE filter, which is far more expensive than a SELECT filter
• To make that WHERE less expensive, you've had to add an index on your Atttribute column, which makes INSERTs more expensive and increases the side of your database. If you move off of the EAV model to the standard table model, you get this for free as part of your table's schema.

The EAV model can be useful for non-fixed data that doesn't easily fit a well-defined schema, but that's not what you're working with. You should go with your coworker's model.

Answer 2

For data storage I would stick with your approach or even create a table for a fact of measure (if you have attributes for it) and separate tables per type of measure if you have different sets of attributes.

What your colleague seems to be doing better is data presentation. But through change of data storage I do not agree with. Fortunately database engines enable us to separate storage and presentation. Would couple of views on top of your table and similar in structure to your colleagues table provide benefits of both options?

Regarding timestamp it is not clear what real life data attribute it represents. It can do well enough in a lot of situations and be a disaster in others.