0

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?

  • There is no guarantee that timestamp will always be unique, I like your coworkers approach but what does he do with collisions? – Pieter B Nov 5 '16 at 16:57
  • "The real table is obviously more complicated, taking units and other details into account, but this MWE is enough to demonstrate my question." How are these other details reflected in your coworkers table? If each measurement requires 5 or 6 additional columns besides the actual value then your approach might be better. Consider transforming your existing data into the new format and then run some tests on actual data. – Cerad Nov 5 '16 at 17:45
  • Main question aside. My question is. Why a RDBMs? Seems to me that noSql DB is what you need. Looking at both tables does it evident. Do you really need RDBMs db features? If you don't then none of the problems exposed here related to querying matters. – Laiv Nov 5 '16 at 20:37
  • @laiv That's precisely the wrong question to be asking. Almost every non-trivial application can benefit from the guaranteed protection against data corruption that the ACID model provides. The real question is, "do you really need 'web scale' scalability more than you need data integrity." And the answer, unless you're doing something that's actually web scale (ie. Amazon, Google, Facebook, etc) is no. Database scale doesn't become a bigger problem than throwing a bigger server at it can handle until you get truly massive. – Mason Wheeler Nov 5 '16 at 23:04
  • I didn't mention scalability intendedly. OP didn't suggest it was a problem (yet). My question was addressed to the adecuacy. Some questions and their answers have been exposing pros and cons inherents to the different RDBM designs. Problems that would desapear with a persistence model that does not care about them. Because in the given example does not matters the integrity. These data are like log traces. You can use Oracle to.persit logs ofcourse but you mainly write them into files 99%of the times. So the RDBM seems to me overkill. – Laiv Nov 5 '16 at 23:45
9

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.

  • You haven't mentioned the unix timestamp yet, but it shouldn't be the primary key. If there is to be a primary key, it should be something that has the proper characteristics, like an autonumber or GUID. – Robert Harvey Nov 5 '16 at 19:10
  • @RobertHarvey Meh. That's getting into the old "natural key vs. surrogate key" debate. IMO the only "proper characteristic" that matters is uniqueness, which the timestamp provides, so I don't see any problem with letting it serve as a PK in this instance. – Mason Wheeler Nov 5 '16 at 19:28
  • Unix timestamps are not guaranteed to be unique. – Robert Harvey Nov 5 '16 at 19:56
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.