Hey, first poster here:
I'm not actually running on MySQL (I'm using MS), but my question is more of a design issue. The model I've been using is perfect for maintaining relationships between data logging devices I have in the field, and so-so in keeping an aggregate of that data stored somewhere. However, I'm wanting to keep a history of the data packets also, which presents a problem.
Currently I have decided to have a resolution of one hour for histories, and so there's a table that has three columns: DateTime Hour, Int64 DeviceId, Int Value. The first two are indices, where DeviceIds are indexed by Hour. The Hour column is set to the first second of every hour that there is data collected, and Value contains the aggregate of all data collected at the device DeviceId.
The problem comes when I'm getting all of this data externally -- there comes a time when I need to insert or update the history of this device, but I don't know if the current hour exists in the history database. Now I can only run an UPDATE and INSERT upon exception, where the Hour and DeviceId match the data I'd like to update with.
Is this the only way to do this? Is this a good way to do it? It doesn't seem like it. Also, is there a better model for this?
I'm new to this field. . .
Thanks,
Brandon